Retrieving Multiple Checkbox Values from a Database

Hi everyone, please help me out on this if you can.

I have a site where a user can select multiple checkbox values, banana, apple, orange, strawberry and raspberry.

I would like the name of the Juice to be displayed if the fruit is contained in it. For example..if strawberry and raspberry was selected then Berry Juice would be outputted.

Here is the code I am using for the checkboxes.

<form action="result.php" method="POST" >
<input type="checkbox" name="ingredients[]" value="Banana">Banana<br>
<input type="checkbox" name="ingredients[]" value="Apple">Apple<br>
<input type="checkbox" name="ingredients[]" value="Orange">Orange<br>
<input type="checkbox" name="ingredients[]" value="Raspberry">Raspberry<br>
<input type="checkbox" name="ingredients[]" value="Strawberry">Strawberry<br>
</span><br />
<input type="submit" value="Submit" />

I only have two columns in my table..juice name and ingredients. I have multiple values in each row.

This is the way my table is set up.

Juice Name    Ingredients

 

Berry            Raspberry, Strawberry

Cider            Apple  Banana

Here is my php code for result.php

<?PHP

$dbhost = "x";
$dbname = "x";
$dbuser = "x";
$dbpass = "x";

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());


$query = "SELECT juicename FROM juice;

echo $query; 

$apple = $_POST['apple'];
$banana = $_POST['banana']; 
$orange = $_POST['orange'];
$raspberry = md5($_POST['raspberry']);
$strawberry = $_POST['strawberry'];

$query = "SELECT juicename FROM juice WHERE match (ingredients) against searchvalue
values ('$apple', '$banana', '$orange', '$raspberry', '$strawberry')";
mysql_query($query) or die(mysql_error());
mysql_close();

echo $query
?>

Many thanks

EDIT: please use

 tags

$checked = array();

foreach($_POST['ingrediants'] as $value)

{

$checked[] = $value;

}

Try something like that, then echo the $checked

Instead of having a comma separated ingredients list, you need an ingredients table.  Also, you would need a relations table

This way your relations table will be like so..  Let's say Cider has an id of 52 in the juice table; and raspberry has an id of 108 in the ingredients table.

So in your relations table... or combinations table... whatever you want to call it.. you would have.

id          | juice          | ingredient

1          | 52            | 108

Then you can select ingredients from combinations WHERE juice = 52

* This is what's known as a many-to-many to relationship.

You should try to normalize your table. There are a few better ways to set your table up... But here is a simple way...

CREATE TABLE tIngredients (Juice VARCHAR(30), Ingredient VARCHAR(30))

an example of the table would be:

Juice                |            Ingredient

---------------------------------------------

Berry Juice                      Raspberry

Berry Juice                      Blue Berry

Berry Juice                      Apple

Berry Juice                      Strawberry

Apple Cider                    Apple

Apple Cider                    Cinnamon

Create a SQL statement as such:

SELECT Juice FROM tIngredients WHERE Ingredient='Apple' OR Ingredient='Orange';

You would need to use PHP code to construct the WHERE clause of this SQL Statement based on which Ingredients the user submitted through the form.

You could then execute this SQL statement to returne all the juices that were selected and loop through them to display.

Many thanks guys, I have redone the tables as ObjNoob suggested.

Regarding the php code, what way do I structure that now??

objNoob's method would not be normalizing your database.  Normalizing your database consists of utilizing your primary keys.  If your "keys" are just varchars, they can easily be disorganized by having a differently spelled "Berry Juice."  Perhaps one with two "i"'s.

His method is essentially the same as my suggestion without the use of primary keys.

Thanks Zanus...my priority at this stage is getting a result from the php code.  Once I have basic functionality, I can then expand my database and normalize the tables.

Should I go back to the php forums for help with this??

Please don't start on new topic on the same subject, because I or someone else will have to delete it.  I have told you what you need to know, if you have questions, just ask.

In my opinion though, your main priority should be getting your database setup ... functionally and normalized as to aid your PHP adventures.  If you setup your database un-normalized, you'll just end up at square one later.

Apologies Zanus, I was unsure of the rules. 

My question is what should my php code now be to query the datababse?

What does your database look like ...... now

What does your database look like ...... now

Juice                |            Ingredient

---------------------------------------------

Berry Juice                      Raspberry

Berry Juice                      Blue Berry

Berry Juice                      Apple

Berry Juice                      Strawberry

Apple Cider                    Apple

Apple Cider                    Cinnamon

An example of what it looks like above.  So if a user picked blueberry and strawberry the result returned should be Berry Juice.

Many thanks

Zanus, anyone.....help!!!

Does anyone have any idea what my php code should now look like???

First check if $_POST['ingredients'] is set

if (isset($_POST['ingredients'])){
//since ingredients is set we need to build the SQL statement to query the database
$count = count($_POST['ingredients']);
$sql_where = null;
for ($i=0;$i<$count;$i++){
  if ($i != $count - 1){
    // not the last; include comma
    $sql_where .= " ingredient='$_POST['ingredients'][$i]',";
  }else{
   // is the last; no comma
    $sql_where .= " ingredient='$_POST['ingredients'][$i]'";
  }
}
$theJuices = array();
$sql = "SELECT Juice FROM tIngredients WHERE".$sql_where;
$result = mysql($sql);
while ($row = mysql_fetch_assoc($result)){
   if (!in_array($row['Juice'], $theJuices){
      $theJuices[] = $row['Juice'];
   }
}
echo 'The juices having the selected ingredients are:<br />';
foreach ($theJuices as $El_Jugo){
   echo $El_Jugo.'<br />';
}

}else{
// $_POST['ingredients'] is not set; show form
<form action="result.php" method="POST" ><input type="checkbox" name="ingredients[]" value="Banana">Banana<br><input type="checkbox" name="ingredients[]" value="Apple">Apple<br><input type="checkbox" name="ingredients[]" value="Orange">Orange<br><input type="checkbox" name="ingredients[]" value="Raspberry">Raspberry<br><input type="checkbox" name="ingredients[]" value="Strawberry">Strawberry<br></span><br /><input type="submit" value="Submit" />
}

objNoob's method would not be normalizing your database.  Normalizing your database consists of utilizing your primary keys.  If your "keys" are just varchars, they can easily be disorganized by having a differently spelled "Berry Juice."  Perhaps one with two "i"'s.

 

His method is essentially the same as my suggestion without the use of primary keys.

In your case, having a table defining juice, a table defining ingredients, and a relation table, the relation table wouldn't need an ID column, the canidate key would be  (Juice, Ingredient). In any senario, you could define a Juice name Berry, or Berri, or Berrrrry Delicious, which could all be separate juices. If you want to notify the user of possible mistakes, you would have to examine the existing juices for similarities. For his simple task I opted to travel the  path of least resistance, and throwing in some words which might cause one to be inquisitive.