[RESOLVED] QUERY-ALLY misunderstood...

fontener

First of all, I'd like to thank everyone who has helped me through this learning process thus far.. it is MUCH appreciated!

Today's technically difficulties are:
I'm trying to display a table and each row have the $sion, $form, and all $Artist,$Song,$stat that share the same $sion (if that makes sense).. I'll give an example after the PHP i have...

$result = mysql_query( "SELECT DISTINCT u.sid, u.soid, u.aid, u.stat,a.*,s.*,t.*,f.* FROM a, s, t, u, f WHERE u.soid=s.soid and u.sid=t.sid and a.aid=s.aid and t.fat=f.fat");   

  for($row_count = 0; $row = mysql_fetch_array($result); $row_count++){
    $Artist = $row["an"];
    $Song = $row["st"];
    $Sion = $row["cl"];
    $form = $row["form"];
    $stat = $row["stat"];
    $row_color = ($row_count % 2) ? $color1 : $color2;
    $array = array($Artist, $Song, $stat);
    echo "<tr class=\"$row_color\"><td>$Sion</td><td>$form</td><td><table><tr>"; foreach ($array as &$value){echo "<td>$value</td>";} echo "</tr></table></td></tr>";}

Let's say there are 3 songs that fall under sion1, 1 song under sion2, and 2 songs under sion3... so results should display like so:

$sion2 | $form | $Artist | $Song | $stat

dagon

keep track of the $sion in the loop, if it changes you do one thing if its the same as the previous row you do another

laserlight

You should also order by this $sion thing.

fontener

I've tried this as well but only get one result for $Sion.. it does not loop

$result = mysql_query( "SELECT u.sid,f.*,t.* FROM u, f, t WHERE u.sid=t.sid");   

  for($row_count = 0; $row = mysql_fetch_array($result); $row_count++){
    $Sion = $row["cl"];
    $id = $row["sid"];
    $form = $row["form"];
    $row_color = ($row_count % 2) ? $color1 : $color2;

echo "<tr class=\"$row_color\"><td class=\"fivefive\">$Sion</td><td class=\"fivefive\">$form</td><td class=\"oneten\"><table>"; 
$result = mysql_query( "SELECT u.sid, u.aid, u.status,a.*,s.*,t.* FROM artist a, song s, station t, status u WHERE u.sid=$id and u.soid=s.soid and u.sid=t.sid and a.aid=s.aid" );
   while ($row = mysql_fetch_array($result)){
    $Artist = $row["an"];
    $Song = $row["st"];
    $stat = $row["stat"];

echo "<tr><td>$Artist</td><td>$Song</td><td>$stat</td></tr>";
}
    echo "</table></td></tr>";}

other than that, I do get the desired effect.

fontener

ok, by changing the second query to r2 instead of result... it returns all.. HOWEVER,... it loops like 20 times... so the data is repeated over and over... why is this?

$result = mysql_query( "SELECT u.sid,f.*,t.* FROM u, f, t WHERE u.sid=t.sid");   

  for($row_count = 0; $row = mysql_fetch_array($result); $row_count++){
    $Sion = $row["cl"];
    $id = $row["sid"];
    $form = $row["form"];
    $row_color = ($row_count % 2) ? $color1 : $color2;

echo "<tr class=\"$row_color\"><td class=\"fivefive\">$Sion</td><td class=\"fivefive\">$form</td><td class=\"oneten\"><table>";
$r2 = mysql_query( "SELECT u.sid, u.aid, u.status,a.*,s.*,t.* FROM artist a, song s, station t, status u WHERE u.sid=$id and u.soid=s.soid and u.sid=t.sid and a.aid=s.aid" );
   while ($row = mysql_fetch_array($r2)){
    $Artist = $row["an"];
    $Song = $row["st"];
    $stat = $row["stat"];

echo "<tr><td>$Artist</td><td>$Song</td><td>$stat</td></tr>";
}
    echo "</table></td></tr>";}