[RESOLVED] Sort Order

intervelopment

Hey All,

I have a bit of a hard question i thought i would ask for help on.

I have a string which i use unserialize on which is

a:9:{i:0;s:2:"20";i:1;s:1:"2";i:2;s:2:"12";i:3;s:2:"13";i:4;s:2:"14";i:5;s:2:"15";i:6;s:2:"16";i:7;s:2:"17";i:8;s:2:"11";}

So, when i 'unserialize' that, it returns 20,2,12,13,14,15,16,17,11

What i then need to do is a create an SQL string.

SELECT * FROM TABLE WHERE pageid = '40'

But i some how need to show the results in order by the unserialize data based on their ID

So, out of the select query results show the row with id of 20 first, then 2 then 12 and so on.

I cant really get my head around it .. could someone please help me out.

Cheers,

bradgrafelman

There was another thread started very recently (and probably many others over time) asking this same question. I believe the common solution is to simply build an array of results and process the items in that array in the order of the list of id's you have.

For example, use a query like this to get all of the rows with matching ID's:

$query = 'SELECT id, col1, col2 FROM myTable WHERE id IN (' . implode(',', $list_of_ids) . ')';

Then, store all of the results in a multi-dimensional array using the ID as the key:

$data = array();
while($row = mysql_fetch_assoc($exec))
    $data[array_shift($row)] = $row;

(Note that I simply used [man]array_shift/man to get the id column and remove it from the array because I listed the id column first in the SELECT query above.)

Finally, you'd loop through your list of ordered ID's and process the data in that order, e.g.:

foreach($list_of_ids as $id) {
    // process the data in $data[$id] here
}

EDIT: One alternative to the above method is to do the ordering in the query itself. To do this, however, you'd need a bunch of "CASE xx THEN 1 .... CASE yy THEN 2 ... etc." code in your ORDER BY statement. If you'd rather go down this route, then look up the "CASE" SQL syntax instead.

intervelopment

Hey .. Thanks for the reply ... I cant seem to get it to work though .... this is what i have

$dbarray = unserialize($bidata);
$newsql = 'SELECT gid, title FROM wp_ngg_gallery WHERE gid IN (' . implode(',', $dbarray) . ')'; 

$testing = mysql_query($newsql);
$newdata = array();
while($newrow = mysql_fetch_assoc($testing)){
	$newdata[array_shift($newrow)] = $newrow;
}


foreach($dbarray as $id){
	echo $newdata['title'];
}

Any chance of letting me know where im going wrong?

Cheers,

zypher11

Whenever you run into an issue where an array isn't printing the way it should be do the following:

echo "<pre>";
print_r($newdata);
echo "</pre>'

in your case currently your looping through dbarray yet trying to display what would be the first item in the newdata array each time.

use print_r to debug the script and make sure your either displaying the array your looping through or you utilize "as $key=>$value" and then cycle through newdata[$key]

intervelopment

Hey .. Sorry, im a bit of a noob ... i can see results using print_r except they are not in the order that was used in the query

zypher11

the results won't return in the order that you entered into the query. they return in the order the database has them entered unless you use ORDER BY at the end which either selects alphabetical or numeric order. If you specifically need the results in the order of the array i would suggest something like:

$newdata;
$dbarray = unserialize($bidata);

foreach($dbarray as $key=>$val){
      $sql = "SELECT gid, title FROM wp_ngg_gallery WHERE gid = $val";
      $result = mysql_query($sql);
      $row = mysql_fetch_assoc($result);
      $newdata[] = $row;
}
echo "<pre>";
print_r($newdata);
echo "</pre>";
//pre is just to display the created array.

of course this is from the hip and im sure theres a more elegant way to accomplish it but as you get better you can alter it as necessary.

EDIT:
the above would return the results into a 2d array. if you simply want it to be a 1d array with the gid being the keys you could replace $newdata[] = $row; with:

$newdata[$row['gid']] = $row['title'];

intervelopment

That returns it in exactly the order i want .... hmmmm next problem is i cant print out the results, do i use a foreach statement on the $newdata?

i.e foreach($newdata as $mydata){
}

bradgrafelman

Note that with my suggested method above you only need 1 DB query, whereas zypher11's method needs "n" queries, where "n" is the number of id's.

intervelopment wrote:
do i use a foreach statement on the $newdata?

Did you try it and find out? You can look through the [man]array[/man] however you please, but yes foreach() is one of them.

zypher11

Ya the only reason i used that method was because it's a bit simpler for him to read and understand being relatively new to php. Your method was a bit more advanced. And yes you would use a foreach loop to display it. if you used ($newdata[$row['gid']] = $row['title']😉 to create the array you would use:

foreach($newdata as $key=>$val){
     echo "id: $key ... title: $val";
}

intervelopment

Ohhh i didnt know that bradgrafelman.

When i tried to use your solution, i couldnt figure it out ... is there any chance you could let me know where i was going wrong with your query

zypher11

@ Your method works perfectly fine except for the fact that it returns the results in order of the DB instead of the order of the starting array. if you want it to return in order that your initial array is set and only use one query then you would do something similar to this:

$dbarray = unserialize($bidata);
foreach($dbarray as $d){
      $newdata[$d] = "";
}
$newsql = 'SELECT gid, title FROM wp_ngg_gallery WHERE gid IN (' . implode(',', $dbarray) . ')'; 
$testing = mysql_query($newsql);

while($newrow = mysql_fetch_assoc($testing)){
	$newdata[$newrow['gid']] = $newrow['title'];
}


foreach($newdata as $key=>$val){
	echo "id: $key .... title: $val... <br />";
}

this should hold the correct order.

intervelopment

Ok ... That last bit of code from zypher11 worked PERFECT.

Thanks so much for your help guys!!!

zypher11

btw bradgrafelman i apologize, i was reading through his interpretation of your code instead of yours. yours definitely would have accomplished the same thing lol. im thinking the array names you used just confused him

intervelopment

Its 6:30 Friday afternoon ... Everything confuses me 😉

bradgrafelman

zypher11 wrote:
it returns the results in order of the DB instead of the order of the starting array

Yes, that was the intended result, which is why the final code snippet in my post used a foreach() loop to process the unordered array of DB results in the order that was originally desired.

I tried to use variable names that described their purpose, but I do seem to recall that I was in a bit of a rush as I finished typing that reply; it's quite possible that I could have made it a bit more clear had I had the time to go back and read things over before posting.

Weedpacket

Here's a thread on a similar problem.