Need help creating a query

[wee493]

I have a table similar the the one below.

Countryurl

United States125

Canada300

Canada125

United States125

 

Currently I use the query below to get all the distinct countries.

SELECT DISTINCT country FROM log WHERE url= '$url'

 

But, I try to count the number of rows WHERE the url = 125. It only does it for the first row.

 

This is the whole script

$sql = mysql_query("SELECT DISTINCT country FROM log WHERE url = '$url'");
while ($r = mysql_fetch_array($sql)){
	$country = $r['country'];
		//$sql = mysql_query("SELECT COUNT(country) FROM log WHERE url = '$url' AND country = '$country'");
		$sql = mysql_query("SELECT * FROM log WHERE url = '$url' AND country = '$country'");
	//$visits = mysql_result($sql, 0);
	echo $country;
	echo " - $visits Visits";
	echo "<br>";
}

 

Any help would be amazing!

[fenway]

I don't follow... what are you trying to do?

[mellis95]

If you are talking about this commented line:

 

//$sql = mysql_query("SELECT COUNT(country) FROM log WHERE url = '$url' AND country = '$country'");

 

It looks like you just need to remove the $country variable from the WHERE clause. Right now, it is only counting the countries where the country name is $country and the URL is $url.

 

For instance, if $country="Canada" and $url="125", it would only give you a count of 1 because there is only one instance where the WHERE clause is true. (Based on the info in your post.)

 

If you remove the $country variable from the WHERE clause, you should see a count of '3' if $url="125".