CODEWITHSUNDEEP
phpmysql
PutraKg
PutraKg

Reputation: 2246

MYSQL returns empty result in PHP

I have the following tableCountry

country  clicks
-------  ------
0        222
66       34 
175      1000
45       650

And I use the following MYSQL statement to get the ranking of any of the country based on the clicks column (just one result)

SELECT COUNT(*) rank 
FROM countryTable a 
JOIN countryTable b 
   ON a.clicks <= b.clicks 
WHERE a.country = 45

The above will return '2'. Then in my php code I try to access the rank value with

$row =  mysql_fetch_array($result) or die(mysql_error()); 
echo $row['rank'];

But this doesn't return any result if the country is the number one. i.e a.country = 175

Upvotes: 0

Views: 1508

Answers (3)

Mr. Radical
Mr. Radical

Reputation: 1855

@PutraKg I think you can close this question, because I answered it in this post MYSQL does not return result in PHP when asked for the first ranking only ;-)

Upvotes: 0

Thanh Trung
Thanh Trung

Reputation: 3804

A join ON is a join between columns, not a comparison.

UPDATED

SELECT COUNT(*)+1 rank 
FROM countryTable
WHERE clicks > (SELECT clicks FROM countryTable WHERE country = 45)

Reasoning: searching for a rank mean searching for the number of records that has clicks > a given click.

Therefore, for 175 there is 0 country with better click => rank 1, country 45, 1 country with better click => rank 2

PHP

$result = mysql_query("....")
$row = mysql_fetch_array($result)
...

Normally it should work unless you got a problem with connecting to the server. That's where you should use your debugging skill. Do a var_dump($result) to see if it return false, if yes then it's a connection problem (check mysql_connect or something)

Upvotes: 1

Kermit
Kermit

Reputation: 34055

If you're looking for ranking, use this:

SELECT @rownum := @rownum + 1 AS num,
    t.country, t.clicks
FROM countryTable t, 
    (SELECT @rownum := 0) r
ORDER BY t.clicks DESC

Result

| NUM | COUNTRY | CLICKS |
--------------------------
|   1 |     175 |   1000 |
|   2 |      45 |    650 |
|   3 |       0 |    222 |
|   4 |      66 |     34 |

See it in action

Upvotes: 1

Related Questions