Tech Is there a quicker / easier way to do this?

[Publisher Bucks]

<?php

$con=mysqli_connect "localhost","maindata","pass","user");
$result=mysqli_query("SELECT * FROM Recipe WHERE Category REGEXP 'diabetic'");
$data=mysqli_fetch_assoc($result);
echo $data['total'];

?>

I'm trying to display a recipe count at the top of a page based on how many 'diabetic' friendly recipes I have in my database, but for some reason can't get the code above working.

Is there an easier way to do this that I'm missing?

[Azamat]

SELECT COUNT(*) AS Total FROM Recipe WHERE Category REGEXP 'diabetic'" something like this, you are not counting in php or in mysql query of your result. Also double check your REGEXP value is correct. https://www.geeksforgeeks.org/mysql-...ssions-regexp/

echo $data['Total'];

[redwhiteandblue]

Do you have a column called "total" in your "Recipe" table? If not it won't work, and even if you do it's probably not doing what you think.

You probably want something more like "SELECT COUNT(1) AS total FROM Recipe WHERE Category REGEXP 'diabetic'"

[redwhiteandblue]

But, if you are going to be testing this "diabetic" property a lot, I would favour making it a separate column with a boolean type, so it is either true or false. Testing that would be much quicker than doing a string search on each row.

[sarettah]

Originally Posted by Azamat

SELECT COUNT(*) AS Total FROM Recipe WHERE Category REGEXP 'diabetic'" something like this, you are not counting in php or in mysql query of your result. Also double check your REGEXP value is correct. https://www.geeksforgeeks.org/mysql-...ssions-regexp/

echo $data['Total'];

Originally Posted by Publisher Bucks

I'm trying to display a recipe count at the top of a page based on how many 'diabetic' friendly recipes I have in my database, but for some reason can't get the code above working.

<?php

$con=mysqli_connect "localhost","maindata","pass","user");
$result=mysqli_query("SELECT * FROM Recipe WHERE Category REGEXP 'diabetic'");

$data=mysqli_fetch_assoc($result);
echo $data['total'];

?>

Is there an easier way to do this that I'm missing?

It depends on how you want to use the data.

If further down the page you are going to list the recipes then you would not want to pull the total, you would want to pull the full dataset at the top. To print the total you can echo the number of rows returned by the query.

<?php

$con=mysqli_connect("localhost","maindata","pass", "user");
$result=mysqli_query("SELECT * FROM Recipe WHERE Category REGEXP 'diabetic'");

echo mysqli_num_rows($result);

?>

Then further on you can use the results rather than running another query.

<?php
while($data=mysqli_fetch_assoc($result))
{
Do something here
}
?>

If you are not going to list the recipes later in the page then just pull the total as azamat and redwhiteand blue said above this.

.

[sarettah]

Also, if you have fixed categories then you should test for equals instead of a regex match. It will be quicker

....where category='diabetic'

And you should have an index on your category column if you do not already.

.

[Publisher Bucks]

Awesome, thank you all for the responses, will have a play with these suggestions shortly.

Much appreciated