count function doubling

[dragon]

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:

PHP Code:

$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]

Maybe print_r your $user to check out why, also try using sizeof

[SeeIT Solutions]

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

PHP Code:

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


[dragon]

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]

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]

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.

PHP Code:

$user = mysql_fetch_array($result, MYSQL_ASSOC); 


[dragon]

Thanks guys! It all makes sense now.

[ClubCosmic]

For my own curiosity will this work?

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

tnx for any insight.
c.c.

[Fou-Lu]

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]

Originally Posted by ClubCosmic

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.