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

wingzero

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.

paulnaj

I think you might just have been a bit unlucky in your choice of examples ...

1) SELECT FIELD, COUNT (DISTINCT FIELD2) AS FIELD3 ...
You can only put DISTINCT as part of the SELECT keyword

2) SELECT COUNT() ... etc
Not quite so straightforward with two or more tables. Count one of the fields of one of the tables rather than ().

This doesn't work ...
SELECT COUNT(*)
FROM table1, table2
WHERE table1.id = table2.id
GROUP BY table1.id

Whereas this does ...
SELECT COUNT(table1.id)
FROM table1, table2
WHERE table1.id = table2.id
GROUP BY table1.id

wingzero

I think I solved it differently.
I spent some time rebuilding the SQL query using multiple JOINs in the FROM clausle and then WHERE to filter out and GROUP BY to make COUNT(*) AS FIELDx work.
It was outputting the correct data inside MySQLCC Query Window, so I tried sending the result set to the function I previously assembled to dynamically generate tables using mysql_fetch_array as main function to read the result set from MySQL.
This time worked flawlessly and the function generated the tables without errors.

But I still wonder why it wasn't possible to concatenate the two arrays from the two queries, one for the needed outputting data and another for the related count() calculus on that same data.
The query with the count() AS FIELDx was generating the correct result set, and moving thru it out of the function let me access the array using the count(*) alias flawlessly. But when I was passing it to the function using a mysql_fetch_array($rs,MYSQL_ASSOC) it kept telling me that the query data wasn't of the right type for the mysql_fetch_array function....

Now my only problem is that I have to sort out how to deal with the fact that based on a condition when looking for an inputted string on the form tested against database data I either have to generate a longer table row including the count() field results or a one column-smaller table row with the missing field value printed as N/A (NotAvailable).
I think I have to deal with it inside the dynamic table function generator by adding more conditions to test that out and adding a blank fake cell in place of the missing count() result when needed, right ?

Regarding the array concatenation thing then I wonder how it could be achieved and why it wasn't working anyway.
Fortunately for my needed queries on that page there and thanks to how I managed to design my database I can relatively easily join tables and get out the needed data thru not so basic SQL queries but what if I wanted to simply concatenate two MySQL result sets that I had no way to output all on the same table row with the dynamic table function thru a single query ?