Re: How to get back the value of COUNT using perl DBI?

[andy]

On Thu, 7 Dec 2000 00:32:56 -0500 (EST), you wrote:

>Its returning 1 as in success, the command was executed successfully.  Had
>there been a syntax error in your sql command, it wouldn't have done that,
>returned null or false or something, i forget exactly.
>What you probably want is
>$querycount = "SELECT count(*) FROM povench where no_processors=1";
>$sth = $dbh->prepare($querycount);
>$sth->execute;
>$rs = $sth->fetch;
>$sth->finish;
>
>Does that help?

Got it! Yes thanks!
The only thing was that $sth->fetch returns an array reference, so this
gets the actual value:
	$count = ${$sth->fetch}[0];

Code now working but hideously untidy!

Cheers,
Andy.

>On Thu, 7 Dec 2000 andy@stripped wrote:
>
>> I'm going mad...
>>
>> I've searched the MySQL docs, I've searched the DBI mailing list for over
>> an hour, and all I get back is SQL strings, which I understand all too
>> well but what I really need is code snippets of examples, and I can't find
>> any at all...
>>
>> I would expect that this would be enough:
>>   $querycount = "SELECT COUNT(*) FROM povbench where no_processors=1";
>>   $rv = $dbh->do( $querycount );
>>
>> It just returns "1", presumably indicating that it was successful, but how
>> can I find the actual magic number? I just couldn't find an example
>> anywhere.
>>
>> I don't know whether it returns a count value, or an array which has to be
>> fetched or whether $dbh-->do or $dbh->prepare with $dbh->execute is the
>> correct method.
>>
>> I even installed MySQL on my NT system and added Perl::DBI and got it
>> working within 2 minutes. Created an identical table to that on the linux
>> server and running identical perl code on NT as on Linux. I'm pleased with
>> myself that I did this much in such a short time, but it's taken me just
>> as long to get nowhere looking for help on how to get a damn result from
>> using COUNT!!!  Maybe I should lie down for a while...
>>
>> I run the povbench database on www.haveland.com/povbench and it's
>> currently grown to a flat file db of over 1500 records and under stress.
>> I'm converting it to MySQL on Linux as I thought it would be an easy
>> exercise and I had it working after 2 hours, generating a nice pretty
>> multicoloured table, but I just iterate through the tables and display
>> those records that are between the variable $from and ($from+20). Messy...
>>
>> You can see what I did on www.haveland.com/cgi-bin/show.cgi
>> Just needs to go backwards and not display one of the prev or next
>> controls if at the end or the beginning of the record set.
>>
>> I want to deliver paginated output, eg: Results [1-50], [51-100],  like
>> we're used to with the search engines, and would like some guidance and
>> code examples on the best method.
>>
>> I'll work on user submissions once I got the output working the way I want
>> it, and then finally get round to doing a much more demanding benchmark.
>>
>> I really don't want to have to read everything into an array and then
>> slice it. I've gathered that COUNT and LIMIT are there for that, but how
>> do I use them in practice in plain perlesque English?
>>
>> Once I've cracked this, my next project is to convert a complicated but
>> small relational Access DB with 40 or so tables in it to MySQL and use XML
>> cos I think it could be fun, and because I will have to in the near future
>> for a contract I'm working on...
>>
>> Any help gratefully appreciated!
>>
>> Cheers,
>> Andy.
>>
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Haveland-Robinson Associates                 Tel.  [phone]
>> Budapest 1015, Donáti u.17 fszt.1, Hungary   Web: www.haveland.com
>>
>>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Haveland-Robinson Associates                 Tel.  [phone]
Budapest 1015, Donáti u.17 fszt.1, Hungary   Web: www.haveland.com