php, using a SELECT statement in prepare, how do I get the fetch_assoc array?

Question

I have been playing around with PREPARE and bind_param in php. I am trying to get it to work for a SELECT statement and display the results. When I try the statement here I get 0 results for the search. If I use query, it works fine and the results are displayed. Is it not possible to do this using prepare? Or is my logic wrong using $result = $stmt->execute(); to get the result?

<?php


$dbhost = 'localhost'; //default
$dbuser = 'root';
$dbpass = 'somepassword';
//Create a connection object
$conn = new mysqli($dbhost, $dbuser, $dbpass);
if($conn->connect_error )
{
  die('Could not connect: %s' . $conn->connect_error);
}
echo "Connected successfully<br>";
//Select the database call the method from out conn object
$conn->select_db("TUTORIAL");

$stmt=$conn->prepare("SELECT tutorial_author FROM tutorial_info WHERE tutorial_title=?;");
$stmt->bind_param("s",$tutorial_title);

$tutorial_title="Math";
$result = $stmt->execute();

//We get a false if it fails
if($result===FALSE) {

  echo "Select failed <br>";

}

else {

  if ($result->num_rows > 0) {

      while($row = $result->fetch_assoc()) {
          echo "Tutorial Title: " . $row["tutorial_title"]. " - Name: " . $row["tutorial_author"]. "<br>";
      }
  } else {
      echo "0 results";
  }



}






//Close the database
$conn->close();




 ?>

[The manual](http://php.net/manual/en/mysqli-stmt.execute.php) says `->execute()` always returns a boolean. — GolezTrol, Jul 22 '15 at 12:48
Your query failed to execute that is why it returned 0. see my answer it might help — Meenesh Jain, Jul 22 '15 at 12:50
Why do I need to declare it first? That defeats the whole point of using prepare. When you use prepare you can declare the values after and use execute. — Kex, Jul 22 '15 at 12:51

score 1 · Accepted Answer · answered Jul 22 '15 at 12:54

1

$stmt->execute() returns a boolean indicating success. In your code, you try to read $result->num_rows and other properties of $result, while $result should be true at that point (not an object).

Use the $stmt->fetch() to fetch records one by one in bind result parameters, or use $stmt->get_result() to get a MySQLi_Result object that has more functionality to fetch the data all at once or one row at a time.

answered Jul 22 '15 at 12:54

GolezTrol

109,399
12
170
196

so I changed my code to $tutorial_title="Math"; $stmt->execute(); $result = $stmt->get_result(); and then leave the if statements and while loops after the same. This should work right? Not getting anything though now. Just "Connected successfully". – Kex Jul 22 '15 at 13:08
That should work indeed. Hard to see what is going wrong. Can you use echo in certain places to see to where the script runs? Also, you can use `var_dump($result)` to check if it really is a result object and if it has any interesting properties. If I read it right, you should always get something, either 'Select failed' or '0 results, or a table. – GolezTrol Jul 22 '15 at 13:56
I get this Fatal error: Call to undefined method mysqli_stmt::get_result() in /public_htm/index.php on line 46 – Kex Jul 22 '15 at 14:01
Think I found the problem...missing driver ...http://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result – Kex Jul 22 '15 at 14:06
Yeh, using bind and fetch now and it's working great. thanks for your help – Kex Jul 22 '15 at 14:25

php, using a SELECT statement in prepare, how do I get the fetch_assoc array?

1 Answers1