Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Question Unanswered: MySQL COUNT(*) on Query never returns full array?

    Now, I have a database and I have to generate a table with PHP.
    I set up a function that uses mysql_fetch_array ( $variable, MYSQL_ASSOC) in a while statement and then more while loops to move thru the array and generate a table getting Table Headers and Table Rows contents out of the returned array.
    The function works flawlessly with a query that doesn't have any COUNT( ) statement.
    But with a COUNT(*) , with or without AS and even with a query like

    SELECT FIELD, COUNT (DISTINCT FIELD2) AS FIELD3
    FROM tables
    WHERE conditions
    GROUP BY FIELD

    I can't an array returned to PHP... although under MySQL when testing the query out I can clearly see that the returned table exists and contains the correct results but no while loops seems able to retrieve anything from the supposed array.. it seems that there's no array there.. why??

    When using just count(*) without any group by I can return the query result to a variable

    $query = mysql_query(" SELECT COUNT(*) .... " , $dblink);
    $count = mysql_result ($query,0);

    and that works as a single value BUT I'd like to output the whole database table as it appears under MySQL and sent it to the generic tables function maker just like I do with other queries that don't have any COUNT() in the SELECT.

    I can't use mysql_num_rows() because I needed to have it done from MySQL directly.

    What I wanted to do was to concatenate the arrays from the query with no COUNT() and the one with it into a new array and sent it to the function to generate a big table returning the COUNT value in the proper field.... BUT if I can't get an array out of the query with the COUNT statement, there's no way to concatenate them and in fact I'm getting parse errors that way.
    Trying to use mysql_fetch_array on the query with COUNT either generates no values and no table or errors for incorrect data type sent to the function.

    So, anyone knows how to solve this issue ?
    I really can't find any useful info online to sort it out.

    Please help.

  2. #2
    Join Date
    Sep 2003
    Location
    New Zealand
    Posts
    6
    I'm a bit lost with all of this but this will fix your count problem

    SELECT FIELD, field2, COUNT (*) AS FIELD3
    FROM tables
    WHERE conditions
    GROUP BY FIELD, field2

    this way if you have the following data

    smith, jones
    smith, peters
    brown, jones
    brown, peters
    brown, williams
    brown, williams
    smith, jones

    you will get back
    ===========
    smith, jones, 2
    smith, peters, 1
    brown, jones, 1
    brown, peters, 1
    brown, williams, 2

    You will get unusual results when you try to use a distinct and a group by in the same query.

Posting Permissions

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