Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: run the same query multiple times

    I was just playing around some, and was seeing how I could move my data around, and i threw two tables together of players and scores*. I can get the things I wanted to to display. I wrote the query to basically include everything. First I made it so each play with their scores summed together.

    Then pretended I needed to output more details for a random player (at my picking) below the all players table, and needed all his scores, versus the total. Well, this didn't show up. First, I changed the var that grabbed the assoc array, no go. Then something told me to move the second all above the all players call. And there was my random player results, and the all players call vanished.

    I was just wondering about this behavior. While all I am doing is outputting data twice on the same page from the same query, with some mods, I only can get one set to output. Do I really need two queries?

    *- I changed this to players and scores, just to give me some privacy. So don't write it off as a homework project.
    Ryan
    My Blog

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Please post your PHP/SQL code for us to help you out as we currently have no idea what you're doing....

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    PHP Code:
    <?php
    [connect to db]
    $q "Select field1..field3 etc From..";
    $result mysql_query($q);?>

    blah blah blah //some formating

    <?php while ($row mysql_fetch_assoc($result)){ //all players with scores
       
    echo $row['field1'];
       echo 
    $row['totalScore'];
       echo 
    $row['totalGames'];
    }
    ?>

    blah blah blah heres the top guy
    <?php while($row mysql_fetch_assoc($result)){ //note I also tried using $row1
       
    echo $row['gameDate'];
       echo 
    $row['gameScore'];
       echo 
    $row['gOppenent'];
    }
    ?>
    So as is the top guy will not appear. If you cut that and have that code block for the top guy appear before the totals, the totals vanish. So, I was wondering how or if you could do this. Or do you need a new query for the top?
    Ryan
    My Blog

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Is it me or are you only running one query?
    Code:
    $q = "Select field1..field3 etc From..";
    $result = mysql_query($q);?>
    In the above your $q variable contains the SQL string that mysql_query is executing. What does this SQL string look like?

    I'm guessing here (because your SQL string isn't fully written out) that you're doing something like "SELECT field1,totalScore,totalGames FROM userinformationtable WHERE user = 'some particular user' ".

    Thus when you execute your query using mysql_query you're going to get back that exact information (i.e. nothing related to a scoreboard for everyone).

    Also it's worth learning how mysql_fetch_assoc works. When you hand it a result set ($result) your while loop will iterate through it, mysql_fetch_assoc each time fetching the next record set. When it reaches the end your loop stops. It now means the record set it as the end. So any further attempts to loop using mysql_fetch_assoc will fail (because it's already at the end).



    So, what you should be doing is the following (pseudo code)
    Code:
    [connect to db]
    $q = "SELECT userinformation FROM usertable for particularuser";
    $result = mysql_query($q);
    
    [while loop]
    fetch record for user
    echo out info
    [/loop]
    
    $newq = "SELECT AllInfo FROM scorestables";
    $result = mysql_query($newq);
    [loop]
    fetch record for each user
    echo out info
    [/loop]

    Edit : any further information on the DDL for your tables will help me perfect the above psuedo code for you.

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Yes, I am only using one query, and its not fully written. That query both includes data for both things I am trying to do.

    Another way to phrase this is I have a query that grabs field1, field2, and field3. At the start of my page, I'd need field1 and field2. Midway through I need field1 and field3. Can I grab that query twice?
    Ryan
    My Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you grab them all at once and shove them into variables which can be called at any point throughout the page?

    Remember to close the recordset as soon as you are done with it!
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    grab all what at once? How would I shove them into variables? I mean I know what you mean by make them variables ie, $fName = $row['firstName'];, but you can only see firstName after you run the query and parse it via mysql_fetch_assoc(), correct?

    I do close the connection to the db, just forgot to type it. It was 5:30 am here :P
    Ryan
    My Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Scratch what I said - misread your code
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    As georgev suggests:

    Code:
    <?php
    $recordset = array();
    while ($row = mysql_fetch_assoc($result)){ 
      // Build an array here.
      $recordset[] = $row;
    }
    
    // Output everyone.
    foreach($recordset as $record){
      echo $record['field1'];
      echo $record['field2'];
    }
    
    // Now output a particular player
    $playernum = 5;
    echo $recordset[$playernum]['field1'];
    echo $recordset[$playernum]['field2'];
    
    ?>
    Build a recordset and use that.
    BTW i don't think you read my post carefully enough or you would have noticed what I said about mysql_fetch_assoc() reaching the end, and hence your inability to use it again.

    Also: please post your SQL query because I think you're falling down at this point. Are you doing a JOIN, UNION, or subselect ? Are you adding in LOADS of data you really don't need, and hence are you better off creating a separate query for getting that single player...

  10. #10
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Sorry I answer your posts at 5:30 or so in the morning. So back up I do not have an Where clause in my SQL. I do know what mysql_fetch_assoc does, it returns an associative array of the query. So I would need to either write a new query, or write the blurbs about what I need through echos then.
    Ryan
    My Blog

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    You have still failed to show us what SQL query you are doing... we can't deduce much unless you show us that.

    Also, have you tried the solution I supplied in my previous post? Obviously it'll need manipulating to your particular problem, but it should suffice.

    I suspect what you're doing is fundamentally flawed by your SQL query and until we know what that is we've pretty must exhausted our possible "suggestions".

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    My sql is not flawed at all, thanks. My original question has been answered. I did not post my original query since it doesn't matter at all what it was. The SQL is valid....

    All I wanted to know is if I had a query that called fields 1 through 6 for example, could I call fields 1-4 in one while loop and 1,5,6 in another.
    Ryan
    My Blog

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I get the impression that the SQL you are doing is involving a JOIN (and possible count or grouping). My reasoning behind asking about the SQL query was that all you need to do is the following :
    SELECT totalScore,totalGames FROM thegeneraltable
    and then
    SELECT userfield,userfield FROM gamedatatable WHERE user_id = someuserid

    The overhead of doing this is WAY less than a JOIN, COUNT, or other and getting a MASSIVE recordset of which at least 2,3 columns you'll never use.

    Just a thought really. Something tells me however you don't understand how to run two separate queries in PHP, which was partly what I was trying to explain to you.

    note: I don't suspect that your SQL is flawed at all. I suspect your logical thinking is

  14. #14
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Ah I see, I don't what to point to, but I just had a feeling, you had, or its better, to make a big query and break it up as needed.
    Ryan
    My Blog

  15. #15
    Join Date
    Dec 2007
    Posts
    1
    $row = mysql_fetch_assoc($result)

    Every time you call the above function it moves the internal pointer to the next record in the dataset.
    Therefore because you are using it in the first loop, by the time the loop finishes there are no more records to be shown (end of records).
    You must use a second query to run through the same dataset in a second loop.

    Otherwise if you need to use data from different columns(using only the first query) you must use a nested loop.

    From the look of your code it seems as though you need two queries anyway. One for all the players and a second just showing the top player.
    Can't really tell from the small amount of code. If you expect help you need to post the whole code.

    A short way to use the same query twice could look like this although i haven't tested it. Otherwise re-declare the second query as other users have already shown.

    Code:
    <?php
    [connect to db]
    $q = "Select field1..field3 etc From..";
    $result =  mysql_query($q);
    $result2 = $result1;
    ?>
    
    blah blah blah //some formating
    
    <?php while ($row = mysql_fetch_assoc($result)){ //all players with scores
       echo $row['field1'];
       echo $row['totalScore'];
       echo $row['totalGames'];
    }
    ?>
    
    blah blah blah heres the top guy
    <?php while($row = mysql_fetch_assoc($result2)){ //note I also tried using $row1
       echo $row['gameDate'];
       echo $row['gameScore'];
       echo $row['gOppenent'];
    }
    ?>
    Last edited by timgraham; 12-14-07 at 22:07.

Posting Permissions

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