how to count the number of items in a return value from database...

[spewn]

here's my code:

my $dbh = DBI->connect('dbi:mysql:gtc','','');
my $sql = "select prodID from products";
my $sth = $dbh->prepare($sql);
$sth->execute;

my $addVar=1;
$srchCount=0;

while (@row=$sth->fetchrow_array){

my $sql1 = "select name from products where prodID='@row'";
my $sth1 = $dbh->prepare($sql1);
$sth1->execute;
$str1 = $sth1->fetchrow_array;

my $sql2 = "select type from products where prodID='@row'";
my $sth2 = $dbh->prepare($sql2);
$sth2->execute;
$str2 = $sth2->fetchrow_array;

my $sql3 = "select price from products where prodID='@row'";
my $sth3 = $dbh->prepare($sql3);
$sth3->execute;
$str3 = $sth3->fetchrow_array;

print << "EOF";
 <tr valign=top>
 <td align=center valign=top>Name: $str1</td>
 <td align=center valign=top>Type: $str2</td>
 <td align=center valign=top>Price: $str3</td>
 </tr>
EOF

$addVar++;
$srchCount++;

}

pretty basic. the code gets all the prodID's from the table 'products'. then it prints the name, type and price of each prodID instance. no prob. but how do i count how many prodID's are returned from the query? as you see, i have a variable $srchCount that increments each time it runs through the code...but this only gives me an accurate value AFTER the code is run. i need to know ahead of time.

i tried adding an additional "while() {}" statement and re-arranged the location of $srchCount increment:

<---
my $addVar=1;
$srchCount=0;

while (@row=$sth->fetchrow_array){$srchCount++}

while (@row=$sth->fetchrow_array){

my $sql1 = "select name from products where prodID='@row'";
--->

but then my second while() loop wasn't even read, or wasn't read right because there was no output result to the browser. it's like the script didn't like two while() loops.

i'm looking for a solution here.

also, is there a simplified way to query my 3 fields (name, type and price), or is the way i have it correct/efficient? for instance, what if i have to get values from 10 fields? just have ten separate queries?

one last thing...is $sth->fetchrow_array the best to use if the result is only 1 value?

thank you.

- g

 

[rharsh]

First off, I haven't done much database interfacing with Perl, but, here's some thoughts that may help you out. One note first, I'm accustomed to using M$ SQL, so some of the queries may require a bit of tweaking.

You should be able to replace all your queries with just one query. Change

my $sql = "select prodID from products";

to

my $sql = "select prodID[blue], name, type, price[/blue] from products";

Then when you're iterating through the results (while (@row=$sth... blah)) the array @row will contain the prodID, name, type and price of each product.

The next thought is, if you really need to know the number of products prior to running through the results from your query, you can run another query first and use the SQL count function. The query might look similar to:

my $sql_qry = 'select count(prodID) from products';

Which will return the number of product ids in products.

 

[PaulTEG]

if @row is the array, isn't $#row the number of items in the array?

--Paul

 

[rharsh]

Yeah, but unless I'm really missing the point (entirely possible) spewn is looking for the number of records that are going to be returned, @row is only one record.

how do i count how many prodID's are returned from the query? as you see, i have a variable $srchCount that increments each time it runs through the code...but this only gives me an accurate value AFTER the code is run. i need to know ahead of time.

 

[Coderifous]

I concur with rharsh's advice. Here is some code you could probably use. Some additional advice I could offer is:

Avoid using "Select *" sql statements (which you've done a good job of so far) in the queries you have in your code. There are a couple reasons for this: It is easier to know what you were querying for later on (easier to maintain), your code won't break as easy if you change the order of the fields in your table, or add new ones later on in the database.

Read your result set into an array (instead of a hash), but access the elements without having to remember their index value. Do this by assigning their index values to constants (using: use constant FIELDNAME => INDEXVALUE) This makes the code easier to read, especially when you are using the elements where none of the surrounding code makes it cleaer what the value represented. Again, easier maintenance.

my $dbh = DBI->connect('dbi:mysql:gtc','','')
    or die $DBI->errstr();

#this one line gives you the count you wanted.
my $count = $dbh->selectrow_array('select count(*) from memo');

my $sth = $dbh->prepare('select prodID, name, type, price from products')
    or die $dbh->errstr();
$sth->execute
    or die $sth->errstr();

# named fields for prettier array-element access.
# Order matches the order of the fields in the SQL string
use constant PRODID => 0;
use constant NAME   => 1;
use constant TYPE   => 2;
use constant PRICE  => 3;

print "Here is a list of $count products:<br />\n";
print '<table>';
while (my @row = $sth->fetchrow_array()){
    print 
        "<tr valign=top>\n",
        '<td align=center valign=top>Name: ', $row[NAME], "</td>\n",
        '<td align=center valign=top>Type: ', $row[TYPE], " </td>\n",
        '<td align=center valign=top>Price: ', $row[PRICE], "</td>\n",
        "</tr>\n";
}
print '</table>';

--jim

 

[siberian]

You could also do a fetchall

         $ary_ref  = $sth->fetchall_arrayref( $slice, $max_rows );

         $hash_ref = $sth->fetchall_hashref( $key_field );

and then count keys to get result set size off the single query.


You could also just call

$sth->rows

To get the number of rows returned.

 

[MikeLacey]

$sth->rows

Careful doing that, if it's a large number of rows you might well wait a while

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[spewn]

perfect...i ask a question, i get great answers. i ended up going w/codiferous and his suggestion. it works perfectly, and cleans my code up nicely.

thank you again.

i do have another question:

lets say i have 20 items returned from the query, but i only want to have 10 on one page and 10 on another...even if there were 50 items, i would only want 10 on a page.

my ideas:

1. if all results are stored as a list in '@row' (i assume they follow a numerical reference system), can i run the query and on each page only ask for certain sections, something like:

***
#assume count=19
if ($count>10) {
if ($page eq 2) {show @row[10...$count]'}
else {show @row[0...9]'}
}
***

or

2. use javascript to hide or show different sections of the results depending on page number, using <div> tags.

i guess the javascript would work, but i would rather use server side functions.

any ideas?

- g

 

[Coderifous]

This is a really common thing: paginating result sets. You can see that there are already some modules created for helping out on this:

http://search.cpan.org/search?query=Paginator&mode=all

In the past though, I have just taken a DYI approach:
Using the two argument form of the LIMIT clause in an SQL statement, you can pass the burden on too the database, which is normally a good thing.

for example:

SELECT * FROM some_table;

might return:

this
that
other
foo
bar
baz
hibby
jibby
jabby

And so

SELECT * FROM some_table LIMIT 0,3

would give

this
that
other

And

SELECT * FROM some_table LIMIT 3,3

would yeild

foo
bar
baz

See how nice and easy that is?

So to make this into code, we have to add two variables:
$how_many_results_we_want_per_page; # which will be used literally, and
$what_page_we_are_on; # which will be used as a factor

On each request to the CGI script that runs the query and gets the results, we will pass the value of $what_page_we_are_on ( which will default to 0 ). The code will take care of the rest. I use the param() (of CGI.pm) function in this samplish code, but you can get the value however you like.

my $results_per_page = 10;
my $what_page_we_are_on = param('page')+0; # forces numerical context, tosses out possible invalid values
my $starting_point = $page * $results_per_page;

# get the number of rows in the whole set (w/o the LIMIT clause)
# and store that value in: $number_of_rows

my $sql = "SELECT foo FROM bar LIMIT $starting_point, $results_per_page";
#... run your query... display your results...

print "<a href='$ENV{SCRIPT_NAME}?page=", $what_page_we_are_on - 1, "'>Previous 10</a>" if $what_page_we_are_on > 0;
print "<a href='$ENV{SCRIPT_NAME}?page=", $what_page_we_are_on + 1, "'>Next 10</a>" if $number_of_rows > $what_page_we_are_on * $results_per_page;

Hope this helps,

--jim

 

[siberian]

I've never had trouble with sth->rows, even on multi-thousand row result sets. Mysql (and Oracle) I -believe -populates this as part of the result it returns, it doesn't re-execute the query. My assumptions are probably wrong, can you explain where the performance hit comes from?

Yea, just checked my code, my ad server for example deals out millions of hits a week and uses sth->rows without any performance impact (that I can see at least) on mysql. We have the same on Oracle and don't see problems either.

 

[MikeLacey]

I was thinking of the wrong thing siberian, I was remembering visual basic and oracle objects for OLE would you believe.... Ignore me.

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[Coderifous]

grep -v Mike

 

[MikeLacey]

[mike takes a small bow]