Trying to get a record count from mysql through php

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
Daron
Forum Newbie
Posts: 11
Joined: Tue Oct 09, 2007 4:50 pm

Trying to get a record count from mysql through php

Post by Daron »

All I'm trying to do here is get the number of records in a table. I'm trying to get it to return a number on the screen at this point, but it won't return anything.

Code: Select all

<?php function ItemCount()
{
//database connection
$sql = "SELECT COUNT(*) FROM pendants";
return mysql_query($sql);
}
echo ItemCount();
?>
What am I missing?
yanglei1979
Forum Commoner
Posts: 38
Joined: Sat Aug 25, 2007 10:21 pm

Re: Trying to get a record count from mysql through php

Post by yanglei1979 »

Code: Select all

<?php function ItemCount()
{
//database connection
$sql = "SELECT COUNT(*) FROM pendants";
$r=mysql_query($sql);
return $r[0];
}
echo ItemCount();
?>
the_power
Forum Newbie
Posts: 10
Joined: Tue Dec 04, 2007 2:34 pm

or this

Post by the_power »

Code: Select all

$sql = "SELECT COUNT(*) FROM pendants";
$count=mysql_result($sql, 0);
echo "They have: $count";
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

Use mysqli_field_count.
You should put a column with the data type integer that increments itself every time that new values are put in the table. It will help to normalize the database and if you had it with just run the command
php_daemon
Forum Newbie
Posts: 3
Joined: Thu Dec 06, 2007 4:35 am

Post by php_daemon »

Code: Select all

<?php function ItemCount() 
{ 
    //database connection 
    $sql = "SELECT COUNT(*) FROM pendants"; 
    $r = mysql_query($sql); 
    return mysql_result($r, 0); 
} 

echo ItemCount(); 
?>
User avatar
crystal ship
Forum Commoner
Posts: 36
Joined: Wed Aug 29, 2007 5:45 am

Post by crystal ship »

I think mysql_num_rows($result) will do good for your case.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

crystal ship wrote:I think mysql_num_rows($result) will do good for your case.
Not in this case. :P mysql_num_rows() will always return 1 in this case. It has one row, of one field, which would be the count (eg..34). as mentioned above, mysql_result($r, 0); is the way to go.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Daron
Forum Newbie
Posts: 11
Joined: Tue Oct 09, 2007 4:50 pm

Post by Daron »

Sorry I haven't responded. Thanks to everyone for the responses. I've been busy and trying to figure out why nothing's working. I would assume that my connection function is to blame, but it works for everything else, so I don't know. The error I keep getting is on the line for the mysql_result, but I've known the error to read one line and really be referring to another because the previous line or something else caused the error to happen in the first place. I will have a go at tweaking it several ways and posting more code later, but right now, I must do something else. Life calls.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You could do two things here. The first would be (and in my opinion, the best choice) to read the count into an alias in the query:

Code: Select all

<?php
$sql = "SELECT COUNT(*) AS `row_count` FROM pendants"; 
if (! $result = mysql_query($sql)) {
  die('Could not run the query: ' . mysql_error());
}

// Get the resulting query return
$res = mysql_fetch_array($result);

// Grab the row from the result set you want
$count = $res['row_count'];
?>
The other option, which is a lot less advisable, would be to select the entire table and use the mysql_num_rows() function on that result:

Code: Select all

<?php
$sql = "SELECT * FROM pendants"; 
if (! $result = mysql_query($sql)) {
  die('Could not run the query: ' . mysql_error());
}

// Count the result set count
$count = mysql_num_rows($result);
?>
While the second option is a little less code it is a lot more overhead and will cost you a lot more in resource, especially if the table gets very large in row count.

Of course, the MySQL COUNT() function was implemented for a reason, so it makes sense to use it.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Everah's spot on.

Typically if you are fetching all the data anyways and you'd like to know the count, then use mysql_num_rows(). If all your concerned about is fetching the count then use COUNT()
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Everah wrote:You could do two things here. The first would be (and in my opinion, the best choice) to read the count into an alias in the query
Or instead of using an alias, just take the first array element since you'll likely only be getting the COUNT().

Code: Select all

$result = mysql_query("SELECT COUNT(*) FROM`tableName`");
$data = mysql_fetch_row($result);
echo $data[0];
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You could do that. But that seems a little less intuitive to me. You could just as easily reference the array index 'COUNT(*)' as well.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Everah wrote:You could do that. But that seems a little less intuitive to me.
On it's own, I agree. But in my database abstraction classes, I have a fetchOne() method that's a shorthand to get the first value for COUNT(), MAX(), etc. queries.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That's funny, because I have a property that I set in my abstraction classes called 'count' (sometimes resultCount and recordCount for multiple result set layers). So I suppose in a way we are doing the same thing, just getting at it differently.
Post Reply