php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #45289 Cannot use prepared statements and fetch_row together
Submitted: 2008-06-17 02:33 UTC Modified: 2009-09-21 15:16 UTC
Votes:7
Avg. Score:4.7 ± 0.5
Reproduced:7 of 7 (100.0%)
Same Version:0 (0.0%)
Same OS:2 (28.6%)
From: mike dot benza at rice dot edu Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.2.4 OS: *
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: mike dot benza at rice dot edu
New email:
PHP Version: OS:

 

 [2008-06-17 02:33 UTC] mike dot benza at rice dot edu
Description:
------------
It's not possible (as far as I can tell) to use a prepared statement in conjunction with mysqli_result::fetch_row or mysqli_result::fetch_assoc.

The inability to do it is not documented in the manual online.

There is no error given when you try to use fetch_row or fetch_assoc on a result.  In fact, it returns successfully, with garbage.

This is a similar issue as http://bugs.php.net/bug.php?id=42619&edit=2, but there is still no documentation of the behavior and no error thrown when trying to use fetch_row or fetch_assoc on a result from a prepared statement

(Note: I only have 5.2.4, since that's the highest available on my OS.  If you really want me to upgrade, I'll build it from source, but I think you know about this incompatibility, since it should be your primary use case; mysqli should be able to combine the security of prepared statements with the useful features of the old mysql extension)
(See also: http://www.robpoyntz.com/blog/?p=189, which doesn't work, and I haven't had a chance to figure out why)
(See also: http://blog.myhat.de/2007/06/26/pdo-and-extending-mysqli/)



Reproduce code:
---------------
$db = new mysqli(...);
$teamId = some integer;

$stmt = $db->prepare('SELECT * FROM teams where id=? LIMIT 1;');
if($stmt === FALSE) {
  die("Could not prepare statement");
}
if($stmt->bind_param('i', $teamId) === FALSE) {
  die("Could not bind params");
}
if($stmt->execute() === FALSE) {
  die("Could not execute statement");
}
if(($result = $db->store_result()) === FALSE) {
  die("Could not store result (db)." . $db->error);
}

$teamAsArray = $result->fetch_assoc();
print_r($teamAsArray);

Expected result:
----------------
I would expect the above code to have $teamAsArray be an associative array with the fields in my team table as the keys, and the values be the corresponding values.  At the very least, I would expect an error to be thrown since it can't.

I expect:
Array ( [id] => 1 [name] => Nicaragua II [updateTime] => 2008-06-05 00:35:43 [meetings] => Saturdays at 10:00pm in the Mech Lab [country] => Nicaragua [currentProjectId] => 3) 

Actual result:
--------------
An array full of garbage...but not senseless garbage, but rather the fields in the wrong spots, with some garbage mixed in between:

Array ( [id] => [name] => [updateTime] => ��� [meetings] => Nicaragua II [country] => ?�#+ [currentProjectId] => Saturdays at 10:00pm in the Mech Lab ) 



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-07-11 08:54 UTC] omolano at PLEASENOSPAMinicia dot es
I've also had this problem. In my case I was trying to use PDO::FETCH_ASSOC on a prepared statement and the database was SQLite 3 based. I also got null values, like the above poster, not garbage.
 [2008-07-22 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2009-05-15 13:39 UTC] jochen dot wiedmann at gmail dot com
I have the same problem with PHP 5.2.6, as delivered with Fedora 10 Linux.
 [2009-05-15 19:55 UTC] jochen dot wiedmann at gmail dot com
I have checked the latest snapshot (php5.2-200905151830), whether the problem still persists by using the script below. (Should be easy to derive a .phpt file from it.)

<?php
  $mysqli = mysqli_init();
  $mysqli->real_connect("localhost", "root", null, 'test');
  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }
  $mysqli->query("DROP TABLE IF EXISTS prep_and_fetch_row")
    or die($mysqli->error);
  $mysqli->query("CREATE TABLE prep_and_fetch_row(id BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64) NOT NULL)")
    or die($mysqli->error);
  $mysqli->query("INSERT INTO prep_and_fetch_row (id, name) VALUES (1, 'abc')")
    or die($mysqli->error);
  $stmt = $mysqli->prepare("SELECT * FROM prep_and_fetch_row WHERE id=?")
    or die($mysqli->error);
  $id = "1";
  $stmt->bind_param("i", $id)  or die($mysqli->error);
  $stmt->execute() or die($mysqli->error);
  $result = $mysqli->use_result() or die($mysqli->error);
  while ($row = $result->fetch_row()) {
    print "----- result row -----------\n";
    print_r($row);
  }
  print "----- no more results -----------\n";

  $mysqli->query("DROP TABLE prep_and_fetch_row") or die($mysqli->error);
  $mysqli->close();
?>

Expected output: Something like

----- result row -----------
Array
(
    [0] => int(1)
    [1] => string(3) "abc"
)
----- no more results -----------

Actual output:

----- result row -----------
Array
(
    [0] => 
    [1] => 
)
----- no more results -----------

I'd consider this to be a clear bug.
 [2009-05-28 18:16 UTC] uw@php.net
I think its bogus. Wrong use of the API. The critical part here is that you get a result set at all...
 [2009-09-21 15:16 UTC] uw@php.net
In this case mysql_store_result() should not return a result set. Closing because it is a matter of the MySQL Client Library and not PHP. See also http://bugs.mysql.com/bug.php?id=47485 . 
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Mon Nov 29 00:03:13 2021 UTC