Tuesday 28 August 2018

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

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.

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

?>

0 comments:

Post a Comment