multiple selection (checkboxes)

LordRogaine

This probably isn't very difficult, but I can't seem to get it right. I want a user of my site that I am creating to be able to pick more than one selection from a topic using check boxes. Whenever I check off more than one box from a group I only get the results from one of the checked selections. Here's an example of the form I am using.

<form method="POST" action="Http://mysite.com/1.php">
<p><b>ANIMALS</b></p>
<p><input type="checkbox" name="animals[]" value="cats">Cats</p>
<p><input type="checkbox" name="animals[]" value="dogs">Dogs</p>
<p><input type="checkbox" name="animals[]" value="birds">Birds</p>
<p><input type="checkbox" name="animals[]" value="turtles">Turtles</p>
<p><input type="submit" value="Submit" name="submit"><input type="reset" value="Reset" name="reset"></p>
</form>

I put brackets [] after the name of every value in the form and my select statement looks something like this.

....."select * from table_name where animals = '$animals'";

where am I going wrong?

cgraz

That won't work. The brackets return the variables in an array:

$animals['0']
$animals['1']
$animals['2']

Cgraz

LordRogaine

so I actually have to number the values in the brackets like you have? Is that it? What about the select statement? What do I need to change there? Thanks for your help.

cgraz

You could use a series of if statements to determine what they chose and build a select.

in pseudo:
if nothing selected
select * from table

if cats is selected and nothing else
select * from table where animals = 'cats'

if cats and dogs are selected
select * from table where animals='cats' AND animals='dogs'

if cats dogs and birds are selected
select * from table where animals='cats' AND animals='dogs' AND animals='birds'

etc.

LordRogaine

this is fine if ther are only four selections but what if someone has a selection of let's say states in the USA? At the very least there will be 50 if statements. Is there an easier way?

cgraz

Never done this before, but possibly something like

<?

$q = "SELECT * FROM table_name";

   for ($i=0; $i < count($animals); $i++) {

    if ($q == 'SELECT * FROM table_name') {
       $q .= " WHERE animals='$animals[$i]'";
    } else {
       $q .= " AND animals='$animals[$i]'";
    }
   }

$q .= ")";

echo <b>query</b> = $q";
$result = mysql_query($q);

?>

That will also echo the query so you can see if it is running the correct query.

Cgraz

cgraz

I figured this out. Change your code to this

<?

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

$query = "SELECT * FROM your_table";

for ($i=0; $i < count($animals); $i++) {

  if ($query == 'SELECT * FROM your_table') {
     $query .= " WHERE animals='$animals[$i]'";
  } else {
     $query .= "  OR animals='$animals[$i]'";
  }

}

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

// do what you want with the results

?>

Change your_table to your table name in both places.

Cgraz

LordRogaine

Thanks for getting back to me with an answer. Have you actually tried the script and it works? I ask this only because it doesn't work for me. If I know you have it working then I know it's a problem on my end, which is probably the case lol. Are you putting brackets [] in your html form next to the group name "animals[]"? Thanks for the help.

cgraz

Yes, I tried it. The only difference is I didn't use a form, I just put an array value right into the script:

$animals = array(cats, dogs);

So the entire script would look something like this

<?

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

/* added array here. Try with this. If this works, then the problem 
is most likely in your form or in how you are calling the array. */
$animals = array(cats, dogs);

$query = "SELECT * FROM your_table";

for ($i=0; $i < count($animals); $i++) {

  if ($query == 'SELECT * FROM your_table') {
     $query .= " WHERE animals='$animals[$i]'";
  } else {
     $query .= "  OR animals='$animals[$i]'";
  }

}

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

// so you know how many results you are getting without actually displaying. This will help in solving any problems.
echo mysql_num_rows($result) . "<br>\n";

// this will help you see what your query looks like and also help solve any problems
echo $query;

// do what you want with the results

?>

By echoing the query and the number of rows, you should be able to quickly determine what's not working properly. Once again, be sure to change your_table to your table name in BOTH places. If you're using a variable, something like

select * from $table

It may not work properly. I think I had trouble when I used a variable for the table name. You will probably want to try changing something like that to
select * from pets (or whatever your table name is, rather than using a variable)

Let me know how it goes.

Cgraz

LordRogaine

still having some problems. For $mysql_num_rows I'm getting "0" results. The query is printing out and it looks fine. As for when I echo my results (there should be nothing I guess since mysql_num_rows was "0") I get a capital "S" next to my results. ???????????? Any ideas? Thanks again for your help. Greatly appreciated.

cgraz

What does the query it's echoing look like? Did you try it first with the $animal = array(dogs, cats) like i posted? do you have anything in your database? do these records have dogs or cats under the animal category?

Also, you shouldn't double post. You'd get better help if you only posted in one forum.

philipolson

Consider using the sql command IN, it will make this much cleaner. Of course you'll want to check and make sure any animals were chosen first before running any query at all.

$sql = "SELECT * FROM blah WHERE foo IN ('" . implode("','",$_POST['animals']) . "')";
print $sql;

Regarding your question, a capital S? Show us some code and what the query looks like after printing it (print $sql). There is a good example on how to do all of this at the following location, it even includes a little error handling:
http://www.php.net/mysql_fetch_assoc

LordRogaine

Yes the database is filled with info. I have been able to retrieve info from the database in the past with a select statement. My only problem is when I use the brackets[] in the html form to pick more than one selection in the group. This is what the query looked like that was echoed from the code you gave me. Thanks again for your help.

SELECT * FROM table WHERE animals='tiger, lion, dog, cat'

cgraz

It should output with OR in there

But anyways, use the code philipolson suggested. Using IN is MUCH EASIER.

Cgraz

LordRogaine

Okay, I'm getting closer to something here. I combined the two codes and according to how many selections I make from the "animals" catagory I get the correct number of results using echo mysql_num_rows($result).
My problem now is that I can't view the actual results. They won't print. I know that it is reading the database properly now because I'm always getting the correct number of results. Just wish they would print. This is what I am using right now.

Thanks for all the help guys!

Oh yeah. I'm a bit confused about what foo does?

$sql = "SELECT * FROM table WHERE foo in ('" . implode("','",$_POST['animals']) . "')";

$query = "SELECT * FROM table";

for ($i=0; $i < count($animals); $i++) {

  if ($query == 'SELECT * FROM table') {
     $query .= " WHERE animals='$animals[$i]'";
  } else {
     $query .= "  OR animals='$animals[$i]'";
  }

}

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

echo mysql_num_rows($result) . "<br>\n";

echo $sql;

echo "<br>\n";
echo "<b>Animal:</b>";
echo $result["animals"];
echo "<br>\n";
echo "<b>Weight:</b>";
echo $result["weight"];
echo "<br>\n";
echo "<b>Age:</b>";
echo $result["age"];
echo "<br>\n";

philipolson

When in doubt, try it. Print out that query I created to see what it does, you will see what happens. Also read that link I posted about what the sql command IN looks like. As well as what implode() does.

http://www.php.net/implode

Also to retrieve data from a database you must fetch it, the following manual page includes a large example:

http://www.php.net/mysql_fetch_assoc

cgraz

Why are you using both select statements? You're only executing one query ($query) and echoing the other ($sql). I strongly recommend you read some php/mysql tutorials.

$query = "SELECT * FROM table"; 

   for ($i=0; $i < count($animals); $i++) { 

  if ($query == 'SELECT * FROM table') { 
     $query .= " WHERE animals='$animals[$i]'"; 
  } else { 
     $query .= " OR animals='$animals[$i]'"; 
  } 

   } 

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

echo mysql_num_rows($result) . "<br>\n"; 

// you have to use a while loop
   while($a_row=mysql_fetch_assoc($result)) {

  print "<br>\n"; 
  print "<b>Animal:</b> " . $a_row["animals"] . "<br>\n";  
  print "<b>Weight:</b> " . $a_row["weight"] . "<br>\n";
  print "<b>Age:</b> " . $a_row["age"] . "<br>\n";

   }

Or philipolson's method

$sql = "SELECT * FROM table WHERE animals in ('" . implode("','",$_POST['animals']) . "')"; 
$result = mysql_query($sql);

echo mysql_num_rows($result) . "<br>\n"; 

   while($a_row=mysql_fetch_assoc($result)) {

  print "<br>\n"; 
  print "<b>Animal:</b> " . $a_row["animals"] . "<br>\n";  
  print "<b>Weight:</b> " . $a_row["weight"] . "<br>\n";
  print "<b>Age:</b> " . $a_row["age"] . "<br>\n";

   }

if that for some reason produces a parse error you can try

$animals = implode(",", $_POST['animals']);

$sql = "SELECT * FROM table WHERE animals IN ('$animals')";
$result = mysql_query($sql);

echo mysql_num_rows($result) . "<br>\n"; 

   while($a_row=mysql_fetch_assoc($result)) {

  print "<br>\n"; 
  print "<b>Animal:</b> " . $a_row["animals"] . "<br>\n";  
  print "<b>Weight:</b> " . $a_row["weight"] . "<br>\n";
  print "<b>Age:</b> " . $a_row["age"] . "<br>\n";

   }

Cgraz

LordRogaine

Eureka!!!! It finally works!!! I want to thank both of you for your help. Also thanks for the final scripts and the links to the manual. I'm finding them very useful. I tried adding another multipe select category to the script and it actually worked. Thanks again. It's greatly appreciated!

philipolson

Just fyi, it's important to use "','" as the first argument of implode and not "," as it means creating 'a','b','c' instead of the incorrect 'a,b,c'. And the code I quoted will never create a parse error.

And btw, this assumes you are escaping your strings with the php directive magic_quotes_gpc on otherwise you'd use addslashes() but this applies to any query you'll ever run.

And instead of simply printing out the number of rows you should actually make it a check to see if you want to run mysql_fetch_assoc or not. If you have zero rows say so but do not attempt to fetch rows at the same time.

LordRogaine

Okay, two quick questions which you probably were waiting. But first, I wanted to add two more catagories that had multiple select to the script so I add them in like this. It works perfectly, is this the proper way? Now for my two questions. I want as part of the select to be an option to select "ALL" from that catagory. I tried using an "IF" statement but I keep coming up with errors. Also, if there aren't any boxes checked off to a catagory I get an error. How can I fix this. I tried using an "if" statement for this but still got errors. I looked at the "manual" too but didn't see anything on this. Thanks again.

$sql = "SELECT * FROM table WHERE animals in ('" . implode("','",$POST['animals']) . "')
and People in ('" . implode("','",$POST['people']) . "')
and Fruit in ('" . implode("','",$_POST['fruit']) . "')";
$result = mysql_query($sql);

echo mysql_num_rows($result) . "<br>\n";

while($a_row=mysql_fetch_assoc($result)) {

  print "<br>\n";
  print "<b>Animals:</b> " . $a_row["animals"] . "<br>\n";  
  print "<b>People:</b> " . $a_row["people"] . "<br>\n";  
  print "<b>Fruit:</b> " . $a_row["fruit"] . "<br>\n";

}