SQL Query, IF issue

[Mutley]

The $p1 / $p2 etc variables are all defined:

 

 

<?php
			$sql = "SELECT p1_pts = IF(p1_id=$p1), p2_pts = IF(p2_id=$p2), p3_pts = IF(p3_id=$p3), p4_pts = IF(p4_id=$p4), p5_pts = IF(p5_id=$p5), p6_pts = IF(p6_id=$p6), p7_pts = IF(p7_id=$p7), p8_pts = IF(p8_id=$p8) FROM races WHERE race_id = $race_id";
				mysql_query($sql);
				if(mysql_num_rows($result)!=0) {											
			while(list($pts_in_1st) = mysql_fetch_row($result)) {			
echo $pts_in_1st;
}
}

?>

 

Can anyone see what's wrong? Thanks.

[Barand]

what is it meant to do

[Mutley]

The

 

*_id fields match the *_pts field, so:

 

p1_id = p1_pts

 

etc

 

What I'm trying to do is select the corresponding *_pts if the *_id matches the variable.

[Barand]

Are the columns p1_id , p1_pts , p2_id , p2_pts , p3_id , p3_pts etc all in the same record?

[Mutley]

Yep, those are the fields in one table. The query is for one row.

[Barand]

Recommended solution:

 

Normalise the table then "SELECT pts WHERE player = $pl AND race_id = $race_id".

 

But to get you out of the mess you're in with a design like that

SELECT CASE $pl 
         WHEN p1_id THEN p1_pts
         WHEN p2_id THEN p2_pts
         WHEN p3_id THEN p3_pts
         WHEN p4_id THEN p4_pts
         WHEN p5_id THEN p5_pts
         WHEN p6_id THEN p6_pts
         WHEN p7_id THEN p7_pts
         WHEN p8_id THEN p8_pts
         ELSE 0 END
         as pts
FROM races 
WHERE race_id = $race_id