Asked  7 Months ago    Answers:  5   Viewed   31 times

I have string containing a lot of IDs corresponding to my database. Like:

1,2,3,4,5

I then do a mySQL query to select all those rows:

SELECT * FROM `table` WHERE `id` IN (".$myIDs.")

I want mySQL to return the rows in the order they are in my IN() select.

So if I instead had

2,1,3,4,5

I would have #2 as first row, when fetching in PHP. Without any ORDER BY it looks like it return the lowest ID first.

Let me know if you need further exlpanation.

 Answers

95

You should be able to do this via the FIELD() function like so:

SELECT * FROM `table` WHERE `id` IN (2,1,3,4,5) ORDER BY FIELD(`id`, 2,1,3,4,5) DESC

That is:

SELECT
  *
FROM
  `table`
WHERE
  `id` IN (".$myIDs.")
ORDER BY
  FIELD(`id`, ".$myIDs.") DESC

More in this blog post: Sorting MySQL rows using column values.

Wednesday, March 31, 2021
 
ariel
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 4 Months ago
100

You almost had it. You just need to add an order property to your a1 terms aggregations, like this:

GET myindex/_search
{
  "size":0,
  "aggs": {
    "a1": {
      "terms": { 
        "field": "FIELD1",
        "size":0,
        "order": {"a2": "desc"}      <--- add this
      },
      "aggs":{
        "a2":{
          "sum":{
            "field":"FIELD2.SUBFIELD"
          }
        }
      }
    }
  }
}
Sunday, August 8, 2021
 
wenjiehu
answered 3 Months ago
49
In [5]: sorted(unsorted_list, key=presorted_list.index)
Out[5]: ['2C', '2D', '3D', '4D']

or, for better performance (particularly when len(presorted_list) is large),

In [6]: order = {item:i for i, item in enumerate(presorted_list)}    
In [7]: sorted(unsorted_list, key=order.__getitem__)
Out[7]: ['2C', '2D', '3D', '4D']

For more on how to sort using keys, see the excellent Howto Sort wiki.


If unsorted_list contains items (such as '6D') not in presorted_list then the above methods will raise an error. You first have to decide how you want to sort these items. If you want them placed at the end of the list, you could use

In [10]: unsorted_list = ['3D','2C','6D','4D','2D']

In [11]: sorted(unsorted_list, key=lambda x: order.get(x, float('inf')))
Out[11]: ['2C', '2D', '3D', '4D', '6D']

or if you wish to place such items at the front of the list, use

In [12]: sorted(unsorted_list, key=lambda x: order.get(x, -1))
Out[12]: ['6D', '2C', '2D', '3D', '4D']
Saturday, August 14, 2021
 
millenomi
answered 3 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 :