Issues

ZF-2112: Zend_Db_Adapter_Interface::fetchCol() does not properly retrieve a column on single column SQL statements.

Issue Type: Bug Created: 2007-10-29T20:18:45.000+0000 Last Updated: 2012-03-28T02:12:07.000+0000 Status: Resolved Fix version(s): - 1.8.3 (09/Jun/09)

Reporter: Kekoa Vincent (kekoav) Assignee: Satoru Yoshida (satoruyoshida) Tags: - Zend_Db

Related issues: - ZF-6788

Attachments:

Description

When using a Mysqli adapter(not sure if this occurs with others) the following produces an error:

<pre class="highlight">

Warning: mysqli_stmt::bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in /usr/home/sciworld/application/library/Zend/Db/Statement/Mysqli.php on line 241

This occurs independent of if a column index is provided.

Interestingly, the following code does work correctly:

$stmnt = $db->query("SELECT areaID FROM userPermissions WHERE userID='$userID'"); $column = $stmnt->fetchColumn();```

It seems perhaps the parsing of the statement in the first case is not functioning properly, although admittedly I don't have much experience with Zend_Db. I do know the documentation does indicate that the first case should work identically to the second, which is not the case(unless I am missing something here).

Comments

Posted by Wil Sinclair (wil) on 2008-03-21T17:05:34.000+0000

This issue should have been fixed for the 1.5 release.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:59.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:54.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Simon Mundy (peptolab) on 2008-05-26T18:50:48.000+0000

What's the final SQL query? What does $userID resolve to? I can't reproduce this here but if you can supply any additional information it will help immensely.

Posted by Cole Snodgrass (cole.snodgrass) on 2008-06-25T12:44:12.000+0000

It appears that this is still happening (verified in 1.5.2) if the where statement is searching for a column with an id of 0 and the column being returned has a 0 in it.

Repro:

<pre class="highlight">
mysql> CREATE TABLE cds_delete ( id SMALLINT NOT NULL DEFAULT 0, name VARCHAR(10) NOT NULL DEFAULT '') ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO cds_delete VALUES (0, 'zero'), (1, 'one'), (2, 'two');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from cds_delete;
+----+------+
| id | name |
+----+------+
|  0 | zero | 
|  1 | one  | 
|  2 | two  | 
+----+------+
3 rows in set (0.00 sec)

<?php
require_once 'Zend/Db.php';
$db = Zend_Db::factory('Mysqli', array(...));

var_dump(
    $db->fetchCol("SELECT id FROM cds_delete WHERE id IN (0, 1, 2)"),
    $db->fetchCol("SELECT id FROM cds_delete WHERE id IN (1, 2)"),
    $db->fetchCol("SELECT name FROM cds_delete WHERE id IN (0, 1, 2)"),
    $db->fetchCol("SELECT name FROM cds_delete WHERE id IN (1, 2)")
);
?>

Expected Result:

<pre class="highlight">
array(3) {
  [0]=>
  int(0)
  [1]=>
  int(1)
  [2]=>
  int(2)
}
array(2) {
  [0]=>
  int(1)
  [1]=>
  int(2)
}
array(3) {
  [0]=>
  string(4) "zero"
  [1]=>
  string(3) "one"
  [2]=>
  string(3) "two"
}
array(2) {
  [0]=>
  string(3) "one"
  [1]=>
  string(3) "two"
}

Actual Result:

<pre class="highlight">
array(0) {
}
array(2) {
  [0]=>
  int(1)
  [1]=>
  int(2)
}
array(3) {
  [0]=>
  string(4) "zero"
  [1]=>
  string(3) "one"
  [2]=>
  string(3) "two"
}
array(2) {
  [0]=>
  string(3) "one"
  [1]=>
  string(3) "two"
}

Notice that the first fetchCol is returning an empty arrays instead of the expected results.

Posted by Cole Snodgrass (cole.snodgrass) on 2008-06-25T13:10:29.000+0000

I traced this issue down to the Zend_Db_Statement::fetchAll function and determined this is due to improper type checking.

<pre class="highlight">
    public function fetchAll($style = null, $col = null)
    {
        $data = array();
        if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
            $col = 0;
        }
        if ($col === null) {
            while ($row = $this->fetch($style)) {
                $data[] = $row;
            }
        } else {
            while ($val = $this->fetchColumn($col)) {
                $data[] = $val;
            }
        }
        return $data;
    }

Notice that both while loops are not checking the types returned from fetchColumn and fetch, therefore anything that could evaluate to a false will cause the while loop to terminate. This is why when the integer 0 is being returned by the fetchColumn method, nothing is being returned.

Updating the code to verify that a boolean false was returned appears to fix this issue (I can only vouch that this fixes the fetchCol issue, I didn't bother to look into what fetch should/could return so that should still be verified).

<pre class="highlight">
    public function fetchAll($style = null, $col = null)
    {
        $data = array();
        if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
            $col = 0;
        }
        if ($col === null) {
            while (($row = $this->fetch($style)) !== false) {
                $data[] = $row;
            }
        } else {
            while (($val = $this->fetchColumn($col)) !== false) {
                $data[] = $val;
            }
        }
        return $data;
    }

Posted by Wil Sinclair (wil) on 2009-01-06T14:33:40.000+0000

This issue has gone unaddressed for too long. I'm re-assigning to Ralph for re-evaluation and categorization.

Posted by Ralph Schindler (ralph) on 2009-01-10T10:22:48.000+0000

Will evaluate within 2 weeks

Posted by Tomasz Gunerski (tommygun) on 2009-01-21T20:05:16.000+0000

Cole Snodgrass solution is incorrect for Mysqli adapter. False is returned only when error will occurre, when stmt cursor reaches the end of data NULL will be returned. More about this here: http://php.net/manual/en/mysqli-stmt.fetch.php

Proper solution:

<pre class="highlight">
public function fetchAll($style = null, $col = null)
{
    $data = array();
    if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
        $col = 0;
    }
    if ($col === null) {
        while ($row = $this->fetch($style)) {
            $data[] = $row;
        }
    } else {
        while (false !== $val = $this->fetchColumn($col)) {
            if ($val === null)
                break;
            $data[] = $val;
        }
    }
    return $data;
}

Posted by old of Satoru Yoshida (yoshida@zend.co.jp) on 2009-05-16T18:02:42.000+0000

can not reproduce

Posted by old of Satoru Yoshida (yoshida@zend.co.jp) on 2009-05-29T07:25:01.000+0000

Reopen for ZF-6788

Posted by old of Satoru Yoshida (yoshida@zend.co.jp) on 2009-05-29T07:59:06.000+0000

add "false !== " condition to "$val = $this->fetchColumn($col)".

But I think the problem with mysql should be issue on fetch() method.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts