Hi Gang,

 

I'm trying out prepared statements with mysqli ... and damn they got me stumped. Sure there are loads of tutts for this but most of the ones I read are more blog candy to get the clicks up and don't explain why things are the way they are. Right now I've spent too long trying to convert a perfectly working query into a prepared query with nothing much to show for it.

 

Origional which works

 

$con = mysqli_connect("localhost","db","pass","user");
if(mysqli_connect_errno()){
echo mysqli_connect_error();
}
$find1 = "Food";
$find2 = "Colour";

$q = "SELECT * FROM `table` WHERE `Food` = '$find1' AND `Colour` = '$find2' ORDER BY `Colour` ASC";
$result = $con->query($q);
if($result) {
  while($row = $result->fetch_assoc()) {
     echo $row['Food'] . " " . $row['Colour'] . "<br>";
  }
}

 

And so far ...

 

$con = mysqli_connect("localhost","db","pass","user");
if(mysqli_connect_errno()){
  echo mysqli_connect_error();
}
$find1 = "Food";
$find2 = "Colour";

$q = "SELECT * FROM `table` WHERE `Food` = (?) AND `Colour` = (?) ORDER BY `Colour` ASC";
$yum = mysqli_stmt_init($con);
if(mysqli_stmt_prepare($yum, $q)) {
   mysqli_stmt_bind_param($yum, "ss", $find1, $find2);
   mysqli_stmt_execute($yum);
}

 

But now i'm stumped as to how to get the $result assoc array from the prepared statement - tried lots of tuts on bind etc to no avail.

 

Any ideas or a link to a really simple explanation would be ideal.

Edited May 14, 201412 yr by BrowserBugs

Ok missed a fundamental flaw to my attempts ... to summarise "Note that all columns must be bound by the application before calling mysqli_stmt_fetch()." *sigh* need more sleep :s

 

Edit: So it looks like this ...

 

$con = mysqli_connect("localhost","user","password","db");
// Check connection
if(mysqli_connect_errno()){
  echo mysqli_connect_error();
}

$search1 = "Chicken";
$search2 = "Spice";
$multidimentionalarray = array();
$query = "SELECT Col1, Col2, Col3, Col4 FROM table WHERE Col1 = ? AND Col2 = ?";
$stmt = mysqli_prepare($con, $query);
mysqli_stmt_bind_param($stmt, "ss", $search1, $search2);
mysqli_stmt_execute($stmt);
// This is now where you must bind each col
mysqli_stmt_bind_result($stmt, $rcol1, $rcol2, $rcol3, $rcol4);
// This adds each row as a row in the multidimentional array
while(mysqli_stmt_fetch($stmt)) {
   $newrow = array(
      'Meat' => $rcol1,
      'Style' => $rcol2,
      'Cooking' => $rcol3,
      'Level' => $rcol4
   );
   // Push it to array
   $multidimentionalarray[] = $newrow;
}
mysqli_stmt_close($stmt);
mysqli_close($con);

There's probably a quicker way but this works

Edited May 16, 201412 yr by BrowserBugs

Ok missed a fundamental flaw to my attempts ... to summarise "Note that all columns must be bound by the application before calling mysqli_stmt_fetch()." *sigh* need more sleep :s

One thing I have found very useful is stripping back frameworks and studying their prepared statements. It can be a bit of work to find exactly where they are but most frameworks have several different types of queries for the different DB's out there which means you get to compare how it is being done with other formats that you already know

Yeah that would be ok just I don't use frameworks (apart from jQuery) ... mainly because there's a whole heap of code I didn't write and if it goes wrong I wouldn't know where to start looking for the bug

Yeah that would be ok just I don't use frameworks (apart from jQuery) ... mainly because there's a whole heap of code I didn't write and if it goes wrong I wouldn't know where to start looking for the bug

With learning all things web I spend a lot of time looking for code examples that I can strip back, open source projects and in particular framework projects are great for that. I don't normally do more than some basic experiments with any framework to make sure I have figured out which pieces are doing what properly. It doesn't take much time to dump a framework once you have finished going through the pieces you want to learn from, and when they are free the only cost is my own time.

 

Plus there is the added bonus that open source projects usually have very useful forums that describe the sections of the project very clearly too.

 

For MySqli try the Yii framework it really does have quite a comprehensive set of commands and statements to work from that can be directly compared with other formats that you already know

Thanks for the link will take a peek.