Page 1 of 1

PHP Count

Posted: Mon Jun 28, 2004 5:55 pm
by ek5932

Code: Select all

include 'db.php';
$sql = "SELECT COUNT(*) AS numrows FROM px_topics WHERE parent_id = '1"; 
$queryResource = mysql_query($sql); 
$row = mysql_fetch_array($queryResource); 
echo $row&#1111;'numrows'] . " rows selected<br />";
I am trying to count all the rows where the parent_id is 1 and show how many rows containing 1 there are but the script above produces the error


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Documents and Settings\Moi.PAUL\Desktop\www\project\listings\New Folder\final\head.php on line 24


Any ideas?

Re: PHP Count

Posted: Mon Jun 28, 2004 6:12 pm
by qads

Code: Select all

<?php
include 'db.php';
$sql = "SELECT COUNT(*) AS numrows FROM `px_topics` WHERE `parent_id` = '1'"; 
$queryResource = mysql_query($sql)or die(mysql_error()); 
$row = mysql_fetch_array($queryResource); 
echo $row['numrows'] . " rows selected<br />";
?>
you were missing a single quote after 1, and i added the mysql_error function, it will tell you whats wrong with it if it still fails to run.

Posted: Mon Jun 28, 2004 6:13 pm
by tim
well you would want to use mysql_num_rows to count rows, you could use an array fetch but thats why mysql_num_rows was coded for :).

Code: Select all

<?php
$sql = mysql_query("SELECT parent_id FROM table_name where parent_id=1") or die mysql_error;

$count = mysql_num_rows($sql);

echo $count;
?>

Posted: Mon Jun 28, 2004 8:18 pm
by qads
either way is fine...but i bet someone else will come in here and say X way is better cos its faster....thats phpdn for ya :lol: :P

Posted: Mon Jun 28, 2004 8:22 pm
by tim
lol I cannot disagree with you on that qads

"I performed a test and mysql_num_rows was .000400060 seconds faster"

oh well, got to love the extra EXTRA knowledge you gain here

Posted: Mon Jun 28, 2004 8:47 pm
by redmonkey
Speed is not everything, and dependant on what you are trying to do and your app may determine what the best approach would be....

If you also want to use the information (e.g. you want to query the database to find all users called 'Adam', give a total then display. Then the best approach would be to query the database and use num_rows for the total (or alternatively, query the database, retrieve all results into an array and count the size of the array).

If you just need a count, but you also have many other queries running that may return large amounts of data then SELECT COUNT would be the way to go. Take for example you had a query that returned 3000 rows but you only need the count, if you issue a standard query then you have 3000 rows sitting in memory. If you issue a SELECT COUNT then you only have one row. As I very rarely see anyone issuing a free_result in the code they post on these boards you could quite easily run out of memory or bog your server down if you have a large scale app serving up multiple requests 24/7.

While speed may not be of any major concern for small scripts, it can be helpful to to identify and remove potential bottlenecks in any script/app. I have yet to meet a coder who would not implement the best performing solution even if the gains were relatively small.

Posted: Mon Jun 28, 2004 9:10 pm
by qads
we know redmonkey, i was just saying that its fine for ek5932's prob, cos he is limiting it with id, so it wont matter much unless there are other queries in the page.