math operation - a little help adding point totals

[crickert]

I'm having trouble properly calculating and outputting the variable $total_member_points. What is happening right now is that it takes the value ($points_car1) in the last row of the array and adds it to each value of $points_car2. How can I add the same rows of $points_car1 and $points_car2?

$connection = mysql_connect($dbhost, $dbuser, $dbpass)
or die ("Not Able to connect");

$db = mysql_select_db($dbname, $connection)
or die ("Not Able to connect");

$query = "SELECT Drivers.Points
FROM Drivers, Members
WHERE (Members.Car1 =Drivers.CarNumber)";

$result = mysql_query($query)
or die ("Not Able to connect");

while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$points_car1 = $row[0];

echo "<table cellspacing=0 border=0>\n";
echo "<tr>\n
<td> $points_car1</td>\n
</tr>";
}
echo "</table>\n";

$query = "SELECT Drivers.Points
FROM Drivers, Members
WHERE (Members.Car2 =Drivers.CarNumber)";

$result = mysql_query($query)
or die ("Not Able to connect");

while ($row = mysql_fetch_array($result, MYSQL_NUM))
{

$points_car2 = $row[0];

$total_member_points= $points_car1 + $points_car2;
echo "<table cellspacing=0 border=0>\n";
echo "<tr>\n
<td width=100></td>\n
<td width=50>$total_member_points</td>\n
</tr>\n";
}

echo "</table>\n";

?>

</body>
</html>

[tomhath]

I'm guessing that a member has 2 cars? This is untested but might be what you want.

If either Car1 or Car2 can be null it's more difficult because the join won't work. It looks like you have an array inside a row (Car1 and Car2), which makes this kind of thing a lot more complicated. It would be easier to move those into a separate table with one car per row.

select m.Car1, m.Car2, d1.Points+d2.Points as TotalPoints
from Members m, Drivers d1, Drivers d2
where m.Car1 = d1.CarNumber
  and m.Car2 = d2.CarNumber

[crickert]

i see what you are saying but I only have 1 table for Drivers. It has the driver name, id, and point total for all drivers. So I don't think i can do that select query since you indicate a d2.Points. How does that change things?

select m.Car1, m.Car2, d1.Points+d2.Points as TotalPoints

from Members m, Drivers d1, Drivers d2

where m.Car1 = d1.CarNumber

and m.Car2 = d2.CarNum

[tomhath]

Did you try the query?

Aliases (d1 and d2) let you refer to the same table more than once in the query, it's as if you have multiple tables. This allows you to get two different rows from one table (the row that matches Car1 and the row that matches Car2).

[crickert]

ok, now i know what you are saying. that query works great for getting the data i need. It works perfect in the commandline and outputs exactly how i want it. To get the output to appear in the browser, I've tried the following while loop but doesn't quite work. What is your suggestion for stepping through the list of players to get the output to appear like

Car1 Car2 TotalPoints

this is what i tried

$result = mysql_query($query)
or die ("Not Able to connect");

while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
extract($row)

echo "<table cellspacing=0 border=0>\n";
echo "<tr>\n
<td width =50> $Car1</td>\n
<td width =50> $Car2</td>\n
<td width =50> $TotalPoints</td>\n
</tr>";
}
echo "</table>\n";

[tomhath]

Other than the missing semi-colon after the extract it looks like it should work. What does it do when you run it?

By the way, code is easier to read in this forum if you put [ php] ... [/code] tags around it.