IN ID()

[jaymc]

Im using the very useful  "IN ID()" in a query

 

Here is an example query

 

IN ID(9,3,5,6,1,2)

 

Now, when it queries the table, it will pull the results out not in the order of 9,3,5,6,1,2 but in the order of where those IDs appear in the table being queried

 

So if ID 9 is the last row in the table... that will be the last one displayed

 

How can I get it to dump the rows in the array in order of 9,3,5,6,1,2 and not in the order they appear in the table

[Barand]

I take it you mean "... WHERE ID IN (9,3,5,6,1,2)".

 

The only SQL way that springs to mind immediately is

 

SELECT .... WHERE ID = 9

UNION

SELECT .... WHERE ID = 3

UNION

SELECT .... WHERE ID = 5

 

etc

 

or use php to put them in the required sequence. I'll come back with code for that in a few minutes.

[jaymc]

I want to mange it all in one query

[Barand]

That would be one query

[jaymc]

Ah I see, example code would be great

[Barand]

I thought that was what I gave you.

 

OK

<?php
include 'db.php';

/*******************************************************
* spoonfed sql solution
********************************************************/
$sql = "SELECT id, dogname FROM dogtable WHERE id = 9
        UNION
        SELECT id, dogname FROM dogtable WHERE id = 3
        UNION
        SELECT id, dogname FROM dogtable WHERE id = 6
        UNION
        SELECT id, dogname FROM dogtable WHERE id = 5
        UNION
        SELECT id, dogname FROM dogtable WHERE id = 1
        UNION
        SELECT id, dogname FROM dogtable WHERE id = 2";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($id, $name) = mysql_fetch_row($res))
{
    echo $id, ' ', $name, '<br/>';
}

echo "---------------------<br/>";

/*******************************************************
* php solution
********************************************************/
$ids = array(9,3,6,5,1,2);

$idlist = join (',', $ids);

$sql =  "SELECT id , dogname FROM dogtable
        WHERE id IN ($idlist)";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($id, $name) = mysql_fetch_row($res))
{
    $results[$id] = $name;
}

/**
* output in desired order
*/
foreach ($ids as $id)
{
    echo $id, ' ', $results[$id], '<br/>';
}
?>

 

gives -->

[pre]

9 dog J

3 dog C

6 dog G

5 dog F

1 dog A

2 dog B

---------------------

9 dog J

3 dog C

6 dog G

5 dog F

1 dog A

2 dog B

 

[jaymc]

Haha @ Spoonfed!

 

Sorry Ive never heard of that UNION claws, I'll opt for the PHP solution it looks great !

 

Thanks!

[fenway]

*shudder*

 

I guess no one has ever heard of ORDER BY FIELD()....

[Barand]

*shudder*

 

I guess no one has ever heard of ORDER BY FIELD()....

 

How would ORDER BY put them in the specified sequence viz. 9,3,5,6,1,2?

[fenway]

*shudder*

 

I guess no one has ever heard of ORDER BY FIELD()....

 

How would ORDER BY put them in the specified sequence viz. 9,3,5,6,1,2?

 

FIELD(str,str1,str2,str3,...)

 

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

 

So:

 

ORDER BY FIELD( id, 9, 3, 5, 6, 1, 2 ) ASC

 

Will give them the desired ordinals assuming that there are no other options... otherwise, you have to put them in backwards, use desc, and then order by the field again afterwards.

[Barand]

Hadn't spotted that one before. Live 'n' learn.