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_array" 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 multidimensional 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.

[George Thompson]

sorry meant to say "field" just forgot the "d"... did you get a chance to see the post i had made just after that? with the multidimensional array? what are your thoughts there? and for me to make each friend have their own field seems like it would be a lot of code? how do other "communities" structure their friend fields? thanks again buddy gt

[Rabbit]

Whether or not it would work is moot if you can fix the SQL.

I wasn't saying to put a friend in their own fields. But to put a friend in their own record.

member friend
bob    sally
bob    billy
sally  billy

[George Thompson]

hm, ok so your saying i should create a new table, call it "friends" or whatever, and in that table have that hold who is friends with whomever?

[Rabbit]

Yes, that way you can join the members to the friend's updates and sort by the time all within SQL. You don't have to write code to compensate for the unnormalized data structure.

[George Thompson]

i see... sounds great. but here is the new problem with that idea... i already have a database set up with members and the members have friends... so i would have to go through one by one and re-set up their friends individually? might take me quite a while... any other suggestions? or maybe a quicker way to restructure the friends

[Rabbit]

What's available to you to accomplish the restructuring will depend on the specific database system you're using. I suggest posting in that database's forum and link to this thread so they have some background. We have forums for the most used databases here so you should be covered.

[George Thompson]

ok thanks, im running mysql 5.1 but i think ive created a php script that will explode and insert into the new table. i really appreciate all of your help. ill start work on that tonight, i appreciate the help, better to restructure it now with it somewhat small system, as apposed to later where it might be to big of a job to handle. thanks again!!