...

View Full Version : count function doubling



dragon
06-08-2005, 03:54 AM
I'm not sure why this is working this way, but for some reason, when I use the PHP count function on an array that just came from, the number is doubling.

Here's the code where the problem is apparent:


$query = 'SELECT ID,name FROM users';
$result = mysql_query($query);
$user = mysql_fetch_array($result);
$c = count($user);
echo($c);

At this point, $c should be 2, but for some reason, it's showing 4!

This is causing problems when I'm trying to use a for loop with $c as the limit. I'm running into 'Notice: Undefined offset' when I use those.

I hope someone here knows what is causing this. Am I missing something or is my computer screwed up?

gsoft
06-08-2005, 03:57 AM
Maybe print_r your $user to check out why, also try using sizeof (http://php.net/sizeof)

SeeIT Solutions
06-08-2005, 04:18 AM
I believe it is because you have 2 rows and 2 colums.

you could divide the result of count by the number of columns you have selected from the database.

it is counting all values in the array not rows.

The best solution however would be to use


$query = 'SELECT ID,name FROM users';
$result = mysql_query($query);
$c = mysql_num_rows($result);
echo $c;

dragon
06-08-2005, 04:19 AM
Ok. sizeof() gave the same as count(). print_r gives me this:

Array ( [0] => 1 [ID] => 1 [1] => Frank [name] => Frank )

I understand a little more... but is that normal to get the array with both numbers and names?

By the way, mysql_num_rows() gives 4 also.

Kid Charming
06-08-2005, 04:32 AM
That's correct: mysql_fetch_array() retrieves results as both an associative and numerical array. If you only want an array of one type or the other, use mysql_fetch_row() for numeric indices or mysql_fetch_assoc() for associative. There's also another parameter you can pass to mysql_fetch_array() that'll limit it to one or the other, but I can't remember the specific syntax off the top of my head (I never use it).

gsoft
06-08-2005, 05:11 AM
From the PHP Manual


The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH.

E.g.



$user = mysql_fetch_array($result, MYSQL_ASSOC);

dragon
06-09-2005, 02:09 AM
Thanks guys! It all makes sense now. :)

ClubCosmic
06-09-2005, 03:20 AM
For my own curiosity will this work?

$query = 'SELECT count(*), ID,name FROM users';

tnx for any insight.
c.c.

Fou-Lu
06-09-2005, 04:51 AM
Assuming that ID linked with the name:


$query = "SELECT COUNT(ID) AS count, ID, name FROM users";

Now your result will contain a count element for it. * should be fine as well, but the mysql gurus will probably tell you about a load time differential on that ;)

Kid Charming
06-09-2005, 05:11 AM
For my own curiosity will this work?

$query = 'SELECT count(*), ID,name FROM users';


Not quite -- you can't mix aggregate functions with non-aggregate functions without having a GROUP BY clause. So you need something like



SELECT
count(*) as mycount
,ID
,name
FROM
table
GROUP BY
ID


This comes with another gotcha, though. The above will give you an error in any SQL except MySQL, because we're selecting two non-aggregate fields (ID and name), but only grouping on one. But just because you're not getting an error in MySQL doesn't mean it's a good idea. For this query, there shouldn't be any problems, since ID'll be a unique value -- there will only be one name per ID, and the grouping is only a formality (this also means, of course, that your COUNT() value will always be 1 ;) ). If your fields aren't unique, though, you can get some pretty skewed results. So the standard-friendly query is



SELECT
COUNT(*) AS mycount
,ID
,name
FROM
table
GROUP BY
ID,
name


As for efficiency and COUNT(), for something like this, COUNT(1) is best, since you don't have to pull anything out of the db itself.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum