3

I have the following code which executes a MySQL query using prepared statements:

$selectStatement = "SELECT " . implode(", ", $allFields); // cat select w/ allFields
$stmnt = $mysqli->prepare($selectStatement . 
     "FROM Musicians WHERE name = ?");
$stmnt->bind_param('s', $name);
$stmnt.execute();

At this point, most of what I've read tells me that I should use bind_result() and then fetch() on $stmnt. However, I'd prefer to get an array filled with the values instead of iterating over a bunch of dumped variables, as fetch() does. Is there a way to get something similar to what fetch_assoc() would give me while using prepared statements and results?

user1427661
  • 11,158
  • 28
  • 90
  • 132
  • Why you no set an array in the execute method? – Sam Mar 03 '13 at 21:48
  • Because it returns a boolean value and accepts no arguments? – user1427661 Mar 03 '13 at 21:50
  • take a look: http://www.php.net/manual/en/mysqli-result.fetch-all.php – michi Mar 03 '13 at 22:08
  • Hm. Looks similar to mysqli_stmt::getresult(), but both of them require something called sqlnd, which doesn't appear to be on the server I'm working on, and I can't handily install and reinstall PHP on this server. That seems to be the only way to install sqlnd... – user1427661 Mar 03 '13 at 22:14

2 Answers2

3

You are right, to use familiar fetch() with mysqli one have to call get_result() first and it is not always available.
Not to mention that you can't bind variable number of placeholders without some sophisticated code too.
Which makes mysqli absolutely unusable with prepared statements.

So, there are 2 solutions.

  1. Do not use mysqli
  2. Do not use prepared statements.

For the first option one go for PDO.
Unlike mysqli, it has everything you need from mysqli and much more.

$stm = $pdo->prepare("SELECT * FROM Musicians WHERE name = ?");
$stm->execute(array($name));
$row = $stm->fetch(); // or fetchAll() depends on the numer of rows expected.

For the second you can use manually handled placeholders.
This approach will be even more useful than PDO, a real neat code:

$row = $db->getRow("SELECT * FROM Musicians WHERE name = ?s",$name);
Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • This was the main reason why I chose PDO when I evaluated mysqli. Being able to fetch associative arrays in regular queries but not in parametrized queries is an absurd restriction. (If you agree with me, go and upvote [this answer](http://stackoverflow.com/a/127218/13508) and bring some justice to the world.) – Álvaro González Mar 04 '13 at 11:44
0

To add a solution to clarify:

If we could choose the server at start, I prefer to use php-mysqlnd module, instead of php-mysql module, for PHP. Then, we could use:

...
$stmnt.execute();
$res = $stmnt->get_result();
$row = $res->fetch_assoc();

That seems simpler to me.

Johnny Wong
  • 945
  • 9
  • 16