Prepared Statement not returning anything

Question

Hi all,

Please forgive me for the possibility of the stupidity of this question, but I've just started using prepared statements. I know this particular query works, as I tested it with unprepared, procedural methods. Here it is:

$name = 'introduction';
$mysqli = new mysqli('localhost', 'user', 'pass', 'db') or die('There was a problem connecting to the database.');
$stmt = $mysqli->prepare("SELECT name, content FROM sections WHERE name = ?");
$stmt->bind_param('s', $name);
$stmt->execute();
$stmt->bind_result($content);
$stmt->fetch();
echo  $content;
$stmt->close();

Any advice or points in the right direction are greatly appreciated!

EDIT 1: Just an update on my progress as I try to troubleshoot. I realized that, since I have an id column as an index in the sections table, I needed to bind that as a result as well, given the above statement at php.net, (thanks again, Bill).

Here's the new code:

$name = 'introduction';
$mysqli = new mysqli('localhost', 'user', 'pass', 'db') or die('There was a problem connecting to the database.');
$stmt = $mysqli->prepare("SELECT name, content FROM sections WHERE name = ?");
$stmt->bind_param('s', $name);
$stmt->execute();
$stmt->bind_result($id, $name, $content);
$stmt->fetch();
echo $content;
$stmt->close();

Thanks again to all who can offer suggestions. (I'm curious: I find it hard to debug when using the OOP style of prepared statements in this way. Is there, for example, an easy way to simply see the query that was actually used?)

EDIT 2: Hi, and thanks for doing that test, I really appreciate it. I understand the question you've been forced to ask, as I've wracked my brain over it for a good while now, too. I've got to say it does exist.

If I do the following, just as a quick-and-dirty example:

$name = 'introduction';
@mysql_connect('host', 'user', 'pass');
@mysql_select_db('db');
$query = "SELECT name,content FROM sections WHERE name = '$name'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_object($result)) {
    $content = $row->content;
    echo $content;
}

My data appears and all is well. If, however, I do the following:

$name = 'introduction';
$mysqli = new mysqli('localhost', 'user', 'pass', 'db') or die('There was a problem connecting to the database.');
$stmt = $mysqli->prepare("SELECT name, content FROM sections WHERE name = ?");
$stmt->bind_param('s', $name);
$stmt->execute();
$stmt->bind_result($name, $content);
$stmt->fetch();
echo  $content;
$stmt->close();

Which I believe is correct (feel free to yell if not, of course), I get nothing. What's more, with that code, when I do an html validation (just in case), I get an internal server warning (500), which I take to be a problem with the sql code. Am I just nuts?

Thanks for your continued help, it's much-appreciated!

EDIT 3: Well, I feel like an idiot, but, I've fixed it, and Bill, your question was right on. I don't know how on earth I managed to put in incorrect database details and miss them for so long, but that's precisely what happened.

Thanks again for your patience and willingness to help.

Answer 1

Shouldn't that be

$stmt->bind_result($name, $content);

As you select 2 columns

Answer 2

I don't see anything wrong with your preparation of the statement or use of parameters, but there is something wrong in your binding results:

http://php.net/manual/en/mysqli-stmt.bind-result.php says:

Note that all columns must be bound after mysqli_stmt_execute() and prior to calling mysqli_stmt_fetch().

(emphasis mine)

---

The above doc should be taken as all columns in your query, not all columns in your table.

Okay, I just tried this myself. If I omit the $name column, it gives this warning:

PHP Warning:  mysqli_stmt::bind_result(): Number of bind variables doesn't 
match number of fields in prepared statement in mysqli.php on line 9
PHP Stack trace:
PHP   1. {main}() /Users/bill/workspace/PHP/mysqli.php:0
PHP   2. mysqli_stmt->bind_result() /Users/bill/workspace/PHP/mysqli.php:9

But it does fetch the data.

If I bind both $name and $content to the results of the query, it works without error or warning.

So I'm forced to ask you: are you sure there's a row in the database that matches your condition? That is, where name = 'introduction'? Keep in mind that in SQL, string comparisons are case-sensitive by default.

One mistake I see people make frequently is that they connect to a different database in their PHP script than the database they use for ad hoc queries. So you need to be absolutely sure you're verifying that the data exists in the right database.