WHERE IN ... ORDER BY FIELD won't work

masonbarge

I hope this is something really stupid.

I have a SELECT ... WHERE IN($var) query and I want to sort the results in the same order as $var. Here's the code

//$var is a numeric array
$var = implode (', ', $var);
$var = rtrim ($var, ',');

echo "$var"; // 3, 22, 6, 21, 4, 20, 12, 17, 2, 5, 19, 18, 7, 10, 16, 13
            (no whitespace at front or back)
	$query = "SELECT * FROM entrytest WHERE id IN ($var)";

This works fine, but it is order by the id in the table (i.e., entrytest.id -- 2, 3, 4, 5 etc). I need it in the same order as $var. However:

	$query = "SELECT * FROM entrytest WHERE id IN ($var) ORDER BY FIELD (id, $var)";

gives me an invalid resource. :mad:

I don't see my mistake. This is mysql 4.1.21-standard. When I do a test query on phpMyadmin, using values instead of the variable

SELECT * FROM entrytest WHERE id IN (3, 22, 6, 21, 4, 20, 12, 17, 2, 5, 19, 18, 7, 10, 16, 13) 
ORDER BY FIELD (id, 3, 22, 6, 21, 4, 20, 12, 17, 2, 5, 19, 18, 7, 10, 16, 13)

it works fine, returning the query in the same order as the IN clause.

masonbarge

This doesn't work either

$var3 = $var;
	$var2 = join (', ', $var2);
	$var2 = rtrim ($var2, ',');
	$var3 = join (', ', $var3);
	$var3 = rtrim ($var3, ',');
	$var3 = ('id, ') . $var3;
	$query = "SELECT * FROM entrytest WHERE id IN ($var2) ORDER BY FIELD ($var3)";

Despite the fact that "echo $var3;" gives me the result I want:
"id, 3, 22, 6, 21, 4, 20, . . "

Piranha

I had a simular problem today, and it turned out that it was pretty easy to solve. Instead of writing

$query = "SELECT * FROM entrytest WHERE id IN ($var) ORDER BY FIELD (id, $var)";

try to type this:

$query = "SELECT * FROM entrytest WHERE id IN ($var) ORDER BY FIELD(id, $var)";

In other words, remove the space after FIELD.

masonbarge

This doesn't work for me. What mysql version are you using? I have 4.1.21-std.

Savahn

Try this:

 $query = "SELECT * FROM entrytest WHERE id IN (" . $var . ") ORDER BY FIELD(id, " . $var . ");