Retrieving Multiple Checkbox Values from a Database

[krilli]

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

[Nyight]

I see what you're asking but I'm failing to see the problem, could you elaborate a little more?

[Edit]
In the future, please wrap your code with [ code=php ] (without the spaces obviously) and
[/code] so that the forum can process it and make it a bit more readable. The background of the quick reply box at the bottom actually asks you to do this as well. It's kind of hard to see. Thanks.

[krilli]

I see what you're asking but I'm failing to see the problem, could you elaborate a little more?

No problem...will do.

Where I am having problems is getting the php code to produce results.

For example my table now looks like this

Juice                 |            Ingredient
---------------------------------------------
Berry Juice                       Raspberry
Berry Juice                       Blue Berry
Berry Juice                       Apple
Berry Juice                      Strawberry
Apple Cider                     Apple
Apple Cider                     Cinnamon

So if a user picked blueberry and strawberry the result should be Berry Juice.

Any ideas on what changes I should make to my php??

Thanking you.

[Nyight]

Ah, I should have seen that to begin with. I'll explain and thanks for the clarification :)

You're only echoing the query, which is a string. You're not receiving the results at all with your current code. What you would want is something like the folowing:

$results = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($results)==1)){ // If there is only one row that was returned
   $results = mysql_fetch_array($results);
   echo $results['juice']; // If the column name is incorrect, just change it to match
}elseif(mysql_num_rows($results)>1)){ // If there is more than one row that was returned
   while($result=mysql_fetch_array($results)){ // Loop through each row that was returned and display it
      echo $result['juice']."<br />";
   }
}else{ //If there were no rows returned, this is just to account for every option and is entirely optional
   echo "No juice for you!";
}

[Note]
This is entirely untested. I haven't even checked to make sure the basic syntax works so if you copy/paste don't be surprised if it errors.

[Edit]
mysql_close() isn't required to be used as it's automatically closed once the script finishes (page loads).

[Links]
http://php.net/manual/en/function.mysql-close.php

[krilli]

Thanks a million for your help, but im getting query was empty!! No syntax errors, there was just a couple of brackets that needed fixing.

Any other suggestions??

[Nyight]

Query was empty? Is that actually what was displayed on the page itself? And please repost your newest code.

[krilli]

Query was empty? Is that actually what was displayed on the page itself? And please repost your newest code.

<?PHP

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

//connect to database or display sql error message.
mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$results = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($results)==1){ // If there is only one row that was returned
$results = mysql_fetch_array($results);
echo $results; // If the column name is incorrect, just change it to match
}elseif(mysql_num_rows($results)>1){ // If there is more than one row that was returned
while($result=mysql_fetch_array($results)){ // Loop through each row that was returned and display it
echo $result."<br />";
}
}else{ //If there were no rows returned, this is just to account for every option and is entirely optional
echo "No juices matched!";
}
?>

I tried to wrap the code, hope it worked.

There is my php code above, and yes the result was query was empty...nothing else was printed to the screen

[Nyight]

Lol the code I added was designed to be added into, not entirely replace xD. Your query variable isn't defined so when it goes to run the query, the query is empty lol.

This is closer to what you need. Again, untested so check it. Also, learn and compare what you were originally doing to the newer stuff :). You had it mostly right, you just left a few critical things out.

<?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"; // There really is no need for this line as nothing is being done with it.

//echo $query; // Same for this line.

$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')";
$results = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($results)==1){ // If there is only one row that was returned
   $results = mysql_fetch_array($results);
   echo $results['Juice']; // If the column name is incorrect, just change it to match
}elseif(mysql_num_rows($results)>1){ // If there is more than one row that was returned
   while($result=mysql_fetch_array($results)){ // Loop through each row that was returned and display it
      echo $result['Juice']."<br />";
   }
}else{ //If there were no rows returned, this is just to account for every option and is entirely optional
   echo "No juices matched!";
}
?>

Out of curiosity, why are you hashing the raspberry on line 19?

[krilli]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'searchvalue values ('', '', '', '', '')' at line 1

This is what I am now getting???

I never even noticed that, thanks for pointing it out..its a mistake from earlier code.

Any ideas as to where i go from here?

[Nyight]

It would appear that you need to have searchvalue wrapped in paranthesis.

$query = "SELECT juicename FROM juice WHERE match (ingredients) against(searchvalue)
values ('$apple', '$banana', '$orange', '$raspberry', '$strawberry')"

By chance is this entire script your own or are you mixing and matching pieces of code from here and there? This is also getting into the area I'm not sure about as I've never used those terms in a query before so it might prove to be difficult for me to answer but I'll still try.

[Links]
http://devzone.zend.com/article/1304

[krilli]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values ('', '', '', '', '')' at line 2

This is the new message.

I really appreciate your help and to be honest at this stage I think the code is more yours than mine.

Any ideas??

[Nyight]

The problem is that you're using ingredients[] within your form. The computer sends the information in as an array so you could look at it like $_POST[0] and so forth. When you have a ton of variables that are all going to be the same, usually radio buttons, this is VERY VERY useful, but for checkboxes, not so much.

<form action="result.php" method="POST" >
<input type="checkbox" name="banana" value="Banana">Banana<br>
<input type="checkbox" name="apple" value="Apple">Apple<br>
<input type="checkbox" name="orange" value="Orange">Orange<br>
<input type="checkbox" name="raspberry" value="Raspberry">Raspberry<br>
<input type="checkbox" name="strawberry" value="Strawberry">Strawberry<br>
</span><br />
<input type="submit" value="Submit" />

The name of the input is what gets sent with the post, it's what allows you to access the value. As an example, the name banana under the post would look like $_POST="banana"; which should help the following make a bit more sense.

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

Once you have assigned the new variables over, your query will no longer be empty as it's using the new variables to fill it in.

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

And with that, hopefully it'll all work.