SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Apr 2007
    Location
    Clare, Ireland
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Accessing single query result multiple times

    I want to create a table with x rows where x is determined by the value of $num_subjects:
    PHP Code:
    $num_subjects $_REQUEST['num_subjects'];
    $num_subjects = (int)( trim($num_subjects)); 
    So far so good - no problem here.
    Each row contains a select drop-down menu which is populated with a list of subjects from the same database table each time. The following code does the trick:
    PHP Code:
    for($j=0$j<$num_subjects$j++) { //loop by number subjects required
        
    echo "<tr><td>";
             echo 
    "<select name=\"code[$j]\" id=\"code\">";
                echo 
    "<option></option>\n";
            
    $query "SELECT * FROM fetac_modules 
                ORDER BY module, level"
    ;        
            
    $result mysql_query($query);
            @
    $num_results mysql_num_rows($result);
                for (
    $i=0$i<$num_results$i++) {
                    
    $row mysql_fetch_array($result);
                    
    $code $row["code"];
                    
    $module stripslashes($row["module"]);  
                    
    $level $row["level"];  
                    echo 
    "<option value=\"$code\">$module ($level)</option>\n";
                } 
    // end inner loop
                
    echo "</select>";
            echo 
    "</td><td>";
    // end for 
    The problem here is that the same query is run x number of times and the higher x is the slower and more resource-intensive the query is. So how do I run the query once and then access the array that holds the results x number of times. When I tried this, the select drop-down for the first row was fine but was empty for all subsequent rows. I don't understand what is going on. Here is the code for this second scenario:
    PHP Code:
    // run query once
    $query "SELECT * FROM fetac_modules 
                ORDER BY module, level"
    ;        
            
    $result mysql_query($query);
            @
    $num_results mysql_num_rows($result);
                    
    $row mysql_fetch_array($result);

    for(
    $j=0$j<$num_subjects$j++) { //loop by number subjects required
        
    echo "<tr><td>";
             echo 
    "<select name=\"code[$j]\" id=\"code\">";
                echo 
    "<option></option>\n";
            for (
    $i=0$i<$num_results$i++) {
                    
    $code $row["code"];
                    
    $module stripslashes($row["module"]);  
                    
    $level $row["level"];  
                    echo 
    "<option value=\"$code\">$module ($level)</option>\n";
                } 
    // end inner loop
                
    echo "</select>";
            echo 
    "</td><td>";
    // end for 
    I'd appreciate any pointers as to what I'm missing. TIA

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code php:
    $query = "SELECT...";
    $result = mysql_query($query);
    while($row = mysql_fetch_array($result))
    {
        //do your stuff here
    }
    Note that you don't actually need to know how many rows there are; each iteration of the loop will attempt to fetch the next row in the resultset, and will return false when there are no more rows.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    To reuse one query multiple times, use this

    PHP Code:

    mysql_data_seek
    ($rs0); 
    It will reset the internal pointer so you can reuse the resource pointer

    And as Kromey pointed out, your code should look more like this:

    PHP Code:
    // run query once

    $query "SELECT * FROM fetac_modules 

                ORDER BY module, level"
    ;        

            
    $result mysql_query($query);


    for(
    $j=0$j<$num_subjects$j++) { //loop by number subjects required

        
    echo "<tr><td>";

             echo 
    "<select name=\"code[$j]\" id=\"code\">";

                echo 
    "<option></option>\n";
    mysql_data_seek($result0);
    while (
    $row mysql_fetch_assoc($result)) {
          
      echo 
    "<option value='$row[code]'>".stripslashes($row['module']).$row[level]</option>";
    }
                echo 
    "</select>";

            echo 
    "</td><td>";

    // end for 
    or

    PHP Code:
            $result mysql_query("SELECT * FROM fetac_modules ORDER BY module, level");
    $optionlist '';
    while (
    $row mysql_fetch_assoc($result)) {
          
    $optionlist .=  "<option value='$row[code]'>".stripslashes($row['module']).$row[level]</option>";
    }

    for(
    $j=0$j<$num_subjects$j++) { //loop by number subjects required

            
    echo "
                <tr>
                      <td>"
    ;
            echo 
    "<select name=\"code[$j]\" id=\"code\">";
            echo 
    $optionlist;
            echo 
    "</select>";
            echo 
    "
                      </td>
               </tr>"
    ;

    much more efficient and faster

  4. #4
    SitePoint Member
    Join Date
    Apr 2007
    Location
    Clare, Ireland
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, I'll try that tomorrow as I'm too tired on this bank holiday Friday night to do any more coding.


Bookmarks

Posting Permissions

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