Results in particular order

zzz

I have an array with member IDs.

I convert it into a string

$MemIDs = implode(",", $IDs);

and then query my database looking for these ids

WHERE memberID IN (".$MemIDs .")

and output info through FOREACH statement.

Is there a way to order results according to the order of the array by either doing it withing query or foreach ?

Actually what I really need is to get the member whose ID is the first one in my array to be on the top of the results.

cahva

Sure, put the results in to array and foreach $IDs:

$member = array();

while ($r = mysql_fetch_assoc($result))
{
	$member[$r['id']] = $r;
}

foreach ($IDs as $MemID)
{
	echo 'Member with id: '.$member[$MemID]['id'];
}

Thats the only way if your $IDs doesnt have any logical order.

zzz

I use a class to query db an it already comes in an array form. Maybe I can reorder that array, hah?

Weedpacket

If the $IDs array has no particular order (as far as the database is concerned) then the easiest method would be to do it in PHP. Obviously this means fetching all of the records from out of the result set and into an array first, and then sorting the result.

Once you've done that you have all your $IDs in one array, and all the $records (ORDERed BY memberID) in another array. Because the records have been ordered by memberID, you want to reorder them using the same permutation that would rearrange their ids to match the order in $IDs.

asort($IDs);
$records = array_combine(array_keys($IDs), $records);
ksort($records);

zzz

Ok, the more I think about it the more I am convinced that I need to sort with PHP. And I think I did not explain it well enough before. Sorry, here's another attempt:

The array of IDs I have has a particular order.

$ID's = array(5193,4428,6468,5203,1168,7279,7184,3160,6009,4623);

The results in from database are in a form of an array object as well. One of the columns ($DBresults->memberID) in queried db corresponds to the ID from the array. I think if I could reorder it before running foreach I'd be OK.

When I run

print_r($DBresults);

I get something like:

Array ( [0] => stdClass Object ([actPub_alias] => kriss [img_default] => spr22 [act_name_m] => [act_stagename_f] => Don [act_stagename_m] => [act_stagename_l] => Kris [member_id] => 1168 [state] => IL [country] => US ) [1] => stdClass Object ( [actPub_union] ....

Weedpacket

zzz wrote:
I use a class to query db an it already comes in an array form.

Well if you're not writing the query then you can't do it in the query.

The results in from database are in a form of an array object as well. One of the columns ($DBresults->memberID) in queried db corresponds to the ID from the array.

What I said still goes: there's only one place it matters what the records actually are, and that's the order in which they are in when you do what I wrote above.
If you want to sort an array of objects (what I take you mean by "an array object" because that's what your dump is of - you do know that those things are easier to read if you keep the formatting, right?) by one of the objects' properties - there's [man]usort[/man] to do that - the usual "take two objects and compare their fields" job.

...if you really want to get witty then the appropriate callback function would allow usort to do the whole job. The callback would need access to the list of IDs in query order, and there are two feasible ways of doing that: (a) a functional closure; (b) since this is in a class the list to order by is presumably available by some method or property, so the callback could be a method with access to it.

zzz

No, I do write the query and am stll open to sorting it in the query. Before I run my query I take my array and convert it into a list of values separated by comma.

$MemIDs = implode(",", $IDs);

and then use it in the query

"SELECT* ...
FROM t1
WHERE memberID IN (".$MemIDs.")
// if I could only do something like this, my problem will be solved
ORDER BY memberID (".$MemIDs.")

If I go PHP route, the I do need to use some sort of function because the results of the query output should be in the same order as the array, or at least the first result should be corresponding to the first array value.

Now, I've gone a long way in terms of array familiarity, but they still confuse me quite a bit. Maybe I can do in the query some thing like

ORDER BY CASE 1, $IDs[0], CASE 2 // the rest of the results -- it's only 10

🙂

zzz

I wish i could sort this with PHP, but I came up with something totally odd. Remember I said it should follow array order OR AT LEAST THE FIRST VALUE IN ARRAY. I added the following code to my query:

ORDER BY CASE 
  WHEN memberID = ".$topMemberID_from_array." THEN 1
END DESC

Totally worked.

Weedpacket

zzz wrote:
Totally worked.

Yeah, that was the guts of what I was thinking about doing it in the query.

As for the PHP approach (and doing everything in one usort):

usort($records, function($a, $b)use($IDs)
{
    $a = array_search($a->member_id, $IDs);
    $b = array_search($b->member_id, $IDs);
    return $a - $b;
});