[PDO] Number of rows found by Select

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

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

RE: [PDO] Number of rows found by Select

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@raincross-tech.com]
Sent: Tuesday, November 08, 2005 10:51 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] [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

Re: [PDO] Number of rows found by Select

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@raincross-tech.com]
> Sent: Tuesday, November 08, 2005 10:51 AM
> To: php-db@lists.php.net
> Subject: Re: [PHP-DB] [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

Re: [PDO] Number of rows found by Select

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@raincross-tech.com]
> > Sent: Tuesday, November 08, 2005 10:51 AM
> > To: php-db@lists.php.net
> > Subject: Re: [PHP-DB] [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

Re: [PDO] Number of rows found by Select

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@raincross-tech.com]
>>>Sent: Tuesday, November 08, 2005 10:51 AM
>>>To: php-db@lists.php.net
>>>Subject: Re: [PHP-DB] [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

Re: [PDO] Number of rows found by Select

Ick.. this is why I don't use OOP DBA libraries..

Uhm.. without spending a bit of time learning more about PDO, I'd be hard
pressed to answer this one. Why are you getting issues with asynchronous
unbuffered queries? If they're unbuffered, how is the code execution
continuing before you get data returned?

Seems weird to me, but I'm probably misunderstanding the error message.

-Micah

On Wednesday 09 November 2005 9:21 am, Rob C wrote:
> 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@raincross-tech.com]
> >>>Sent: Tuesday, November 08, 2005 10:51 AM
> >>>To: php-db@lists.php.net
> >>>Subject: Re: [PHP-DB] [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

Re: [PDO] Number of rows found by Select

Cracked it! The buffer command
(setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE)) needs to be
sent to the PDO object, not PDO::Statement objects. The documentation is
wrong here, in a number of ways.

This is the function that (fingers crossed) runs a SELECT query and
returns the statement, while setting the variable passed as the second
parameter to the number of rows. If no second paramater is passed, of
course nothing is set and function avoids unnecessary DB calls.
found_rows() returns the total number of turns regardless of any LIMIT
clause in the SQL so if you use LIMIT, pass the same number as the last
parameter. Offset doesn't matter.


Example Call (notice chopped SQL, no "SELECT"):

$c = -1;
$s = DM::select( '* FROM foo WHERE bar < 40 LIMIT 4 OFFSET 2', $c, 4);
var_dump($s->fetchAll()); # Normal dump of found data
var_dump($c); # The number of rows found, at most 4.


Function:

public static function select($sql, &$count = NULL, $limit = NULL)
{
$db = self::handle(); # Get PDO

$sql = 'SELECT '.(!is_null($count)?'SQL_CALC_FOUND_ROWS ':'').$sql;

try {
$stmt = $db->prepare($sql);
$stmt->execute();

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

if (!is_null($limit) && $count > $limit) {
$count = $limit;
}
}
} catch (PDOException $e) {
# todo
}

return $stmt;
}

Rob



Rob C wrote:
> 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@raincross-tech.com]
>>>> Sent: Tuesday, November 08, 2005 10:51 AM
>>>> To: php-db@lists.php.net
>>>> Subject: Re: [PHP-DB] [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