counting rows

[gotornot]

Hi All

I have a DB with over 950,000 rows and i want to be able to count how many rows are in the table.

The only trouble is a mysql_num_rows statement takes ages i need something a lot quicker.

This is what im using crrently and it doesnt work. any help would be great.

            <?php
            $qx = "select count(*) FROM products";
            //$qnx = mysql_query( "SELECT SQL_CALC_FOUND_ROWS `id` From `products`" );
            //$qnx = mysql_query($qx);
            //$qx = "SELECT * FROM products";
            $qxr = mysql_query($qx);
            $qxn = mysql_num_rows($qxr);
            echo $qxn;

            ?>

[dalecosp]

1. Please don't shotgun your posts. One would have been sufficient, either in this "Newbies" area or in Database or Coding.

2. Read my .sig --- mysql() is outdated. Start using mysqli() instead, or PDO.

Now, your question.

You don't need mysql_num_rows() for this. When the query is "select count(*)" only the result is returned. Under the old mysql() system:

$qx = "select count(*) FROM products";
echo mysql_result(mysql_query($qx));

[Weedpacket]

You'd only get one row from that query because you would only get one result - to be precise, one result that contains the number of rows in the table in one of its columns (and it's easy to tell which column because there's only one of those as well).

If you have an ID column that serves as a primary key you might have a speed improvement if you SELECT COUNT(ID) ....

[gotornot]

Hi Sorry about the shotgun didnt realise i had done it.

The trouble is it didnt work:
Warning: Wrong parameter count for mysql_result() in /home/n/o/nowcodes/web/public_html/admin/index.php on line 133??
Sometimes i hate learning to code 🙂

[Derokorian]

try adding an alias of count so you can access it by this name. or just use offset 0 as there's only one column.

[Weedpacket]

Or try checking the manual entry for [man]mysql_result[/man] to see what's missing (take a note of the warning at the top of the page, too).

[dalecosp]

Weedpacket;11024033 wrote:

Or try checking the manual entry for [man]mysql_result[/man] to see what's missing (take a note of the warning at the top of the page, too).

A most excellent suggestion, which might help one avoid terribly minor bugs produced by the fact that a professional coder is writing so much code these days he doesn't have time to debug (or even look over, apparently:o ) his own posts. 😉