PHP Count

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ek5932
Forum Commoner
Posts: 25
Joined: Mon Jun 28, 2004 5:55 pm

PHP Count

Post 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?
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Re: PHP Count

Post 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.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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;
?>
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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.
Post Reply