search query from array

[dtyson2000]

I'm like 95% done with this pain in the butt but can't get past this thing.

I have a search page that takes values from multiple form elements and pieces together a query. I've come up with a solution for a multiple checkbox search that queries the database column DAY and returns whatever days are selected in the checkboxes. It may not be pretty but looks like this:

[code]
FORM:
input type "checkbox" name="day[]" value="Sunday"... etc.

PHP PAGE:
if($_POST['day'] != '') {
$days = $_POST['day'];
$query .= "(";
foreach($days as $chosen) {
$query .= "day = '$chosen' || ";
}
$query = rtrim($query," || ");
$query .= ") && ";
}
[/code]

The next part is the same idea but a little more tricky

[code]
FORM:
<input type "checkbox" name="type_array[]" value="red">
<input type "checkbox" name="type_array[]" value="green">
<input type "checkbox" name="type_array[]" value="blue">
[/code]

The problem is that in the database, the column that is queried is full of numeric equivalents to the types in the array. So in that column you have "1,2,3" or "1,3" etc. (depending on what was input and it goes through like 22).

I'm trying to use the same sort of query as with the DAY column but am certainly open to suggestion. Here's what I've been working with:

[code]
if($_POST['type_array']) {
$types = $_POST['type_array'];
$query .="(";
foreach($types as $chosen) {
$query .= "type LIKE '$chosen' || ";
}
$query = rtrim($query," || ");
$query .= ") && ";
}
[/code]

Again, some people may only need to call rows that include like 1 and 22 - others may need 4,5,6,13 for this part of the larger query. Usually, they will search for one at a time but may need more.

Any thoughts, anyone?

Thanks, in advance!

[corbin]

Does mysql use ||?  I dont remember, but I dont think it does.

[dtyson2000]

[quote author=corbin link=topic=120465.msg494123#msg494123 date=1167550642]
Does mysql use ||?  I dont remember, but I dont think it does.
[/quote]

Yeah... it does.

[URL=http://www.phpfreaks.com/manuals/mysql/functions.html#logical-operators]http://www.phpfreaks.com/manuals/mysql/functions.html#logical-operators[/URL]

Just felt like using those. I have no particular reason.

[Barand]

Easier to use the IN syntax than multiple ORs

... WHERE day IN ('Sunday', 'Tuesday', 'Friday')

... WHERE color IN (1,3)

and jou can get the values just by JOINing the array elements

[dtyson2000]

Thanks Barand. I'll give it a shot right now. By the way, I think it was one of your posts that set me on the right track to getting the DAY one to work. Thanks!

So the syntax would be like this?

[code]
if($_POST['type_array']) {
$types = $_POST['type_array'];
$query .="(";
foreach($types as $chosen) {
$query .= "$chosen IN (1,2,3,4,5)";
}
$query = rtrim($query," || ");
$query .= ") && ";
}
[/code]

I can see the chosen values being evaluated against the but I'm not sure how this works. It's still not limiting the results to just those records that have say '12' as $chosen.

I'm just not sure how the IN syntax works and reading the MySQL manual on the matter is like reading Hesse to figure out how to fix my Volkswagen.

[Barand]

try
[code]
<?php
  if($_POST['type_array']) {
$types = $_POST['type_array'];
        $typelist  = join ("','", $types);
   
        $sql = "SELECT * FROM mytable WHERE chosen IN ('$typelist')";
   
        echo "<p>$sql</p>";
        echo '<hr>';
  }

  /**
  * the sample form
  */
  echo "<form method='post'>\n";

  $types = array(1,2,3,4,5);
  foreach ($types as $val) {
      echo "<input type='checkbox' name='type_array[]' value='$val'> $val<br>\n";
  }
  echo "<input type='submit' name='action' value='Submit'>\n";
 
  echo "</form>\n";
?>
[/code]