two tables of data & adding numbers

crickert

I've got two tables. Table 1 has 4 fields. MemberID, MemberName, CarSelection1, CarSelection2

Each member has picked a CarSelection1 = a race car number and CarSelection2 = another race car number.

In table 2, I have 4 fields also. CarID(my primary key), CarNumber, DriverName, Points

What I am trying to do:

if say MemberID #1 is Bob and he picks CarSelection1 = 24 and CarSelection2 = 88, I want to look into Table 2 and find CarNumber =24 and CarNumber=88 - upon finding the corresponding CarNumbers, I want it to add the Points for each CarNumber together.

When this is complete, I want it to loop through Table 1 for each MemberID and do the same.

The output would be something like this

Bob - Summed Points
John - Summed Points
Joe - Summed Points

any help would be greatly appreciated! thanks

chris

Mordecai

Just run the query twice, and add to the value the second time:
$carquery = mysql_query("SELECT points FROM table1 WHERE carnumber=$carnumber");
while ($info = mysql_fetch_array($carquery, MYSQL_NUM)) {
$points = $info[0];
}
$carquery = mysql_query("SELECT points FROM table1 WHERE carnumber=$carnumber2");
while ($info = mysql_fetch_array($carquery, MYSQL_NUM)) {
$points += $info[0];
}

Then insert the new value to the user. This all would go into the foreach containing the user list.

crickert

But points is not in table1.

i need CarNumber in Table 2 to match CarSelection1 for a particlular member in Table1. When the match is made, store the points that equate to that CarNumber and then perform the next query: CarNumber in Table2 match up with CarSelection2 for the same member. Take the points from that CarNumber and add them to the first. So i agree with your multiple queries.

I don't need to insert a value back into the database, i just need to perform the math and output the point total for each person.

I was thinking more of a for loop for each member and then a query for each CarSelection. we are on the right track.

do u kind of understand what i am trying to do?

why are there comments around this line that you wrote?

{
$points = $info[0];
}