sort mysql data

[derek mcgilvray]

Hi folks,

I've spent two days on this problem and cannot get it working, so I need help!

I want to select data from one table and display it all, and use the table from another table to highlight cells.

table1
id
af_id
af_detail

table2
id
user_id
af_id

//display all from table1
//when table1 is displayed, colour red the values from table2

//not sure how to apply this...

Any comments are much appreciated.

Thanks

[rocknbil]

when table1 is displayed, colour red the values from table2

The only unique value from table two is user id? The table2 id is an auto increment, right? having no other value? Not getting it.

<?php
$ubg = 'style="color:#ff0000;"'; // or use class
$out = null;
// make your connection, then
$query = "select table1.id,table1.aff_id,table1.user_id from table1,table2 where table1.af_id=table2.af_id order by table2.user_id asc, table1.af_id asc";

$result = mysql_query($select) or die("Cannot execute query: " . mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
 $out .= ' 
 <tr><td><a href="details.php?id="' . $row['id'] . 
 '">' . $row['id'] . '</a></td><td>' . $row['af_id'] . 
 "</td><td $ubg>" . $row['user_id'] . '</td></tr>'; 
} 
if ($out) { echo "<table>$out</table>"; } 
else { echo "<p>No results</p>"; } 
?> 

[derek mcgilvray]

Sorry, I didn't explain it very well. This might be better.

I have one table full of football teams:
team_name
team_detail

and another table which shows people's favourites:
user_id
team_name

I want to display all the team names in an html table, one in each cell. I want to colour all the favourites red (so out of 50 teams in my table I might have 15 coloured red).

Thanks for taking the time to look.

[derek mcgilvray]

Ok, got it - I found that starting from scratch was easier than shuffling rows and rows of code :) Hope this might help someone else in same boat.

$query2="SELECT * FROM table1 ORDER BY id DESC";
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);
echo"<table width=\"100%\" border=\"1\"><tr valign=\"top\">";
$m=1;
$n=0;
while($n<$num2){
$af=mysql_result($result2,$n,"af");
$name=mysql_result($result2,$n,"name");


$query3="SELECT DISTINCT af_id FROM table2, team_groups WHERE table2.userid=team_groups.id AND team_groups.team='$team' AND af_id='$name'";
$result3=mysql_query($query3);
$num3=mysql_num_rows($result3);
if($num3>0){
$aaa="bgcolor=\"red\"";
}else{
$aaa='';
}

echo"<td $aaa width=\"14%\"><p class=\"small\"><b>$name</b></p><p class=\"small\">$af</p></td>";


if($m==7){
echo"</tr><tr valign=\"top\">";
$m=0;
}
$m++;
$n++;
}
echo"</tr></table>";