stevebob

I have built a mqsql database based site, and am using php / dreamweaver. I need the site to have an 'adavnced search section' where you can choose a number of options to search by. So far I have managed to build what you will see below. The search works, but you MUST select an option for each section or else it fails. My question is how do I get it to either ignore a field that has not been posted or use a 'wildcard' to select all form taht section? 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!!!! Also, I would like to search wor more than one item in a field, e.g you might select 'documentary' and 'animation' in the genre field, adn I would like it to pick out records that contain either. How do I do this? Thanks in advance for your help! I took on a project that was fine until advanced search was requested now I am really struggling!

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!!!!

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!

Thanks. Does the extensiion work for only searches where you type in the variables? Or can it be configured for drop-down menus / checkboxes? Is there a way to do this without shelling out for an extenstion?

I have a database drivensite and need to add an 'advanced search' function to it. 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. Is there a way to have the results show even if one variable has had no value. 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. 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!