Titles and Lists

[NigeB]

I am trying to create a page that queries a table containing team names and country to return a list thus

Belgium
Smiths Team
Jones Team
Rogers Team

Holland
Sithy's Team
Peters Team

etc etc,

I have written a query, but unfortunatly it only returns the first country and teams from that country, any pointers as to the errors greatly appreciated -

Nigel

Code -------

$query1 = "SELECT DISTINCT country FROM teams_online";
$result1 = mysql_query($query1);
while ($row = mysql_fetch_array($result1)) {

$region = $row["country"];
$query2 = "SELECT * from teams_online where country ='$region' order by team_name";
$result2 = mysql_query($query2);
}

mysql_free_result($result1);

if ($myrow = mysql_fetch_array($result2)){

echo &quot;<table width=600 border=0><tr><td>'$region'</td></tr>\n&quot;;

$i =1;

do{

printf(&quot;<tr><td>%s</td><td>%s</td></tr>\n&quot;, $i, $myrow[&quot;team_name&quot;]);

$i++;

} while ($myrow = mysql_fetch_array($result2));


echo &quot;</table>\n&quot;;

} else {

echo &quot;Sorry, no Team records were found!&quot;;

}

 

[r937]

you should really get everything from the database in one query --

[tt]select country, team_name
from teams_online
order
by country, team_name[/tt]

then just loop through these results, detect a change in country, and print your country headers before the team_name lines

rudy

http://r937.com/

 

[NigeB]

rudy,

thanks for the pointer, I had been using one query, but was getting lost trying to insert a country header in the middle of the loop, i.e. how to detect the country change, hence the two queries.

What is your suggestion for inserting a header in this loop? As all that comes back is one Country and a long list of teams for that country.

$query1 = "SELECT * from teams_online order by country, team_name";
$result1 = mysql_query($query1);
     
echo &quot;<table width=600 border=1>\n&quot;;

while ($myrow = mysql_fetch_array($result1)) {
   
   printf(&quot;<tr><td>%s</td></tr>\n&quot;, $myrow[&quot;country&quot;]);
   
   	$i =1;

while ($myrow = mysql_fetch_array($result1)) {  
   
 	printf(&quot;<tr><td>%s. %s</td></tr>\n&quot;, $i, $myrow[&quot;team_name&quot;]);
	
	$i++;
	
}

	echo &quot;</table>\n&quot;;

}

TIA

Nigel

 

[r937]

Nigel, sorry, i don't do php

in coldfusion it's a snap, checking for control breaks is actually built in to the language (CFOUTPUT GROUP=)

perhaps re-post in the php forum


rudy

 

[NigeB]

Thanks for the assitance, I managed with a little help from the php forum, the final code is -

echo &quot;<table width=600 border=0>\n&quot;;

$query1 = &quot;SELECT DISTINCT country FROM teams_online order by country&quot;;
$res1 = mysql_query($query1);
while ($c_row = mysql_fetch_assoc($res1))
{
   printf(&quot;<tr><td><b>%s</b></td></tr>\n&quot;, $c_row[&quot;country&quot;]);
   
   $region = $c_row[&quot;country&quot;];
   $query = &quot;SELECT * from teams_online where country = '$region' order by team_name&quot;;;
   $res2 = mysql_query($query);
   if (mysql_num_rows($res2) > 0)
   {
      while ($t_row = mysql_fetch_assoc($res2))
      {
	  
	  printf(&quot;<tr><td>%s</td></tr>\n&quot;, $t_row[&quot;team_name&quot;]);
         
      }
   }
   else
   {
   echo &quot;Sorry, no Team records were found!&quot;; 
      
   }
} 
echo &quot;</table>\n&quot;;

NigeB

 

[r937]

like i said, i don't do php, but i can sorta read it, and that looks terribly inefficient

i guess it doesn't matter, if you don't have a huge application, but it won't scale well

it has two queries, and the second is inside a loop!!

ee

http://wwwwwwwww

no offence, nige