Re: [PDO] Number of rows found by Select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



So would it be possible to write a select() function to handle the wierdness? I'm attempting to write one but I'm getting buffered query errors. This function is part of a Database Manager class and is supposed to return a PDO::Statement with the number of rows being stored in a referenced paramater.

Error:
'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in E:\Server\swi\qpf\mng\data.class.php:64 Stack trace: #0 E:\Server\swi\qpf\mng\data.class.php(64): PDOStatement->execute() #1 E:\Server\swi\www\db.php(13): DM::select('* FROM cdrs WHE...', 20, -1) #2 {main} thrown in E:\Server\swi\qpf\mng\data.class.php on line 64


Function:

public static function select($sql, &$count = NULL, $limit = NULL)
{
    $db = self::handle(); #Singleton method to create/retrieve db handle
    $buf = array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE);

    $sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;
		
    $stmt = $db->prepare($sql, $buf);
    $stmt->execute();

    if (!is_null($count)) {
        $rows = $db->prepare('SELECT found_rows() AS rows', $buf);
        $rows->execute(); #ERROR HERE
        $rows_array = $rows->fetch(PDO::FETCH_NUM);
        $rows->closeCursor();
        $count = $rows_array[0];

        if (!is_null($limit) && $count > $limit) {
            $count = $limt;
        }
    }

    return $stmt;
}

Where am I going wrong here?

Rob



Micah Stevens wrote:
There's a function called 'found_rows()' function, so you could try issuing a query, then issuing a second one 'SELECT FOUND_ROWS();' and it should give the number of rows returned by the previous select.
Here's details:
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html



On Tuesday 08 November 2005 9:13 am, Micah Stevens wrote:

yeah, it would help if I read the whole post. Sorry.

On Tuesday 08 November 2005 9:06 am, Dwight Altman wrote:

I suppose you could use "count( PDOStatement::fetchAll() )", but I
understand your amazement.

mysql_num_rows() is specific to MySQL.  He wants a PDO version.

-----Original Message-----
From: Micah Stevens [mailto:micah@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, November 08, 2005 10:51 AM
To: php-db@xxxxxxxxxxxxx
Subject: Re:  [PDO] Number of rows found by Select



mysql_num_rows()

On Tuesday 08 November 2005 5:17 am, Rob C wrote:

What is the recommended way to find the number of rows found by a
SELECT query? PDOStatement::rowCount() doesn't work with MySQL and is a
bit of a hack anyway. Doing a COUNT(*) before the SELECT is very
hackish - the data could have changed and it's an extra query. What is
there that's better than either of these? Is there any way to use
COUNT(*) without risking data change, such as inside a transaction?

I'm amazed that there is no mysql_num_rows() equivilent, I get the
feeling that I'm missing something obvious. I can only presume there is
some technical limitation that I'm not appreciating, if anyone can shed
some light on this, I'd like to know.

I'm new to both PDO and this mailing list, so please be gentle with me.
I'm using PDO 1.0RC2, PHP 5.0.5 and MySQL 4.1.15.

Rob

--

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux