View Full Version : foreach statement

10-24-2004, 04:05 AM
i have a table with a list of games that contain amongst other things a user, and a result column.
i want to loop through the table and for each individual user, tally the results ( w or l) to see how many games they won and loss. i know how to tally results, but how do i get to append each result with each user?

hope i made myself clear.
tnx for any help.

10-24-2004, 02:35 PM
ok, now that i have most of it working i can better explain (hopefully).

i have a query that grabs and id out of 1 table and with a foreach statement, queryies another table using the id as an argument.

i got it to work except after the first id is read it stops. here is the code:

$query2 = "SELECT id from tablename";
$result2 = mysql_query($query2);
$row = mysql_fetch_array($result2);
foreach ($row as $id) {
$query = "SELECT result FROM tablename WHERE id='$id'";
$result = mysql_query($query);
while ($res = mysql_fetch_array($result) {
blah blah blah

it doesnt loop throught the other rows in the foreach statement.
tnx for any input

10-24-2004, 03:32 PM
why don't you just join the two tables?

you can't use foreach like that since $row in n array with just one element. so it's the same as $row['id'].
your second query is now selecting from the same table so i completely don't see the logic in your code.

What data are you trying to pull out of which table?

10-24-2004, 04:18 PM
i'm trying to retrieve the result column to determine where the result is a win or loss

i'll look up syntax on joining tables.

basically im saying:
grab all of the user ids from the user table and for each id -> query the result column where the userid = 'id" in the games table. determine if the result was a "w" or "l" and echo the user, the wins and the losses.

10-24-2004, 06:34 PM
you should be able to do that in one query, with a GROUP BY clause.

post the tabledesign so we can write it out.

10-24-2004, 06:42 PM
these are the fields in each table:
users table -> user_id

games table -> user_id, username, result

is that what you were looking for?
there is also a username field in the user table but i didnt list it, i thought it would be faster to user the id instead. also, the user_id field in the geames table is not the primary index but a foreign key.

tnx for the help and patience

10-24-2004, 08:53 PM
if you don't need any other data from the users-table, then you don't even need a join. Unless you also want to list users that haven't already played.
username should not be included in the games-table. All userdata need to be in the users-table and you can then get it with a join

Else you can just use

SELECT user_id, result, count(*) FROM games GROUP BY user_id, result

this will give you 1 or two records for each user_id with a count of each resultvalue.