It works for me, if I add username and password to your connect statement.

You say that your script produces no value. Does it mean that the $total variable is empty? It is not.

It looks like what you posted is not the actual code you're using. Therefore, a few questions are necessary:

• Are you using strict and warnings?
• Have you tried DBI trace?
• Have you tried that query outside your script? (in MySQL standard client, for example)
• Have you read Reading from a database, Tricks with DBI and Before asking a database related question ...?

Furthermore, the $sth->rows method doesn't return the number of rows in a table, but the number of rows affected by your statement, when your SQL contains modifying commands such as UPDATE, INSERT, DELETE.

That method returns sometimes the number of records from a SELECT statement, but it is driver dependant, and not reliable, as you can see from the DBI docs. Using MySQL, it should return the number of rows if you execute a SELECT that fetches all the rows in your table, but a simple COUNT is less expensive.

To count the number of rows, then, you need to use COUNT(*), as you did.

Please read the above Tutorials, and then provide more information about your case.

Update
The error is NOT in the "AS" keyword as stated by ronzomckelvey. Such keyword is perfectly legal and functional in MySQL. (Updated again - OK, I see that both Zaxo and dws have pointed out that in detail.)

 _  _ _  _  
(_|| | |(_|><
 _|
[download]

  [dws@labrat test]$ perl total.pl
  2[dws@labrat test]$
  ^
[download]

The link you provided indicates a problem with count(*) when doing an INNER JOIN, which your script doesn't.

You can't use the rows method on select statements, only on statements that modify the database.

OK, now I'm really confused. I do that all the time! I do a select, and use rows to check if it returned anything. Don't I? (Goes to check...) yes I do. I have code like this:

## prepare some SELECT statement
$sth->execute() || die "Error: " . $dbh->errstr;
if( $sth->rows > 0 ){
 ## do some stuff
}
[download]

---




($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

OK, now I'm really confused. I do that all the time!

It might work on whatever database you're using, but getting a row count back from a SELECT isn't portable.

You can't use the rows method on select statements

Where on earth do you see the rows() method used? I don't. My browser can't find it.

I have no idea why it doesn't work for the OP. It seems to be working for me.

Mea culpa: I don't know where I saw the rows method used either, but obviously that was some good **** I was smoking yesterday. Please ignore that post, and I'll go find more to share. :I

OK I'm really confused indeed right now.

I've got working code which goes like this:

 $sth = $dbh->prepare( "
 SELECT  
   ## long complicated WHERE stuff
  " ) || die "Error: " . $dbh->errstr;

  $sth->execute() || die "Error: " . $dbh->errstr;    
  if( $sth->rows > 0 ){
     ## print out some results
  }
[download]

And according to this thread, I shouldn't do that, and I shouldn't rely on the results?

What should I do instead? Should I do the whole thing twice, like this?

 $sth = $dbh->prepare( "
 SELECT COUNT(*) As Total
   ## long complicated WHERE stuff
  " ) || die "Error: " . $dbh->errstr;

  $sth->execute() || die "Error: " . $dbh->errstr;    
  if( $Total > 0 ){
     ## only if it passes this condition, do the REAL
     ## select and print out the results
  }
[download]

That seems bizarrely innefficient.

What should I do instead? Should I do the whole thing twice, like this? ... That seems bizarrely innefficient.

I was deep into this issue several years back, and I wish I still had my old notes so that I tell you which of the then-major RDMBSs didn't provide a count at SELECT time. It had to do with how that particular RDMBS used lazy evaluation to produce its result set. (Lazy eval meant that no count was avaible until the result set was exhausted. I doubt that's what you want.) This might not be an issue with the current generation of significant databases. Perhaps others who work with the major vendors can chime in and let us know.

If you've got something working with MySQL, a strategy of sticking with what you've got, but noting that there might be an issue should you port, may be good enough.

Thanks for that. So DBI tells you not to rely on a rows count in general terms, but MySQL is a specific instance when you can? I'm happy with that. Mine's not the kind of code you port.

---




($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

If you want to do some function on your database, but only if there is something there to do it on, you could do the "two step" you suggested (COUNT (*) is very fast and fuly optimized in mySQL, so it does not "hurt" your database much), BUT your queries are not atomic and it could be that between your first step and the second step someone changed (deleted or added) the number of records so the result of your first test becomes invalid.

Therefore, only do the second step and wrap the fetch-function in a while-loop. If you get no results out of your SELECT, the code inside the while-loop will not get executed and no harm is done.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law