Multi-term search with SELECT

[strangebeer]

I've got a search form that's searching a MySQL DB. I've got 3 columns that comprise a "Date": Month, Day, Year, and I need to get them searched so the user can look them up either by month or by Month/Day or Month/Day/Year, etc. The problem I'm hitting, is that I don't know how to tell the search.php form that there is more than one searchterm. I can throw in <input type="hidden" value="month"> but that would only cover 1 of the three columns.

Here is the code for the search form:

<form action="/shows/search.php" method="get">
<select name="month">
                <option selected>Month
                <option value="January">January
                <option value="February">February
                etc, etc...
</select>
<select name="day">
                <option selected>Day
                <option value="01">1
                <option value="02">2
                etc, etc...
</select>
<select name="year">
                <option selected>Year
                <option value="2003">2003
                <option value="2004">2004
</select>
<input type="submit" value="Go">
</form>

Here is the code for the search.php:

<h1> Search Results</h1>
<?php
include("$lib/dbconnect.php");

trim($searchterm);
if (!$searchterm)
{
   echo ("You havent entered any info please do so<br />");
   include ("$scriptroot/searchform.php");
   exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

mysql_select_db("database");
$query = "select * from db_table where ".$searchtype." like
   '%".$searchterm."%'";

[cgraz]

SELECT * FROM db_table WHERE month='$month' AND day='$day' AND year='$year'

the problem with this is what if they don't select a day. Then your query would look like this after all the vars were plugged in

SELECT * FROM db_table WHERE month='January' AND day='' AND year='2001'

this wouldn't return anything since it's saying AND the date equals nothing.

What you'd want to do is check for which fields were filled out, then use that to run your search

$query = "SELECT * FROM db_table";

foreach($_POST as $key => $value) {
   if ($query == "SELECT * FROM db_table") {
      $query .= "WHERE $key='$value'";
   } else {
      $query .= " AND $key='$value'";
   }
}

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

Something to that extent should work for you.

Cgraz

[strangebeer]

Thanks for the response.

Here's what I have now:

$query = "select * from db_table";

foreach($_POST as $key => $value) {
        if ($query == "select * from db_table") {
                $query .= "where $key='$value'";
        } else {
                $query .= " and $key='$value'";
        }
}

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

A couple of things happen now. When I do a search for something that previously worked, it returns all records, not just ones matching the query. But that's with the radio buttons and the input text field. When I use the month/day/year search that I was initially seeking assistance with, I get the same thing...all 4 of the test records come back
Non-working URL: $URL/search.php?searchterm=&month=March&day=21&year=2003
Working URL: $URL/search.php?searchtype=genre&searchterm=punk

I have suspicions about the query line. Do I need something else after the "select * from db_table";?

[strangebeer]

Okay, I solved my problem. I made a separate search script for the date, datesearch.php.

if ($month == 'Month') { $month = '%'; }
if ($day == 'Day') { $day = '%'; }
if ($year == 'Year') { $year = '%'; }

$query = "select * from db_table where month like
   '".$month."' and day like '".$day."' and year like '".$year."'";

Thanks for the help, cgraz.