Querying SQL database using values from PHP checkboxes

Hi,

I'm building a simple recipe search system using PHP and an SQL database. There are various search crieria used to match recipes, but the one I'm having difficulty with is the "skill level".

Each recipe is given a skill level; either beginner (B), intermediate (I) or advanced (A). On my search form the user can select either one or all of these. Obviously it would a an easy select statement if the user could only select one option:

SELECT FROM `recipes` WHERE `skill_level` = $skill_level

But as the user can select either one, two or all the levels I'm struggling to work out the logic for the SELECT query.

Any help would be greatly appreciated.

Thanks.

 

When all skill levels are being requested, simply do not include a WHERE clause for `skill_level`.
Say you wanted to only get beginner (B) and intermediate (I); assume all table entries have a skill level as one of the three specified.
To eliminate one simply use a not equal operator, for example to get B and I:

SELECT FROM `recipes` WHERE `skill_level` != 'A'

This will give you all entries at beginner and intermediate skill levels.

 

$skill_B =  $_GET["skill_B"]; // if B is checked, value is 1, else is 0
$skill_I =  $_GET["skill_I"]; // if I is checked, value is 1, else is 0
$skill_A =  $_GET["skill_A"]; // if A is checked, value is 1, else is 0

if ($skill_B==1) $sql_B = "AND skill_level = 'B' ";
if ($skill_I==1) $sql_I = "AND skill_level = 'I' ";
if ($skill_A==1) $sql_A = "AND skill_level = 'A' ";

$sql = "SELECT FROM recipes WHERE *** $sql_B $sql_I $sql_A"; // replace *** with other conditions you need for search

PHP:

 

Not really recommended - the above code will throw warnings depending on the error-display settings and php version.
It will also not work if you don't have other criteria for the WHERE-clause.
I'd recommend something like this:

$skill = array(); //sets the skills as an array

$skill[] = (isset($_GET['skill_B']) && $_GET['skill_B'] == 1) ? 'skill_level = B ' : '';
$skill[] = (isset($_GET['skill_A']) && $_GET['skill_A'] == 1) ? 'skill_level = A ' : '';
$skill[] = (isset($_GET['skill_I']) && $_GET['skill_I'] == 1) ? 'skill_level = I ' : '';

$temparray = array_filter($skill); //filters the array for empty values

if (!empty($temparray)) {
   $selection = 'WHERE '.join('AND ',$temparray);   // assigns a WHERE-clause with the non-empty values in the $temparray
} else {
   $selection = ''; // this selects all recipes if no criteria is set
}

$sql = "SELECT FROM recipes $selection"; // selects from the database based on which criteria is set - some, all or none

PHP:

 

$sql="SELECT FROM recipes WHERE 1=1 $sql_B $sql_I $sql_A";

 

Why in the world would you introduce an empty check-value when you can do it correctly?

 

First of all, I don't think there are not any other criteria to search, other than skill level.
Anyway, I thing that a search using a "empty check value" is more faster than a join, especially for large databases.
http://www.mysqlperformanceblog.com/2013/07/19/what-kind-of-queries-are-bad-for-mysql/

Cheers.

 

Lol.

 

This is gold.

I'm not sure if you should be selling scripts. The code you posted was beyond bad, and the last comment was just the tip of the iceberg.

 

Sorry, my mistake.
And yes, I'm shure.. from 2005. Just ignore my response about empty check
No need of that anyway, while the topic starter says that there are various search criteria used to match recipes

 

Hi,

There's a really elegant and straightforward solution to this using the SQL IN() function. There's some stuff above that I do agree with and some I don't. Here's my preference:

$skills = array("B","I","A");      // Define array of skills
$skill_sql = '';                          // Create comma separated list of skills to use in SQL.
foreach ($skills as $skill) {
[INDENT]if (isset($_GET['skill_'.$skill])) {
[INDENT]$skill_sql .= '"' . $skill . '",';[/INDENT]
}[/INDENT]
}
$skill_sql= rtrim($skill_sql,',');  //  Remove any trailing commas from the sql.

PHP:

NOW, use the SQL IN() statement - EXTREMELY useful in this case:

$query_sql = 'SELECT FROM `recipes` WHERE `skill_level` IN(' . $skill_sql . ')';

PHP:

So, for example, if "I" and "A" have been checked on the input form, the query will look like this:

SELECT FROM `recipes` WHERE `skill_level` IN( "I", "A" );

Give me a shout if anything is unclear.

Cheers
Mike