Having issues with displaying database query results

[wolfcry]

Hey all,

 

First off, if this is in the wrong section I apologize. I wasn't sure if it should be here or the mySQL section.

 

What's going on is, I'm in the process of learning the Prepared Statement way of doing things and am changing / updating my code to reflect the changes. Everything was going fine until I attempted to do what I could do using old MySQL methods and that is display the queried results on the same page.

 

 

I can place a query and display the results as they should be displayed if I only use one block of code. However, if I try to do any additional queries on the same page, they get killed and do not display anything even though I know the query is fine because I can test the exact same syntax below one a different page and it works.

 

Here's a code snippet for an example:

Code:
<table>
<tr>
<td>
// The below code will display a selection box containing various strings such as "hello world", "great to be here", "Wowserz", "this is mind blowing" etc. that are stored in the database.

<?php

   echo "<select = \"SpecialConditions\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
   
       echo "<option value=\"$specialId\">$specialcondition</option>";
   }
}
   echo "</select>";
?>
</td>
<td>
// If I place another fetch query below the above fetch() query, this one will not show up. This one is supposed to display values 1 - 20 that have been stored in the DB.

<?php
echo "<select = \"NumberSets\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
      
          echo "<option value=\"".$numbers."\">".$numbers."</option>";
      }
   }
echo "</select>";

?>

</td>
</tr>
</table>

 

What am I doing wrong with this? When I use regular SQL queries I can display multiple results on the same page.

 

The results are being pulled from two separate joined tables but I don't think that's the issue.

[dharmeshpat]

according to me i think ur using same name for the variable or object for the queries please try to use different name for variable or query and than try it will get resolved

[wolfcry]

Hi dharmeshpat,

 

Thank you for you your reply, I greatly appreciate it.

 

I'm sorry, but I don't understand what you mean. Are you saying that I need to make multiple query statements (or objects) and use those instead? Shouldn't I be able to use one $stmt object  considering all of the information is being stored?

 

Here's how my connection is being set and stored.

 

$query = "SELECT `results`, `Success`, `Failure`, `Counter`, `Grades`, `Classes`, `Special_Id`, `SpecialCondition` 
FROM `Courses` INNER JOIN `Students` ON `id` = `Students_Id` JOIN `Conditions` ON `id` = `Special_Id`";

$stmt = $Mconn->prepare($query);
$stmt->bind_result($results, $success, $failed, $counter, $grades, $classes, $specialId, $specialcondition);
$stmt->execute();
$stmt->store_result();

 

Are you saying I need to do the following?

 

$query = "SELECT `results`, `Success`, `Failure`, `Counter`, `Grades`, `Classes`, `Special_Id`, `SpecialCondition` 
FROM `Courses` INNER JOIN `Students` ON `id` = `Students_Id` JOIN `Conditions` ON `id` = `Special_Id`";

$stmt = $Mconn->prepare($query);
$stmt->bind_result($results, $success, $failed, $counter, $grades, $classes, $specialId, $specialcondition);

$secondobject = $Mconn->prepare($query);
$secondobject->bind_result($results, $success, $failed, $counter, $grades, $classes, $specialId, $specialcondition);

$stmt->execute();
$secondobject->execute();

$stmt->store_result();
$secondobject->store_result();

 

That looks odd to me but then again, I'm just learning how to use Prepared Statements.

[wolfcry]

Ok, so I've been trying everything I can think of, including using $stmt->close(); in the corresponding code blocks and nothing is working. There has to be a way to display multiple queried results on the same page using Prepared Statements.

[wolfcry]

Nice, I got it to work finally. Everything needs to be stored in an array and extracted through that it seems. At least that's the only way I got it to work properly.

 

Here's the final solution in case anyone is curious or hits this wall in the future. If you know of a way that does not require this method, please let me know.

 

My Dynamic function that connects and queries the database:

<?php

function DB_Query(){
   $databasefields = array();
   $results = array();

@$Mconn = new mysqli(DBHOST, DBUSER, DBPWRD, DBNAME);
   
   if (!@mysqli_connect(DBHOST, DBUSER, DBPWRD, DBNAME)){
   die(DBERROR);
   }

$query = "SELECT `results`, `Success`, `Failure`, `Counter`, `Grades`, `Classes`, `Special_Id`, `SpecialCondition`
FROM `Courses` INNER JOIN `Students` ON `id` = `Students_Id` JOIN `Conditions` ON `id` = `Special_Id`";

$stmt = $Mconn->prepare($query);
$stmt->execute();
$meta = $stmt->result_metadata();

   while($field = $meta->fetch_field()){
   $databasefields[] = &$row[$field->name];
   }

   call_user_func_array(array($stmt, 'bind_result'), $databasefields);
   while($stmt->fetch()){
      $queried = array();
      foreach($row as $key => $vector){
           $queried[$key] = $vector;
      }

      $results[] = $queried;
   }
   
   return $results;
}

$results = DB_Query();  // placed in include file so I don't have to keep calling it on each page a query is needed.
?>

 

And here's the code I use to actually display the data singly or in multiple blocks (YAY! lol). Just change the field name to correspond to yours. The function is dynamic and doesn't require you to hard-code any binding results so you can fetch as many fields as desired.

 

<?php

foreach($results as $row){
echo $row['grades'].'<br />';
}

 

Hope that helps those who hit the same wall as I did