Simple PHP Select with Prepared Statements Displays No Results

Question

I am trying to create a simple PHP script that selects data using prepared statements. Currently, I am using this tutorial as a source. When I call my script, I have no errors in the error.log, but no data is displayed. Here is my script:

<?php
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";  

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$stmt = $conn->prepare("SELECT * FROM Regions WHERE id=?");
$stmt->bind_param("i", $_POST['id']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    $name[] = $row['name'];
    $center[] = $row['center'];
    $content_string[] = $row['content_string'];

  }
}
$stmt->close();
echo $name;
echo $center;
echo $content_string;

$conn->close();
?>

My structure looks like this:

I have tried to access the output by calling http://URL/php/protected.php?id=1 and http://URL/php/protected.php to no avail. I've narrowed down that the error is happening because if($result->num_rows > 0) never gets evaluated. From that, I figure that the error must be in my SQL statement.

`$stmt->execute` is true or false? Have you checked for errors on the execution? — chris85, Aug 01 '17 at 01:34
An `int` can't hold 48 decimal places if that is what you plan to do.. — chris85, Aug 01 '17 at 01:43
My bad, sorry. I was thinking the first parameter was the binding reference. — fubar, Aug 01 '17 at 01:44

D-Marc · Answer 1 · 2018-01-06T22:50:48.100

I am actually the author of that tutorial lol. Did you check the section on error handling? You should be able to get a meaningful message by following that.

For instance

if ( !$stmt = $conn->prepare("SELECT * FROM Regions WHERE id=?") ) 
 echo "Prepare Error: ($conn->errno) $conn->error";
if ( !$stmt->bind_param("i", $_POST['id']) )
  echo "Binding Parameter Error: ($conn->errno) $conn->error";
if ( !$stmt->execute() ) 
 echo "Execute Error: ($stmt->errno)  $stmt->error";
if ( !$stmt->get_result() ) //Only for select with get_result()
 echo "Getting Result Error: ($conn->errno) $conn->error";
if ( !$stmt->store_result() ) //Only for select with bind_result()
 echo "Storing Result Error: ($conn->errno) $conn->error";
$stmt->close();

Edit: A more elegant way to do this would be with exception handling. Keep in mind that if mysqli_report() is used before creating the connection, then it will output your database password on errors. You should be using either a try/catch or custom exception handler printing to your error log anyway, but it's worth pointing out. Also, make sure to do display_errors = Off and log_errors = On in your php.ini

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $conn->prepare("SELECT * FROM Regions WHERE id=?");
$stmt->bind_param("i", $_POST['id']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    $name[] = $row['name'];
    $center[] = $row['center'];
    $content_string[] = $row['content_string'];

  }
}
$stmt->close();

But as RichGoldMd pointed out, you also should be doing something like the following, which is a very common reason for errors in my experience.

$id = (!empty($_POST['id']) ? $_POST['id'] : '');

score 1 · Answer 2 · answered Aug 14 '17 at 00:15

Do you own the server this is running on (VPS or dedicated), or is this a shared hosting environment? When I tried running your code, I got this error:

PHP Fatal error:  Call to undefined method mysqli_stmt::get_result()

A fatal error on the get_result line would certainly prevent if($result->num_rows > 0) from being evaluated. That error message led me to this question, which suggests that you can't use get_result without the the mysqlnd extension installed. With PHP 5 on Debian/Ubuntu, this would be the php5-mysqlnd package.

If you aren't able to install that extension, and this is indeed your problem, you won't be able to use get_result, meaning you'll need to use the far uglier bind_result method, which would look something like this:

<?php
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";  

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$stmt = $conn->prepare("SELECT name, center, content_string FROM Regions WHERE id=?");
$stmt->bind_param("i", $_POST['id']);
$stmt->execute();
$stmt->bind_result($row_name, $row_center, $row_content_string);
if($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    $name[] = $row_name;
    $center[] = $row_center;
    $content_string[] = $row_content_string;

  }
}
$stmt->close();
echo $name;
echo $center;
echo $content_string;

$conn->close();
?>

It's highly advisable to not use SELECT * when using bind_result, since the order of columns returned by the database matters.

Personally, I'd recommend looking into using PDO instead of mysqli, because I think the syntax is a lot friendlier than mysqli's and there's a lot less "gotchas" like the one I described above. Here's a decent tutorial to introduce you to PDO.

score -1 · Answer 3 · answered Aug 01 '17 at 02:44

You are calling you script with a query parameter - which is probably a GET if you are doing it in the browser, but retrieving the value from $_POST - Furthermore, you should ensure that the array index exists before you bind it - that is likely the problem, that $_POST['id'] doesn't exist when you call bind_param().

Before running the query you should test it:

if (isset($_POST['id']) {
   // ... do the query...
}

Simple PHP Select with Prepared Statements Displays No Results

3 Answers3