Jump to content
Larry Ullman's Book Forums

Sql Count Function And Php


Recommended Posts

Hi everyone,

I'm getting the following error message:

 

"mysql_fetch_array() expects parameter 1 to be resource, object given"

 

with the following code:

 

$q = "SELECT COUNT(snippet_ref) FROM snippets WHERE (display = 'yes' AND price < 8)";

 

$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

 

$p8_displayy = mysql_result($r, 0);

 

if ($p8_displayy >= 1)

{

echo'

<p>How many:' . $p8_displayy . '</p>

';

}

else

{

echo'

<p>There are no snippets displayed in this price range</p>

';

}

 

mysqli_free_result($r);

 

I must admit to being a bit confused as to whether to use mysql_result, mysql_num_rows or a while loop with Mysqli_fetch_array. The one above seems to be the oist logical to me as a count function presumably doesn't return rows, just a number.

 

I've triple checked the table and column names. It should return '5'.

 

Cheers

Paul

Link to comment
Share on other sites

You don't use fetch_array() in the code above. Tricky to know what's wrong with your code then. This is how I would've done it.

 

$query = "SELECT COUNT(snippet_ref) AS snip_count FROM snippets WHERE display = 'yes' AND price < 8";

$result = mysqli_query($dbc, query);

if ( mysqli_num_rows($result) === 1 )
{
   $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

   echo '<p>How many: '. $row['snip_count'] .'</p>';
}
else {
 echo'<p>There are no snippets displayed in this price range</p>';
}

 

Another thing is that agregate function often requires a group by statement in the SQL. You might need that if weird results are returned.

  • Upvote 1
Link to comment
Share on other sites

Antonio,

Thanks very much it worked fine.

 

I just needed to amend it slightly and add an extra bit of code as follows:

 

if ($row['snip_count'] == 0)

{

echo'<p>There are no snippets displayed in this price range</p>';

}

else

{

echo '<p>How many: '. $row['snip_count'] .'</p>';

}

 

This is because the count could be zero. If this record is zero the 'else' part above wouldn't kick in, it would return 'How many' as 0.

 

I've also learned what '===' is, although when I get 2 minutes I need to look up the difference between '===' and '=='.

 

Cheers

Paul

Link to comment
Share on other sites

You'd want both in that case. You'll get an error if no rows are found. Other than that, glad you solved it. :)

 

Regarding "==" and "===", the difference is type checking. A String "0" is regarded as a boolean FALSE when you compare with "==". The reason is that PHP type jugles for you. Therefor, if type is important, you should use "===".

 

Some functions can return a result that equates to FALSE in some regards (for example String "false", String "0" or Integer 0. The same functions may return a boolean FALSE on error. To make sure it's not a false positive, you'd need to check type as well. Use "===" when a function may return a false positive.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...