MySQl Order By help....

Dawg · Posted: Mon Apr 07, 2008 7:38 pm

OH Great ones....

May a mear Grasshopper ask a question?

I have 2 tables....

Table 1 is Town Name and it contains TID (TownID)
Town A
Town B
Town C
Town D
etc...

Table 2 is Friend (and it contains TID)
Friend 1, TIDB
Friend 2,TIDA
Friend 3,TIDA
Etc...

What I want to do is create a list of Towns and frineds...so that I get

Town A
Friend 1
Friend2
Friend3

Town B
Friend1
Friend2
Friend3

Town C
Friend1
Friend2
Friend3
You get the general Idea....

Here is my town loop....

Code:

$querystr = "SELECT tid,town_name,rank FROM ".$prefix."_towns ORDER BY rank" ;
$result = sql_query($querystr, $dbi)
or die ("invalid query in towndisplay");
for ($m=0; $m < sql_num_rows($result, $dbi); $m++)
{
list ($tid,$town_name,$rank) = mysql_fetch_row($result);
echo" <tr>"
. " <td><center>$town_name</center></td>"
. " <td><center>$rank</center></td>"
. " </tr>";

}

Here is my friend loop....

Code:

$result = sql_query('SELECT id,tid,text,title,date_added,img_one,img_two,img_three,state,town,email,report,photolink,page,website,logo,phone,text2
FROM '.$prefix.'_list ', $dbi);

$i = 0;
for($m=0; $m < sql_num_rows($results, $dbi); $m++) {

list($id,$tid,$text,$title,$added,$img_one,$img_two,$img_three,$state,$town,$email,$report,$photolink,$page,$website,$logo,$phone,$text2) = sql_fetch_row($result, $dbi);

if (++$i % 2 == 1)
{

echo "<table width='100%' border='5' cellspacing='0' cellpadding='0'><tr>";

}

$content = <<<END

Ton of stuff where I am formatting the vars....
END;

      echo $content;
}

My question is "How do I combine them so I sort by the $TID#1 in the first sql and list the friends then loop back and get TID#2 and list the friends and loop back and get $TID#3 and list the friends and so on....

Thank You very much for the help and as usual...I am not looking for someone to write the code for me...just explain how it should work with an example.

Thank You for your time!

Dawg

Raven · Posted: Mon Apr 07, 2008 10:45 pm

Dawg wrote:

OH Great ones....

May a mear Grasshopper ask a question?

I have 2 tables....

Table 1 is Town Name and it contains TID (TownID)
Town A
Town B
Town C
Town D
etc...

Table 2 is Friend (and it contains TID)
Friend 1, TIDB
Friend 2,TIDA
Friend 3,TIDA
Etc...

What I want to do is create a list of Towns and frineds...so that I get

Town A
Friend 1
Friend2
Friend3

Town B
Friend1
Friend2
Friend3

Town C
Friend1
Friend2
Friend3
You get the general Idea....

Here is my town loop....

Code:

$querystr = "SELECT tid,town_name,rank FROM ".$prefix."_towns ORDER BY rank" ;
$result = sql_query($querystr, $dbi)
or die ("invalid query in towndisplay");
for ($m=0; $m < sql_num_rows($result, $dbi); $m++)
{
list ($tid,$town_name,$rank) = mysql_fetch_row($result);
echo" <tr>"
. " <td><center>$town_name</center></td>"
. " <td><center>$rank</center></td>"
. " </tr>";

}

Here is my friend loop....

Code:

$result = sql_query('SELECT id,tid,text,title,date_added,img_one,img_two,img_three,state,town,email,report,photolink,page,website,logo,phone,text2
FROM '.$prefix.'_list ', $dbi);

$i = 0;
for($m=0; $m < sql_num_rows($results, $dbi); $m++) {

list($id,$tid,$text,$title,$added,$img_one,$img_two,$img_three,$state,$town,$email,$report,$photolink,$page,$website,$logo,$phone,$text2) = sql_fetch_row($result, $dbi);

if (++$i % 2 == 1)
{

echo "<table width='100%' border='5' cellspacing='0' cellpadding='0'><tr>";

}

$content = <<<END

Ton of stuff where I am formatting the vars....
END;

      echo $content;
}

My question is "How do I combine them so I sort by the $TID#1 in the first sql and list the friends then loop back and get TID#2 and list the friends and loop back and get $TID#3 and list the friends and so on....

Thank You very much for the help and as usual...I am not looking for someone to write the code for me...just explain how it should work with an example.

Thank You for your time!

Dawg

This assumes that the friend table has a name field. If not then just change the field name.

If you only want to list towns that have at least 1 friend, then try this query.

Code:

SELECT t.tid, f.name
FROM town_name AS t, friend AS f
WHERE t.tid = f.tid
ORDER BY t.tid, f.name

If you want to list all towns regardless of how many friends, then try this query.

Code:

SELECT t.tid, f.name
FROM town_name AS t LEFT JOIN friend AS f
ON t.tid = f.tid
ORDER BY t.tid, f.name

Raven · Posted: Wed Apr 09, 2008 2:00 am

Did this resolve your question?

Dawg · Posted: Wed Apr 09, 2008 6:19 am

Raven,
I did not get to work on it yesterday. I will today though. Thank You for the help!

The second one is the one I will be using...I assume this is a kind a short hand for ...

SELECT town.tid,friend.name FROM town_name as town LEFT JOIN friend AS friend ON town.tid = friend.tid ORDER BY town.tid, friend.name

IS that correct?

Dawg

Raven · Posted: Wed Apr 09, 2008 1:53 pm

Yes.