Data inside data listings .........

[abemaca]

hi , first ide like to say hello as this is my first proper post (i think lol) ive been reading the site for years and it has been most helpful so now its time for me to ask the question , so here goes ......

 

i run a network site and it users friends like most network sites do . when i login it gets my friends and there events , these are 2 different tables.

 

it shows the result in the order of friends , IE USER1 did this , USER1 did that , USER1 did this also , USER2 did this , etc etc

 

i need to to read in time posted but as its selecting friends 1st followed by there events it lists like above , how can i get it to read event times so it reads like this .... USER1 did this , USER2 did this , USER1 did that , USER2 did that , etc

 

any tips welcome and thanks for reading.

 

[xyph]

Try using the ORDER BY `datecolumn` in your SQL query. Regardless, I don't think this is RegEx.

[abemaca]

sorry if i posted in wrong place , hopefully an admin will move it.

 

i list in time order and it still does all friend events at same time , IE all user1 1st , then all user2 , etc

 

 

[xyph]

Can I see your query? A GROUP BY will affect the ORDER BY clause.

[abemaca]

<?

      $getfriends = mysql_query("SELECT contact FROM $tab[contacts] WHERE user='$id';");

        while ($friends = mysql_fetch_array($getfriends))

        {

 

      $getevents = mysql_query("SELECT event,time,img,vid,status,added FROM $tab[events] WHERE user='$friends[0]' ORDER BY time;");

              while ($event = mysql_fetch_array($getevents))

              {

 

[xyph]

You can merge both of those queries into one using a JOIN

 

SELECT `e`.`event`,`e`.`time`,`e`.`image`,`e`.`vid`,`e`.`status`,`e`.`added`,`c`.`contact`
FROM `events_table` `e`, `contacts_table` `c`
WHERE `e`.`user` = `c`.`contact` AND `c`.`user` = $id

 

I haven't tested this though. I'll set up a demo table and see if it works

[abemaca]

thanks XYPH , all you help has been very usefull , im off to work now but ill test the code asap and post results here.

 

thanks again for tacking time out to help , its appreciated alot.

[abemaca]

do i keep like this ......

 

FROM `events_table` `e`, `contacts_table` `c`

 

..... or use my table names like this ......

 

FROM `$tab[events]` `e`, `$tab[contacts]` `c`

 

.... ????

[abemaca]

well it replaced it with that one only to get an error

[fenway]

@abamaca -- post your query.

[abemaca]

i did before , this is what im using now ......

 

<?
       $getfriends = mysql_query("SELECT contact FROM $tab[contacts] WHERE user='$id';");
        while ($friends = mysql_fetch_array($getfriends))
        {
      
            $getevents = mysql_query("SELECT event,time,img,vid,status,added FROM $tab[events] WHERE user='$friends[0]' ORDER BY time;");
              while ($event = mysql_fetch_array($getevents))
              {?>

[fenway]

That's php code, not a mysql query.

 

Also, why not just use a JOIN?

[abemaca]

That's php code, not a mysql query

 

i posted it elsewhere and it was staff that moved it here.

 

Also, why not just use a JOIN?

 

i didnt know about this , hence me posting it on a help site , to get some help !!!

 

someone asked me to paste what i used already , so i did.

 

i was then given another way to query , so i replaced mine for theres but it gave back error hence the followup questions.

[fenway]

"SELECT e.event,e.time,e.img,e.vid,e.status,e.added, c.contact FROM $tab[contacts] AS c INNER JOIN $tab[events] AS e ON (  e.user =  c.contact ) WHERE c.user='$id' ORDER BY e.time"

[abemaca]

"SELECT e.event,e.time,e.img,e.vid,e.status,e.added, c.contact FROM $tab[contacts] AS c INNER JOIN $tab[events] AS e ON (  e.user =  c.contact ) WHERE c.user='$id' ORDER BY e.time"

 

ok i copied this in and NO errors , yippie

 

this is how i have it now .....

 

<?
       $getfriends = mysql_query("SELECT e.event,e.time,e.img,e.vid,e.status,e.added, c.contact FROM $tab[contacts] AS c INNER JOIN $tab[events] AS e ON (  e.user =  c.contact ) WHERE c.user='$id' ORDER BY e.time;");
        while ($friends = mysql_fetch_array($getfriends))
        {

 

so how do i show results ??? before i would use <?=$friends[0]?> ...... but this dont work ???

 

thanks in advance for this help. great site

[fenway]

Look at each of the fields returned -- one of them is what you're looking for.

[abemaca]

so it is .... thanks alot for your time and help fenway , it really is appreciated alot

 

topic solved !!!