Counter Issue

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

Hi Monks,
This is a piece of my code that when it runs it finds only one result based on the date I am searching on it. But my question is why my counter is bringing out 2 instead of 1, when 1 is the times the entry was found in the database.
All I am trying to do is to count how many times a status showing 1 or 2 is inside of the table in my database.
I have many entries with status 1 and many with status 2, I am showing all the status 1 or 2 based on the date I need, I hope I explained it well.
Here is the code:

$count_sent=0;
$count_received=0;
$count_nviewed=0;

$sql = "SELECT * FROM table1 WHERE day = '$d' AND month = '$m_num' and
+ year = '$y' ";

$sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;

while ($pointer = $sth->fetchrow_hashref)
  {
      $count_sent++;
      if (my $status = $pointer->{'status'} eq "2") {
      $count_received++;}
      
      if (my $status = $pointer->{'status'} eq "1") {
      $count_nviewed++;}      
  }
#Running this it find one entry based on my date as 06/24/2004
print "My results: $count_nviewed<br>";
[download]

Thank you

Comment on Counter Issue Download Code

Replies are listed 'Best First'.
Re: Counter Issue by iburrell (Chaplain) on Jun 25, 2004 at 16:44 UTC
What are you trying to accomplish? It looks like you are trying to fetch multiple records for a given date, and count the total, how many have status 1, and how many have status 2. You could let the database do the work: `SELECT status, COUNT(*) FROM table WHERE day = ? AND month = ? AND year = ? GROUP BY status` [download] This SQL will return rows with the status, and the count of rows with that status.	[reply] [d/l]
Re: Counter Issue by pbeckingham (Parson) on Jun 25, 2004 at 15:33 UTC
Could you use a variation of the following (perhaps a syntactically correct version would be better than mine) to let the DBMS itself do the counting? It would be far more efficient, as it only has to return a single result to you, rather than a set of results that you then iterate over. You could modify it to count the various items you want. `SELECT count(*) FORM table1 WHERE day='$d' AND month='$m_num' AND year='$y'` [download]	[reply] [d/l]
Re^2: Counter Issue by Anonymous Monk on Jun 25, 2004 at 15:47 UTC
Because the way my program is writen I would like to keep the format I already have, but I tried that and it still didn't work	[reply]
Re: Counter Issue by Mercio (Scribe) on Jun 25, 2004 at 16:21 UTC
It would be easier to incorporate everything into the SQL database, but if that's not what you want then try this. First off, you might want to use strict and declare your variables. I doubt that would fix your problem, but it might help. I don't know what you are wanting `$status` to equal, but in the way perl runs status will either equal `1` or `null` if I remember right. If you are wanting `$status` to equal `$pointer->{'status'}` then you need to fix your if statement to be: `if ((my $status = $pointer->{'status'}) eq '1') {` [download] Also, I am assuming that status in your sql database is a `int` so you should change the `eq '1'` and `eq '2'` to `== 1` and `== 2`. I don't know if that's the exact problem, but It might fix it. `if ((my $status = $pointer->{'status'}) == 1) { #yada yada } if ((my $status = $pointer->{'status'}) == 2) { #yada yada yada }` [download]	[reply] [d/l] [select]