Perl and MySQL: Get number of rows affected

Spidy has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Perl and MySQL: Get number of rows affected by perrin (Chancellor) on Jul 15, 2006 at 16:59 UTC
In addition to the obvious route of issuing another query with a COUNT(*), you can also use a MySQL-specific trick for this. If you run this SQL: `SELECT SQL_CALC_FOUND_ROWS value FROM table WHERE distinguishing_value + = ?` [download] then you can run this query afterward to get the count without actually accessing the tables again: `SELECT FOUND_ROWS()` [download] This is documented here.	[reply] [d/l] [select]
Re: Perl and MySQL: Get number of rows affected by reneeb (Chaplain) on Jul 15, 2006 at 07:49 UTC
If samy_rios answer doesn't do what you want you can use this one: `my $select = "SELECT value FROM table WHERE distinguishing_value = ?"; my $sth = $dbh->prepare($select) or die $dbh->errstr(); $sth->execute() or die $dbh->errstr(); print $sth->rows;` [download] see DBI	[reply] [d/l]
Re^2: Perl and MySQL: Get number of rows affected by gellyfish (Monsignor) on Jul 15, 2006 at 08:14 UTC
This may not work depending on the driver - the DBI manpage says: For "SELECT" statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the "rows" method or $DBI::rows with "SELECT" statements is not recommended. I can't find anything in the DBD::mysql documentation that would contradict this. /J\	[reply]
Re: Perl and MySQL: Get number of rows affected by Samy_rio (Vicar) on Jul 15, 2006 at 04:18 UTC
Hi Spidy, If I understood your question correctly, this may help you. `"SELECT COUNT(value) FROM table WHERE distinguishing_value = ?"` Regards, Velusamy R. eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@\|6%,53!-9@2~j';	[reply] [d/l] [select]
Re^2: Perl and MySQL: Get number of rows affected by Ieronim (Friar) on Jul 15, 2006 at 11:01 UTC
Wrong. It will return the number of not-NULL `value`'s in the rows retrieved, which is always less or equal to the number of rows actually fetched! The number of rows fetched is returned by `COUNT()`, i.e. `SELECT COUNT() FROM table WHERE distinguishing_value = ?` [download] See MySQL grouping fuctions manual for more details.	[reply] [d/l] [select]
Re^3: Perl and MySQL: Get number of rows affected by Samy_rio (Vicar) on Jul 15, 2006 at 12:07 UTC
In MySql documentation, I got the below query: `Query from documentation : SELECT COUNT(ip), AVG(down) from test;` [download] When I run the below query, i am getting the following output. `Query which I ran: mysql> select count(us_name) from table where us_name='samy'; +----------------+ \| count(us_name) \| +----------------+ \| 6 \| +----------------+ 1 row in set (0.00 sec)` [download] Regards, Velusamy R. eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@\|6%,53!-9@2~j';	[reply] [d/l] [select]
Re^4: Perl and MySQL: Get number of rows affected by Ieronim (Friar) on Jul 15, 2006 at 13:23 UTC
Re: Perl and MySQL: Get number of rows affected by prabu ayyappan (Initiate) on Jul 15, 2006 at 15:57 UTC
$query = $db->query($sql_query); $number = $query->affectedrows the variable $sql_query is a string, and contains your SQL syntax for your query. There is no semi-colon at the end of the query in this case though. You can directly insert the string if you like as well, instead of using $sql_query. The variable $query now contains the identifier for the query, and will be used from now on for accessing that query's data. $number contains the number of rows in the database affected by query.	[reply]
Re^2: Perl and MySQL: Get number of rows affected by perrin (Chancellor) on Jul 15, 2006 at 16:31 UTC
There is no affectedrows() method in DBI. If you were thinking of the rows() method, that doesn't work for SELECT statements.	[reply]


Perl: the Markov chain saw
	PerlMonks