[RESOLVED] COUNT() With WHERE

[D_tunisia]

Can anyone please tell me why this query won't work?

$url_count = mysql_query("SELECT COUNT(*) FROM unique_views WHERE page_id = $myrow[page_id]");
$urlrow = mysql_fetch_array($url_count);

I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

Thanks in advance for any help.

[Roger_Ramjet]

echo your query string and you'll see what is wrong with it.

[bradgrafelman]

1. Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].

2. Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:

   echo 'Your name is: ' . $row['name'] . '! Hello there.';

3. Looks like you're doing multiple SQL queries even though there's a relationship between the different queries. Can you show us the different queries? That way, we can show you how to combine them into one query for optimization.

[D_tunisia]

Thanks, here is my code:

<?php

// Get the total amount of records from the table

$result = mysql_query("SELECT COUNT(*) FROM unique_views");
$myrow = mysql_fetch_array($result);

echo "Total Page Views: " . $myrow[0] . "<br><br>";

// Get all individual page URLs recorded

$result = mysql_query("SELECT page_id FROM unique_views GROUP BY page_id");
$myrow = mysql_fetch_array($result);

while($myrow = mysql_fetch_array($result)) {

	echo $myrow[page_id] . ": ";

	// Now get the number of times that URL appears in the table

	$url_count = mysql_query("SELECT COUNT(*) AS page_id FROM unique_views WHERE page_id = $myrow[page_id]");
	$urlrow = mysql_fetch_array($url_count);

	echo $urlrow[0] . "<br>";
	}

?>

[Piranha]

bradgrafelman wrote:

1. Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].

2. Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:

   echo 'Your name is: ' . $row['name'] . '! Hello there.';

Sorry to say, but this is wrong. You can, according to the manual, use arrays in strings in two ways. The code below is copied from the manual.

// These examples are specific to using arrays inside of strings.
// When outside of a string, always quote your array string keys
// and do not use {braces} when outside of strings either.

// Let's show all errors
error_reporting(E_ALL);

$fruits = array('strawberry' => 'red', 'banana' => 'yellow');

// Works but note that this works differently outside string-quotes
echo "A banana is $fruits[banana].";

// Works
echo "A banana is {$fruits['banana']}.";

// Works but PHP looks for a constant named banana first
// as described below.
echo "A banana is {$fruits[banana]}.";

// Won't work, use braces.  This results in a parse error.
echo "A banana is $fruits['banana'].";

// Works
echo "A banana is " . $fruits['banana'] . ".";

[D_tunisia]

Thanks for the help guys, I worked it out myself using a single query!

$result = mysql_query("SELECT ref_page, COUNT(*) FROM unique_views GROUP BY ref_page");
$myrow = mysql_fetch_array($result);