PHP Warning: mysql_result() expects parameter 1 to be resource, boolean given in public_html/province.php on line 11

[FUNKAM35]

<?php
$select="SELECT distinct province FROM property WHERE country = '$country' order by province asc "; 
$results = mysql_query("$select", $link_id);
if(mysql_num_rows($results)>0){
 while ($query_data = mysql_fetch_row($results))
 {
$theprovince=$query_data[0];
if(trim($theprovince==""))continue;
$theprovince=stripslashes($theprovince);
$total_results2 = mysql_result(mysql_query("SELECT COUNT(*) as Num  FROM property  WHERE province = '$theprovince' "),0) ;
echo "<li><a href=\"houses.php?province=$theprovince&country=$country\" title='$theprovince $country'>$theprovince ($total_results2)</a></li>\n";
}
}
?>

PHP Warning:  mysql_result() expects parameter 1 to be resource, boolean given in public_html/province.php on line 11

Hi, can anyone please advise as I keep getting this error message on this code and its driving me mad. Many thanks

[.josh]

a) It means your mysql_query failed, so it's returning false instead of a result resource.

 

b) The mysql functions are deprecated. You should update your code to use mysqli or PDO functions.

[Psycho]

Your query is failing. And since you decided to group all the functions together you have no way to check for errors - not that you have added any error handling at all. But,the bigger issue is that you should NOT be running queries in loops - especially for what you have. You only need ONE query with a GROUP BY clause.

 

.josh is right about mysql_ functions being deprecated and you should change them, but here is a solution with what you currently have - which only uses a single query

 

 

<?php
$query = "SELECT province, COUNT(province) as total
          FROM property
          WHERE country = '{$country}'
          GROUP BY province
          ORDER BY province ASC";
$result = mysql_query($query, $link_id);
if(!$result)
{
    die("Query: {$query}<br>Error: " . mysql_error());
}

while ($row = mysql_fetch_assoc($result))
{
    //Assign variables
    $province = $row['province'];
    $total = $row['total'];
    $title = "{$province} {$country}";

    //Create output
    echo "<li><a href=\"houses.php?province={$province}&country={$country}\" title='{$title}'>{$province} ({$total})</a></li>\n";
}
?>

[FUNKAM35]

brilliant Psycho, thanks it cured the error. I have only used MySQL functions before.