MYSQL does not return result in PHP when asked for the first ranking only

Question

I am trying to get hold of 1 record from a MySQL table using PHP. I have tried many different SELECT statements, while they all work in MYSQL they refuse to return any result in php.

The countriesRanking table is a simple two column table

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

The mysql returns the ranking of the country column (1, 2, 3, etc..) and it returned all results EXCEPT the first ranked country. Eg when country=175, should return 1 but no result returned. Direct query via web browser return blank page, no error message. My PHP code

$result = mysql_query("SELECT FIND_IN_SET(clicks,
(SELECT GROUP_CONCAT(DISTINCT clicks  ORDER BY clicks DESC) 
FROM countriesRanking)) rank FROM countriesRanking
WHERE country = '$country'") or die(mysql_error());

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

EDIT

I tried the following but get the same blank page

var_dump($row['rank']);

EDIT 2

For a successful query print_r($result) returned something like Resource id #4. While print_r($row) returned Array ( [0] => 4 [rank] => 4 ). But when querying for the top ranking country. eg country=175, it returned a blank page.

Answer 1

Assuming you are not getting an error that means you are successfully connecting to your database. You are just not getting back any values. Try:

$row = mysql_fetch_row($result);

I would usually think $row = mysql_fetch_assoc($result); would work but if neither of these work it must be something within your mysql_query.

Answer 2

You can debug this as below.

1. make sure your SQL is correct by running it on PHPMyAdmin and check the result.
2. make sure you don't have any fatal errors, if you get a blank page there is something wrong. Turn on PHP errors and see.
3. print_r($result) and see whether it's empty.
4. if you want first ranking only then add the LIMIT 1 to your query

Answer 3

If you want only one record from database than you have to use limit in query

$result = mysql_query("SELECT FIND_IN_SET(clicks,
(SELECT GROUP_CONCAT(DISTINCT clicks  ORDER BY clicks DESC) 
FROM countriesRanking)) rank FROM countriesRanking
WHERE country = '$country' LIMIT 1") or die(mysql_error());

and after that use while loop

while($row =  mysql_fetch_assoc($result)) {
    $theranking = $row['rank'];
}
echo $theranking;

Answer 4

Give this a try. It is based on your earlier question. MYSQL returns empty result in PHP
MYSQLI version:

<?PHP
function rank(){
/* connect to database */
$hostname = 'server';
$user = 'username';
$password = 'password';
$database = 'database';

    $link = mysqli_connect($hostname,$user,$password,$database);

    /* check connection */

    if (!$link){ 
        echo ('Unable to connect to the database');
    }

    else{
    $query = "SELECT COUNT(*) rank FROM countryTable a JOIN countryTable b ON a.clicks <= b.clicks WHERE a.country = 175";
    $result = mysqli_query($link,$query);
    $arr_result =  mysqli_fetch_array($result,MYSQLI_BOTH); 
        return $arr_result['rank'];
    }
    mysqli_close($link);
}

echo rank();

?>

MYSQL version:

<?PHP
function rank(){
  /* connect to database */
  $hostname = 'server';
  $user = 'username';
  $password = 'password';
  $database = 'database';

  $link = mysql_connect($hostname,$user,$password);

  /* check connection */

  if (!$link){ 
    echo ('Unable to connect to the database');
  }

  else{
  $query = "SELECT COUNT(*) rank FROM countryTable a JOIN countryTable b ON a.clicks <= b.clicks WHERE a.country = 66";
  mysql_select_db($database);
  $result = mysql_query($query);
  $arr_result = mysql_fetch_array($result,MYSQL_BOTH); 
  return $arr_result['rank'];
  }
  mysql_close($link);
}

echo rank();

?>