Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder dragon's Avatar
    Join Date
    May 2003
    Location
    Florida
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count function doubling

    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?

  • #2
    Regular Coder
    Join Date
    Apr 2004
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe print_r your $user to check out why, also try using sizeof

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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

  • #4
    New Coder dragon's Avatar
    Join Date
    May 2003
    Location
    Florida
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by dragon; 06-08-2005 at 03:27 AM.

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

  • #6
    Regular Coder
    Join Date
    Apr 2004
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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($resultMYSQL_ASSOC); 

  • #7
    New Coder dragon's Avatar
    Join Date
    May 2003
    Location
    Florida
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys! It all makes sense now.

  • #8
    Regular Coder
    Join Date
    May 2004
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For my own curiosity will this work?

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

    tnx for any insight.
    c.c.

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Assuming that ID linked with the name:
    Code:
    $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

  • #10
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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

    Code:
    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

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •