Question about fetchall

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

Hi Perl Monks,

I'm trying to get information that is stored in a DB along with the total number of rows.

My code is:

$sth->execute(user) or die $db->errstr;

$user_info = $sth->fetchall_arrayref();

foreach $info (@{client_info})
{
 $usr_info[0] = $info->[0];
 ... (to [6]) 
}

$num_rows = $sth->rows;

return (@usr_info, $num_rows);
[download]

I tried printing the contents of my array and the number of lines by doing:

print "Rows = ".$num_rows."\n";

foreach $info (@usr_info)
{
 print "Info = ".$info."\n";
}
/code>

<p>I got the following output:</p>

<code>
Row = 

Info = user A
... (to 6)
Info = 1
[download]

It appears i'm getting an extra value that doesn't exist in the database.

My question is: is this value the number of columns?

Please advice, thanks

Comment on Question about fetchall_arrayref Select or Download Code

Replies are listed 'Best First'.
Re: Question about fetchall_arrayref by GotToBTru (Prior) on Apr 28, 2014 at 15:03 UTC
The code as you have copied it is not complete nor would it compile. For instance, you fetch an array ref into `$user_info` and then iterate over `@{$client_info}`. It will be difficult to assist without knowing what you're actually doing.	[reply] [d/l] [select]
Re: Question about fetchall_arrayref by NetWallah (Canon) on Apr 28, 2014 at 15:13 UTC
Could you post actual code that produces the result ? The code above has several inconsistencies - that makes it difficult to distinguish between typos, partial paste errors, and intent. If the code is too large to paste, pare it down to the minimum necessary to reproduce the problem you are trying to solve. What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against? -Larry Wall, 1992	[reply]
Re: Question about fetchall_arrayref by Anonymous Monk on Apr 28, 2014 at 14:38 UTC
Can anyone also tell me why the num_rows is empty?	[reply]
Re: Question about fetchall_arrayref by locked_user sundialsvc4 (Abbot) on Apr 28, 2014 at 16:13 UTC
(1) If you need to know the number of rows that meet the criteria for a query, ask the SQL servers to execute `SELECT COUNT() AS numrows FROM ...` to get a one-row result containing the count. (2) Generally I think it is a much-preferred idea to retrieve rows one at a time, not to retrieve all rows. Also, to retrieve it as a hashref and to access the columns by their name.* Your program should not care how many (millions of?) rows there might be, and it certainly should be written in the most descriptive, easy-to-understand and easy-to-maintain way possible. Since this isn’t a code-writing service, I am not going to delve into the whys or the wherefores of your particular program.