Multiple Queries Using Prepared SQL Statements

PHP
1

Hello all,

I’ve recently begun the tedious work of learning the new prepared statements route for both security purposes and OO purposes, but I ran into a snag when trying to fetch() and display various results from either the same table, a different table and from both non-joined and joined tables.

A code sample follows:


<?php

//Display Dice Notations (Sides or Types);

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

?>

That bit of code works to display the fetched results properly, however, if I attempt another fetch request (either from same request or fetching results from a different table) to display the data below the first code block, nothing happens though I know the fetch is working because I can test it on a different page and it displays the results.

Now the statements are in table cells for testing purposes to keep everything formatted but I can’t see why it’s not working when I can get this to work using older mySQL query methods.

Any help would be greatly appreciated.

2

Could you post some more of your script, it’s hard to tell anything looking at just the fetch-statements.

3

Are you using Mysqli or PDO?

if it were PDO you may have something like this to loop through a result set:


foreach ( $stmt->fetchAll(PDO::FETCH_OBJ) as $row ){
echo echo "<option value=\\"".$row->dicetype."\\">".$row->dicetype."</option>"; 

}

** from memory, untested, and assuming dicetype is a column, or an alias name, in your table.

Either way, fetch() seems designed to fetch a single row, not sure of the equivalent mysqli syntax, sorry.

As said, post a bit more code.

4

Hi netgrubber,

I don’t know if this is what you are after, and not knowing if you are using PDO, this might help:


$o_Db = new PDO('mysql:host=localhost;dbname=things', 'user', 'secret');
//Query with bound parameters
$sql ="
SELECT
  products.description as 'Description'
  ,categories.name as 'Category'
FROM
  products as pr
INNER JOIN categories as ca
  ON ca.id = pr.id
WHERE pr.product = :product
AND ca.name = :category;";

$stmt = $o_Db->prepare($sql);

//bind prodcuct and category
$stmt->bindValue(':product', $product); // earlier (not shown) got $product from form submission or some other source...
$stmt->bindValue(':category', $category);

// First Product Group
$category = 'Sporting Goods';
$stmt->execute();
$first_product_group = $stmt->fetchALL(PDO::FETCH_OBJ);

// Second Product Group
$category = 'Kitchen';
$stmt->execute();
$second_product_group = $stmt->fetchALL(PDO::FETCH_OBJ);

Then process them as CUPS described in the foreach loop.

Obviously this is a contrived example but it gives the general approach to reusing a query by binding parameters.

Regards,
Steve

5

Hey all,

Thanks for the replies, I greatly appreciate it.

I love the examples, unfortunately I’m using Prepared Statements and haven’t learned PDO yet.

Here’s the database connection and query statements.


@$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-&gt;prepare($query);
$stmt-&gt;bind_result($results, $success, $failed, $counter, $grades, $classes, $specialId, $specialcondition);
$stmt-&gt;execute();
$stmt-&gt;store_result();

Sorry, I originally thought I had posted that information.

I’ve even tried closing the connection using $stmt->close(); within the code blocks but nothing seems to work.

6

Sorry my mistake – I’m clogging up your thread, you are clearly using mysqli, you don’t need to use PDO as well. Most people use one or the other.

7

Nah, clog away. I need to get this resolved because it’s causing me to go gray and bald lol. I think after I learn how to work PS properly, I’m going to go hit the PDO style up as well.

8

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:


&lt;?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-&gt;prepare($query);
$stmt-&gt;execute();
$meta = $stmt-&gt;result_metadata();

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

   call_user_func_array(array($stmt, 'bind_result'), $databasefields);
   while($stmt-&gt;fetch()){
      $queried = array();
      foreach($row as $key =&gt; $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.
?&gt;

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.


&lt;?php

foreach($results as $row){
	echo $row['grades'].'&lt;br /&gt;';
}

Thanks for all the help everyone!

9

Ahhh! Lovely.

Like I say, I’m no mysqli man, so maybe what you posted can be improved, but if you’ve scored that, then hurray for you. :slight_smile: Hopefully you have made a thing you can use over and over.