MYSQL returns empty result in PHP

Question

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

Have you tried `mysql_fetch_assoc`? Also please use mysqli or PDO extensions. — user1190992, Jan 12 '13 at 16:55
@user1190992 I have and it doesn't return any result also. How do I use mysqli? — PutraKg, Jan 12 '13 at 16:59
It doesn't have anything to do with fetch_assoc or mysqli or pdo. It's the SQL itself. — Thanh Trung, Jan 12 '13 at 17:05
@ThanhTrung the Answer you gave below does not work unfortunately — PutraKg, Jan 12 '13 at 17:25
@PutraKg I know, but in term of phrase, are you searching for all country that has clicks smaller than a given country? — Thanh Trung, Jan 12 '13 at 17:34
I would think so. But those countries with the same number of clicks should have similar ranking. BTW, the mysql statement works as I have tested it here http://sqlfiddle.com/#!2/4fe22/2 . It just the PHP script that does not return any value when it's the number one country — PutraKg, Jan 12 '13 at 17:37

Thanh Trung · Answer 1 · 2013-01-12T18:07:14.007

1

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)

edited Jan 12 '13 at 18:07

answered Jan 12 '13 at 17:08

Thanh Trung

3,566
3
31
42

Thanks. Just tried this but it didn't return the correct ranking. Getting the first ranking still returns the blank result also – PutraKg Jan 12 '13 at 17:16
Error: Unknown column 'a.country' in 'where clause'. BTW I have tried several mysql statement which are all correctly returning values in MYSQL. But in PHP all returns nothing for the number one country. – PutraKg Jan 12 '13 at 17:45
It's fixed, try this new sql – Thanh Trung Jan 12 '13 at 17:52
The new sql is correct. But how do I get $row['rank'] = 1 for the the number one country? I still get a blank result in php (not mysql). – PutraKg Jan 12 '13 at 17:57
That's what I am using and strangely it doesn't return any value only when the country is number one in the ranking. It's not a connection problem for sure because the problem is specific to that condition. I am not a PHP or MYSQL programmer so I can't go far with my own debugging :( – PutraKg Jan 12 '13 at 18:13
I tested var_dump($result) but it doesn't return any value when it's the number one ranking country. How can this be a connection problem when other queries is returning the correct result? – PutraKg Jan 12 '13 at 18:37
Then again my friend, I have no idea. $result should always contain a resource of "false". If it doesn't show up any anything, it means that your code is broken somewhere, maybe above it. Like mysql_query is not executing – Thanh Trung Jan 12 '13 at 18:42

score 1 · Answer 2 · answered Jan 12 '13 at 17:45

1

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

answered Jan 12 '13 at 17:45

Kermit

33,827
13
85
121

Thanks. In my case though I just want the query to return one result. The problem I have is accessing the return value in PHP when the country ranked 1. – PutraKg Jan 12 '13 at 17:49
Interesting, this way you can have a list of rank when you need it. You can simply get a record with WHERE country=id_country – Thanh Trung Jan 12 '13 at 17:55

score 0 · Answer 3 · edited May 23 '17 at 11:48

0

@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 ;-)

edited May 23 '17 at 11:48

Community

1
1

answered Jan 13 '13 at 17:54

Mr. Radical

1,847
1
19
29

MYSQL returns empty result in PHP

3 Answers3

See it in action

Linked