Asked  7 Months ago    Answers:  5   Viewed   23 times

I have a list of numbers: 7,1,3,2,123,55 (which are the ids of existing records)

I have a mysql table with the colums id and name, where id is an integer primary key. I want to select records from this table, but in a specific order, for example 7,1,3,2,123,55.

  • Is it possible to do this in MyISAM within query, without any post processing?
  • What is the simplest way to do this?

 Answers

65

Since 1 < 3 < 77 < 123, a simple ORDER BY id would suffice.

If, however, you want to order this way: 77, 3, 123, 1, then you could use function FIELD():

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

If your query matches more rows than you list in FIELD

FIELD returns 0 when a row does not match any of the ids you list, i.e. a number smaller than the numbers returned for listed ids. This means, if your query matches more rows than the ones you list, those rows will appear first. For example:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 77, 3, 123, 1)

In this example, the row with ID 400 will appear first. If you want those rows to appear last, simply reverse the list of IDs and add DESC:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1, 400) 
ORDER BY FIELD(id, 1, 123, 3, 77) DESC
Wednesday, March 31, 2021
 
Sanguine
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 3 Months ago
29

Your sorting routine will look something like this, depending on the specifics of how you do your JSON/Object mapping:

Collections.sort(myObjectList, new Comparator<MyObject>() {
  @Override
  int compare(MyObject a, MyObject b) {
    if (a.getStartTime().equals(b.getStartTime())) {
      return a.getMachineName().compare(b.getMachineName());
    }
    return a.getStartTime().compare(b.getStartTime());
  }
});
Wednesday, August 25, 2021
 
Daveel
answered 2 Months ago
52

What you are doing is valid - it will order the results in descending order by a but randomize the order of ties.

However to do what you want you need to first use a subquery to get the latest 100 records and then afterwards sort the results of that subquery randomly using an outer query:

SELECT * FROM
(
    SELECT * FROM table1
    ORDER BY date DESC
    LIMIT 100
) T1
ORDER BY RAND()
Monday, August 30, 2021
 
MasterJoe
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :