Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    May 2004
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    foreach statement

    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.

  • #2
    Regular Coder
    Join Date
    May 2004
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #4
    Regular Coder
    Join Date
    May 2004
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you should be able to do that in one query, with a GROUP BY clause.

    post the tabledesign so we can write it out.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #6
    Regular Coder
    Join Date
    May 2004
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok,
    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
    Last edited by ClubCosmic; 10-24-2004 at 05:49 PM.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •