Jump to content

PHP / sql search on multiple columns


stevebob

Recommended Posts

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!
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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!!!!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.