PHP / sql search on multiple columns

[stevebob]

I have a database driven site and need to add an 'advanced search' function to it, where users can select multiple options from tick boxes or drop down menus to search on. They may choose to search on some or all of columns.

The code below seems to work, but only if I have selected a value for each of the variables. If any are left empty then the results page shows no results. So if teh user chooses to search by gender but chooses noting for genre then they get no results. Is there a way to have the results show even if one variable has had no value, or have a 'wildcard' to select all from that section? I have tried using for example and 'all genres' option on the search with a value of '0' or '-1' but this does not seem to work.

SELECT *
FROM performers
WHERE gender = 'colname' AND genre LIKE '%colgenre%' AND voicetype LIKE '%colvoice'
ORDER BY name ASC


Also, I am using drop-down lists at the moment, so can only select one choice for each variable. How can I set up the search to use multiple checkboxes for 'genre' and search for ANY of the selected - e.g. if you tick 'documentary' and 'corporate' it will look for both of those values in the 'genre' column, as well as the other variables.

Hope you can help. I am new to database driven sites and this project has snowballed! Now i'm stuck!

[redarrow]

Fill in the xxx give it a go good luck.

[code]

//connect to database for data result useing like and %wildcard

$db=MYSQL_CONNECT("localhost","xxx","xxx");
MYSQL_SELECT_DB("promotor" , $db);



$query="SELECT * FROM xxxx WHERE xxx like '%".$xxx."%' AND xxx LIKE '%".$xxx."%'
AND xxx LIKE '%".$xxx."%' ORDER BY ASC";

$result=MYSQL_QUERY($query);

[/code]

[stevebob]

When i tried the above I got errors about unexpected '%'.
Maybe it will help if I post the code I am using.

For the search page (build in dreamweaver if that makes any difference), here is the code:

[code]
<td><form id="advancedsearch" name="advancedsearch" method="post" action="advancedresults.php">
      <table border="0" class="titles">
        <tr valign="top">
          <td width="105">Gender:
          
          <p>
            <label>
            <span class="maintext">
<input type="radio" name="gender" value="male" />
Male</span></label>
            <span class="maintext"><br />
            <label>
            <input type="radio" name="gender" value="female" />
Female</label>
            <br />
            <label>
            <input type="radio" name="gender" value="children" />
Children</label>
            </span><br />
          </p></td>
          <td width="112">Age group:
            <p>
              <label>
                <span class="maintext">
                <input type="radio" name="agegroups" value="children" />
                Children</span></label>
              <span class="maintext"><br />
              <label>
                <input type="radio" name="agegroups" value="teenager" />
                Teenager</label>
              <br />
              <label>
                <input type="radio" name="agegroups" value="twenties" />
                20's</label>
              <br />
              <label>
                <input type="radio" name="agegroups" value="thirties" />
                30's</label>
              <br />
              <label>
                <input type="radio" name="agegroups" value="forties" />
                40's</label>
              <br />
              <label>
                <input type="radio" name="agegroups" value="fifties" />
                50's</label>
              <br />
              <label>
                <input type="radio" name="agegroups" value="sixties" />
                60+</label>
              </span><br />
            </p></td>
          <td>Genre
            <select name="genre" id="genre">
              <option value="documentary">Documentary</option>
              <option value="corporate">Corporate</option>
              <option value="children's tv">Children's TV</option>
              <option value="animation">Animation</option>
            </select>
            <label></label>
            <p>
            </label>
            <label>Accent
            <select name="accent" id="accent">
              <option>RP</option>
              <option>Cockney</option>
              <option>Birmingham</option>
              <option>Black Country</option>
              <option>Australian</option>
              <option>Norfolk</option>
              <option>West Country</option>
                                                                                                                                                </select>
            </label>
</p>
            <p>
              <input name="Submit" type="submit" id="Submit" value="Submit" />
            </p></td>
        </tr>
      </table>
          </form>    </td>
[/code]


For the recordset in the results page, I have this entered in dreamweaver:
[code]
SELECT *
FROM performers
WHERE gender = 'colname'   AND genre LIKE '%colgenre%'  AND accents LIKE '%colaccent%' AND agegroups LIKE '%colage%'
ORDER BY name ASC
[/code]

which seems to have produced this code:

[code]
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colaccent_Recordset1 = "0";
if (isset($_POST['accent'])) {
  $colaccent_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['accent'] : addslashes($_POST['accent']);
}
$colage_Recordset1 = "0";
if (isset($_POST['agegroups'])) {
  $colage_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['agegroups'] : addslashes($_POST['agegroups']);
}
$colgenre_Recordset1 = "0";
if (isset($_POST['genre'])) {
  $colgenre_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['genre'] : addslashes($_POST['genre']);
}
$colname_Recordset1 = "0";
if (isset($_POST['gender'])) {
  $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['gender'] : addslashes($_POST['gender']);
}
mysql_select_db($database_connFirstvoice, $connFirstvoice);
$query_Recordset1 = sprintf("SELECT * FROM performers WHERE gender = '%s'   AND genre LIKE '%%%s%%'  AND accents LIKE '%%%s%%' AND agegroups LIKE '%%%s%%' ORDER BY name ASC", $colname_Recordset1,$colgenre_Recordset1,$colaccent_Recordset1,$colage_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connFirstvoice) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

[/code]
The search works fine as long as you have selected an option for each variable. However, not all users will want to narrow it down so much.

My question is how do I work things so that the search does not fail if one of the search fields has been left empty. Is there a 'wildcard' option I can choose or can I get it to ignore empty fields?

HELP!!!!