[SOLVED] Counting Records in MySQL

[Albatross]

Once again I've hit a roadblock...

 

I've been trying to write a page that checks the database and counts the number of records and if the number is less than 3000, it loads page X, but if the number is equal to 3000, it loads page Y.

 

I know this should be a simple if/else statement but it's the checking and counting that I cannot get to work.  Any help would be appreciated.

[pedrobcabral]

You have to check for the number by using either mysql_num_rows or mysql_affected_rows, depending on the action you are doing.

Something like:

<?php $query = mysql_query("SELECT something FROM table");  if (mysql_num_rows($query) < 3000) { something; } else { something; } ?> 

[Barand]

Assuming that where you say "database" you really mean "table"

<?php
$res = mysql_query("SELECT COUNT(*) FROM tablename");
$num_recs = mysql_result ($res, 0, 0);

$next_page = $num_recs < 3000 ? 'pageX.php' : 'pageY.php';

header ("location: $next_page");
exit;
?>

[Albatross]

Worked beautifully once I played with it a bit.  Thank you both!  (I tried them both n they both worked afer a jig!)

[Barand]

The problem with pedro's solution is that query has to return 3000 somethings before you can count them (what if the table contains 1,000,000 somethings?)

 

"SELECT COUNT(*) FROM tablename" will return the value instantly as mysql will just lookup the number of recs in the table without having to read any records.

[Albatross]

Probably good that I used yours then,

 

$res = mysql_query("SELECT COUNT(email) FROM $table_name");

 

is grabbing every email up to the 3000th if I understood your code correctly.  Thank you

[Barand]

That isn't what I posted.

 

If you use COUNT(*) it gives the row count.

 

If you use COUNT(email) it has to read the entire table to count non-null values in that column.

[Albatross]

With no email, no ID field is generated and that's half of what I needed anyway so if the code is skippinng blanks in it's total count, that would be in my favor....unless I needed to total records regardless of content.

 

(I think I understood what I just said...)

 

But thank you for the heads up.  I changed it back to the * and added the not null attribute to the field so it MUST be populated in order to process.