[RESOLVED] (Very) Simple SQL Select Problem

[barkley1979]

Hi

I want to pull each DISTINCT field called 'url' out from a table called 'click' with a count of the number of occurences of each field and output in the format of:
www.google.com 4
www.yahoo.com 1
www.hotmail.com 2
etc...
So far, after much searching I have:

$sql = "SELECT URL FROM click WHERE log_date='2004-11-28'";

if ($result=mysql_query($sql)) {
if ($row=mysql_fetch_row($result)) {
echo "Total Count is ".$row[0]."<br>\n";
} else {
echo "<!-- SQL Error ".mysql_error()." -->";
}
}

This obviously just returns the first entry in the table.
I have tried manipulating it and have come up with the following:

$sql = "SELECT URL, COUNT(*) FROM click WHERE log_date='2004-11-28'";

while ($result=mysql_query($sql)) {
if ($row=mysql_fetch_row($result)) {
echo "Total Count is ".$row[0]."<br>\n";
}
}

But this doesn't seem to work and I can't understand what I am doing, let alone what I am doing wrong...!

Any help/guidance would be appreciated.

Edit: Please ignore my "Total Count is" - this was just testing!

[nemonoman]

You appear to be trying to want to group the records by URL, and then show the count.

Fortunately this is easy to do:

SELECT URL, COUNT(*) FROM click WHERE log_date='2004-11-28'
GROUP BY URL