Page 1 of 1

Trying to get a record count from mysql through php

Posted: Thu Dec 06, 2007 12:31 am
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?

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

Posted: Thu Dec 06, 2007 12:53 am
by yanglei1979

Code: Select all

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

or this

Posted: Thu Dec 06, 2007 3:10 am
by the_power

Code: Select all

$sql = "SELECT COUNT(*) FROM pendants";
$count=mysql_result($sql, 0);
echo "They have: $count";

Posted: Thu Dec 06, 2007 4:05 am
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

Posted: Thu Dec 06, 2007 4:38 am
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(); 
?>

Posted: Fri Dec 07, 2007 12:14 am
by crystal ship
I think mysql_num_rows($result) will do good for your case.

Posted: Fri Dec 07, 2007 12:38 am
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.

Posted: Sat Dec 08, 2007 5:47 pm
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.

Posted: Sat Dec 08, 2007 6:53 pm
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.

Posted: Sat Dec 08, 2007 7:16 pm
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()

Posted: Sat Dec 08, 2007 9:49 pm
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];

Posted: Sat Dec 08, 2007 9:52 pm
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.

Posted: Sat Dec 08, 2007 10:14 pm
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.

Posted: Sat Dec 08, 2007 10:43 pm
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.