ordering data by datetime from 2 different tables

[George Thompson]

ok, so, what im trying to do is complicated for me, im trying to do this with as least amount of server usage as possible... what im trying to do is basically make an output that shows my "friends updates" but in time order.

$sqlF = mysql_query("SELECT * FROM myMembers WHERE id=6 ");
while($rows = mysql_fetch_array($sqlF)){
    $friend_array = $rows["friend_array"];
}
$friend_array = explode(",", $friend_array);
 
foreach($friend_array as $key => $value){
    $sql = mysql_query("SELECT * FROM updates WHERE user=$value");
    while($rowin = mysql_fetch_array($sql)){
        $name .= ''.$rowin["datetime"].'<br />';
    }
}
 

the code above is almost right, it shows the list and orders them ascending, but it does them by each friend. example

id date friend number
4 2012-05-22 19:15:07 <= friend #4
5 2012-05-22 21:28:58 <= friend #4
1 2012-05-22 16:51:22 <= friend #8
2 2012-05-22 16:56:24 <= friend #8
3 2012-05-22 16:56:49 <= friend #8

and its good but not quite all the way there, what im wanting to do is have it order the whole output by the date/time and not by friend order... right now as you can tell the output is listed by friend order from the exploded freinds list, and not by time. should i do a LEFT JOIN in there to save a query and would that help with ordering by the date/time? any directions would be great, thanks
gt

[Rabbit]

Join the members table to the update table.

[manohoo]

You are correct, a joined query is the answer. However, you don't provide the tables structures, so you leave us in the dark. It would look something like this:

SELECT * 
FROM myMembers, updates 
WHERE myMembers.? = updates.? (the joined columns)
AND myMembers.id=6 
ORDER BY updates.datetime");

A less elegant solution would be to sort the array.

[George Thompson]

sorry, i didnt leave more information, I was hoping to be able to do most of the work myself. but i havent had much luck, i will try to give more information... in the myMembers table i have a whole bunch of tables, only one i need from where is the ("friend_array" ) and from the updates table ill need all tables. ("id, user, name, datetime, type, profile_id, location_id, album_id, thread_id")

now what i think is the tricky part, is that in my "friends_ar ray" table, it has many people listed, for example ("4,2,7,12,8 ") thats how my freinds list looks and i think in the left join its not going to understand all the members are run together and only seperated by commas

[Rabbit]

You should normalize your data. Then you won't have this problem. However, if absolutely necessary, you can join using the LIKE operator.

[George Thompson]

Rabbit, i dont understand what you mean by normalize my data... is there anyway i can get a sample senario? i hate asking for things to be handed to me, but this is the last piece of the puzzle and im going on day 3 for this part, searching online for what im trying to accomplish. it has to be doable, i just cant get my hands around it.

no matter how i try to put it in the query, it just keeps out putting the data from "updates" table in order of the "friends_array" . and ofcourse thats now what i need, i need it to be in order of "datetime" from the updates table, that is querried within the foreach loop. is there a way to after the loop and after the query to re-order the information? thanks Rabbit, gt

[Rabbit]

Instead of storing everything in a string in one field, separate it out as one record for each. Then you can join on it.

[George Thompson]

is there a way to do that via php, or are you saying that each friend should have his/her own field in the mysql database? how would i go about doing that?

if your saying each friend should have their own fiel in the database that would take a lot of reconstruction of an already existing database..

[George Thompson]

ok so did a little research and found something that may work Rabbit. with the code above i am creating a multidimensiona l array correct? where the first array is the friends list, and the second array would be the information from the "updates" table? if thats really the case then i could sort by the second array, i found some code that might lead me in the right direction... here is the snippet i got from the other website.
http://www.informit.com/articles/art...31840&seqNum=6

function compare($x, $y)
{
 if ( $x[1] == $y[1] )
  return 0;
 else if ( $x[1] < $y[1] )
  return -1;
 else
  return 1;
}
 
$products = array( array( 'TIR', 'Tires', 100 ),
          array( 'OIL', 'Oil', 10 ),
          array( 'SPK', 'Spark Plugs', 4 ) );
 
 
usort($products, 'compare');
 
print_r ($products);
 

think that would work? with some modification of course. thanks
gt

[Rabbit]

I don't know what you mean by they should each have their own files. A database is not a file management tool. But yes, I mean redesign the structure. Because as it is, it causes problems when trying to query data.