error with $array['COUNT(A.user_id)']

[expert_21]

Hi, here's my code:

while ($array = mysql_fetch_array($gallery_stats->results))
{
	$text = $array['name'] . .$array['COUNT(A.user_id)'] . '</br>' . "\n";
	fwrite($fp,$text);

The error returned is the parse error.

It's due to $array['COUNT(A.user_id)'], which came from the SELECT COUNT(A.user_id)'...etc query.

Any ideas? Thanks.

[laserlight]

hmm... it looks like you're confusing a database column name with a database function applied to a column.

Must have a look at the query itself, and possibly at more of the relevant PHP code to be able to say more though.

[expert_21]

Well, here goes:

$gallery_stats->query("SELECT DISTINCT A.user_id, B.name, COUNT( A.user_id )
						FROM a_images as A, b_members as B
						WHERE A.user_id=B.id
						AND image_date > $thismonth
						GROUP BY user_id
						ORDER BY `COUNT( A.user_id )`  DESC 
						LIMIT 0 , 10");

fwrite($fp, '<p>&nbsp;</p><H2 class=\"navigationtitle\"><img src=\"http://www.domain.gif/pointer.gif\" alt=\">\"> ' . $month . ' Top Authors </H2>');

while ($array = mysql_fetch_array($gallery_stats->results))
{
	$text = $array['name'] . '</br>' . "\n";
	fwrite($fp,$text);
	foreach ($array as $key => $value) {
   echo "Key: $key; Value: $value<br />\n";
   }
	}

fwrite($fp, "\" ?>");

[expert_21]

oh, I tried aliasing the COUNT query but it didn't work as well.

$gallery_stats->query("SELECT DISTINCT A.user_id, B.name, COUNT( A.user_id ) AS text
                            FROM a_images as A, b_members as B
                            WHERE A.user_id=B.id
                            AND image_date > $thismonth
                            GROUP BY user_id
                            ORDER BY `COUNT( A.user_id )`  DESC 
                            LIMIT 0 , 10"); 

[laserlight]

I'm not particularly fluent with SQL but what if you try:

$gallery_stats->query("SELECT DISTINCT A.user_id, B.name, COUNT( A.user_id ) AS `text`
	FROM a_images, b_members
	WHERE A.user_id=B.id
	AND image_date > '$thismonth'
	GROUP BY user_id
	ORDER BY `text` DESC
	LIMIT 0 , 10");

btw, be careful when using text as an alias since text is also a column type.

[expert_21]

it returns:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /nfs/home/...php on line 97

I'm doomed 🙁

[laserlight]

You're hardly doomed.

You just need to debug.

What is the error from the DB server?
Incidentally, what DBMS are you using?