Is it possible to use mysqli_fetch_object with a prepared statement

Question 1

All the examples I see using mysqli_fetch_object use mysql_query(), I cannot get it to work with prepared statements. Does anyone know what is wrong with this code snippet, as fetch_object returns null.

$sql = "select 1 from dual";
printf("preparing %sn", $sql);
$stmt = $link->prepare($sql);
printf("prepare statement %sn", is_null($stmt) ? "is null" : "created");
$rc = $stmt->execute();
printf("num rows is %dn", $stmt->num_rows);
$result = $stmt->result_metadata();
printf("result_metadata %sn", is_null($result) ? "is null" : "exists");
$rc = $result->fetch_object();
printf("fetch object returns %sn", is_null($rc) ? "NULL" : $rc);
$stmt->close();

The output is:

preparing select 1 from dual
prepare statement created
num rows is 0
result_metadata exists
fetch object returns NULL

Question 2

I don't believe the interface works like that.

Going by the documentation and examples (http://www.php.net/manual/en/mysqli.prepare.php) it seems that $stmt->execute() does not return a resultset, but a boolean indicating success / failure (http://www.php.net/manual/en/mysqli-stmt.execute.php). To actually get the result, you need to bind variables to the resultset (aftere the execute call) using $stmt->bind_result (http://www.php.net/manual/en/mysqli-stmt.bind-result.php).

After you did all that, you can do repeated calls to $stmt->fetch() () to fill the bound variables with the column values from the current row. I don't see any mention of $stmt->fetch_object() nor do I see how that interface could work with a variable binding scheme like described.

So this is the story for "normal" result fetching from mysqli prepared statments.

In your code, there is something that I suspect is an error, or at least I am not sure you intended to do this. You line:

$result = $stmt->result_metadata();

assignes the resultset metadata, which is itself represented as a resultset, to the $result variable. According to the doc (http://www.php.net/manual/en/mysqli-stmt.result-metadata.php) you can only use a subset of the methods on these 'special' kinds of resultsets, and fetch_object() is not one of them (at least it is not explicitly listed).

Perhaps it is a bug that fetch_object() is not implemented for these metadata resultsets, perhaps you should file a bug at bugs.mysql.com about that.

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

Only data can be bound with placeholders.

Column/table names are part of the schema and cannot be bound. (The fact that it generates "odd results" instead of simply yielding an error is a peculiarity of the implementation.)

I would suggest using a white-list of column-names and controlled string interpolation.

Question 6

"Yes", but it won't do what you expect.

The expression used for the switch is evaluated once - in this case contains evaluates to true/false as the result (e.g. switch(true) or switch(false)) , not a string that can be matched in a case.

As such, the above approach won't work. Unless this pattern is much larger/extensible, just use simple if/else-if statements.

var loc = ..
if (loc.contains("google")) {
  ..
} else if (loc.contains("yahoo")) {
  ..
} else {
  ..
}

However, consider if there was a classify function that returned "google" or "yahoo", etc, perhaps using conditionals as above. Then it could be used as so, but is likely overkill in this case.

switch (classify(loc)) {
   case "google": ..
   case "yahoo": ..
   ..
}

While the above discusses such in JavaScript, Ruby and Scala (and likely others) provide mechanisms to handle some more "advanced switch" usage.

Is it possible to use mysqli_fetch_object with a prepared statement

Answers

php,mysqli