using a secondary query if the first one doesn't do anything

making sure that a result always appears

[togethercomms]

Hi All,

I am using MySQL and php to create a simple search engine, nothing too complicated.

I have made the main query work as it throws the results i want based on the criteria i choose.

What i am having trouble with is if the user selects "any" in 1 or all of the search criteria's.

This still throws up no results and goes to the error text.

<?php
$searchkeywords = $_POST['title'];
$searchlocation = $_POST['location'];
$searchmin = $_POST['min'];
$searchmax = $_POST['max'];
$searchrole = $_POST['role'];

echo "
  <strong>Job Search:</strong><br><br>
  <form class='frm' method='post' action='search.php'>
  Area of Expertise: <select name='title' class='title'>
 <option value=''>Search all areas of HR</option>
     <option value='hr generalist'>HR Generalist</option>
     <option value='employee relations'>Employee Relations</option>
     <option value='l&d/management development/talent management'>L&D/Management Development/Talent Management</option>
     <option value='recruitment/resourcing'>Recruitment/Resourcing</option>
     <option value='reward/expat/mobility'>Reward/Expat/Mobility</option>
     <option value='hr systems'>HR Systems</option>
     <option value='diversity'>Diversity</option>
     <option value='change management/organisational development'>Change Management/Organisational Development</option>
     <option value='management consultancy (HR)'>Management Consultancy (HR)</option>
     <option value='payroll'>Payroll</option>
     </select><br><br>
  <table align='center' cellspacing='0' cellpadding='0' border='0' width='280'>
  <tr><td>   
  salary (from): <br><select name='min' class='min'>
 <option value=''>Search all</option>
     <option value='15000'>15,000</option>
     <option value='20000'>20,000</option>
     <option value='25000'>25,000</option>
     <option value='30000'>30,000</option>
     <option value='35000'>35,000</option>
     <option value='40000'>40,000</option>
     <option value='45000'>45,000</option>
     <option value='50000'>50,000</option>
     <option value='55000'>55,000</option>
     <option value='60000'>60,000</option>
     <option value='65000'>65,000</option>
     <option value='70000'>70,000</option>
 <option value='75000'>75,000</option>
     <option value='80000'>80,000</option>
 <option value='85000'>85,000</option>
     <option value='90000'>90,000</option>
 <option value='95000'>95,000</option>
     <option value='100000'>100,000</option>
     </select>
  </td><td>   
  salary (to): <br><select name='max' class='max'>
 <option value=''>Search all</option>
     <option value='15000'>15,000</option>
     <option value='20000'>20,000</option>
     <option value='25000'>25,000</option>
     <option value='30000'>30,000</option>
     <option value='35000'>35,000</option>
     <option value='40000'>40,000</option>
     <option value='45000'>45,000</option>
     <option value='50000'>50,000</option>
     <option value='55000'>55,000</option>
     <option value='60000'>60,000</option>
     <option value='65000'>65,000</option>
     <option value='70000'>70,000</option>
 <option value='75000'>75,000</option>
     <option value='80000'>80,000</option>
 <option value='85000'>85,000</option>
     <option value='90000'>90,000</option>
 <option value='95000'>95,000</option>
     <option value='100000'>100,000</option>
     </select>
 </td></tr>
  </table>
  <br>
  Location: <br><select name='location' class='location'>
 <option value=''>Search all regions</option>
     <option value='london'>London</option>
     <option value='south-east'>South-East</option>
     <option value='south coast'>SouthCoast</option>
     <option value='south-west'>South-West</option>
     <option value='east-anglia'>East-Anglia</option>
     <option value='west midlands'>West Midlands</option>
     <option value='east midlands'>East Midlands</option>
     <option value='north west'>North West</option>
     <option value='north & north-east'>North & North-East</option>
     <option value='scotland'>Scotland</option>
     <option value='wales'>Wales</option>
     <option value='northern ireland'>Northern Ireland</option>
 <option value='republic of ireland'>Republic of Ireland</option>
     <option value='uk wide'>UK Wide</option>
 <option value='international'>International</option>
     </select><br><br>
  
  
  
  Type of role: <br><select name='role' class='role'>
 <option value=''>Search all</option>
     <option value='Permanent'>Permanent</option>
     <option value='Contract'>Contract</option>
     <option value='Temporary'>Temporary</option>
     </select><br><br>
  
  
     
  <input type='submit' class='submit' id='submit' value='Search'>
  </form>
  ";
?>
  </div>
  
</div>
  
 
<?php

//Connect to mysql db
$conn = mysql_connect('', '', '');
mysql_select_db('',$conn);

//result set for the current page
$rs = mysql_query("SELECT * FROM `job_board` WHERE `mean salary` between '$searchmin' and '$searchmax' and `keywords` like '%$searchkeywords%' and `region` = '$searchlocation' and `type` = '$searchrole'");

if (mysql_num_rows($rs) == 0) {

 echo "<div width='400px' align='center'><font color='#3F262E'>Sorry, currently there are no vacancies that match your criteria.<br><br>
Please try again by selecting a different set of search options or alternatively send your CV to us <span class='email'><a href='mailto: '>here</a></span><br><br>
Otherwise please call us on <strong></strong> and we can talk to you about possible roles we may be working on in the future.</font></div>";// Show message
 
} else {

 //Loop through the result set
if($rs) {
while ($newArray = mysql_fetch_array($rs)) {
$id = $newArray['id'];
$title = $newArray['title'];
$short = $newArray['short'];
$salary = $newArray['salary'];
$type = $newArray['type'];
$loc = $newArray['location'];
$ref = $newArray['ref'];
echo "<table width='530px' class='border' cellspacing='0' cellpadding='0' border='0'><tr><td>";
echo "<table width='530px' class='subject' cellspacing='0' cellpadding='0' border='0'><tr><td align='left'>".$title. "</td><td align='right'>" .$salary."</td></tr> <tr><td align='left'>".$type."&nbsp;(PG " .$ref.")</td><td align='right'>".$loc."</td></tr></table>";
echo "<table class='body' width='530px' cellspacing='0' cellpadding='0' border='0'><tr><td>".$short."........</td></tr></table>";
echo "<table width='540px' class='morelink' cellspacing='0' cellpadding='0' border='0'><tr><td align='left'><a target='_blank' href=\"job-info.php?id=".$id."\">I'm interested</a></td><td align='right'><a href='mailto:[e-mail]?subject=".$title." (PG ".$ref.")'>click to respond</a></td></tr></table>";
echo "</td></tr></table><br><br>";
}
}
}
?>

Hope you can help.
Many Thanks

[edited by: eelixduppy at 3:29 pm (utc) on Feb. 3, 2010]
[edit reason] exemplified [/edit]

[Readie]

What I tend to do here, is have the mysql string being:

$sql = "SELECT * FROM table WHERE 1";

Then, I can have something like:

$sel_title = mysql_real_escape_string($_POST['title']);
if(isset($sel_title) && $sel_title != "") {
$sql .= " AND `title` LIKE '%$sel_title%'";
}

(...)

$rs = mysql_query($sql);

2 things to note: notice the space at the start of the string to be appended to the existing $sql string,

and I know that it's your select menu, peeps can't use it to do a sql injection attack, so you'd think you wouldnt need a mysql_real_escape_string() - but they could create their own form that posts to the same location; just off-site. So escape it or you could regret it.

---

EDIT:

I also suggest changing the way you do the min/max salary so that people can specify a minimum with no maximum, or vice versa.

$sql .= " AND `mean salary` <= $searchmax";
$sql .= " AND `mean salary` >= $searchmin";

[togethercomms]

wow, that easy.

I will try it out, thanks for the extra advice.

[togethercomms]

that works absolutly amazingly.
exactly what i wanted.

Also very easy to add my pagination script to aswell.
Just another point, ver simple, how do i make sure that the form remembers the options selected by the user?

At the moment it goes back to defaults.

Many Thanks

[Readie]

There must be a better way than this, but a working method for remembering form post data is like this:

$role_sa = '';
$role_pe = '';
$role_co = '';
$role_te = '';
switch($searchrole) {
default:
$role_sa = ' selected';
break;
case "Permanent":
$role_pe = ' selected';
break;
case "Contract":
$role_co = ' selected';
break;
case "Temporary":
$role_te = ' selected';
break;
}
 
...
 
echo "<select name='role' class='role'> 
<option value=''" . $role_sa . ">Search all</option> 
<option value='Permanent'" . $role_pe . ">Permanent</option> 
<option value='Contract'" . $role_co . ">Contract</option> 
<option value='Temporary'" . $role_te . ">Temporary</option> 
</select>";

If you enable a text input you can cheat and simply do:

<input name='some_name' type='text' value='" . $some_variable . "'>

[rocknbil]

TMTOWTDI, the way I like to do it. The results are the same as "where 1".

User selections are meant to narrow results. So if you think about it, you don't need an "all". All is "default." Simple example, where $form_items represents what's in your form and maps those fields to database field names,

$form_items = array ( 
 'firstname' => 'fname', 
 'lastname' => 'lname', 
 'email => 'email' 
); 
// 
$where=null; 
foreach ($form_items as $key=>$value) { 
 if (isset($_POST[$key])) { 
 if ($where) { $where .= ' and'; } 
 $where .= " $form_items[$key]='$value'; 
 } 
} 
// 
list($order,$limit) = get_limit_string(); // write this func. 
$query = "select * from table"; 
if ($where) { $query .= " where $where"; } 
$query .= " $order $limit"; 

There's a tad more to it than that, managing dates, numeric values, like operators, cleansing the data, etc., but that's the framework.

how do i make sure that the form remembers the options selected by the user?

In the context of a search, this is a bad idea. When you search, and go back to a form - especially a big one, might be OK with a one-liner - you don't expect it to be populated with your terms. What happens is the user is returning to this form for another search, and may be likely searching for something else. They miss blanking out an item or two and don't get the results they expect, so give up thinking your site is broken. (Caveat: I have learned this from emergency calls from customers, only to find out they didn't de-select this or uncheck that.)

A thing some don't know, RESET does not BLANK OUT a form, it resets it to it's original loaded state. So if you load with values populated, it won't clear them.

But for other forms, in respect to textual values, this is easy:

$fname = (isset($_POST['fname']))?$_POST['fname']:'';
$lname = (isset($_POST['lname']))?$_POST['lname']:'';
$email = (isset($_POST['email']))?$_POST['email']:'';

Then go on to put those in the values field. For select lists and radio buttons, To really ecomomize/repurpose your code, you should do things like this when outputting forms:

$state = (isset($_POST['state']))?$_POST['state']:0;


$stateList = state_list('state',$state,$your_select_list_class);
$out .= '<label for="state">State:</label> ' . $stateList;

Then the function.

function stateList($nm,$value,$class) { 
 if (! $nm) { die("cannot create state list, no name supplied"); } 
 // 
 $class=(isset($class))?' class="'.$class.'"':''; 
 $list = '<select name="' . $nm . '" id="' . $nm . '"' . $class . '> 
 <option value="0">Select</option>'; 
 // The previous is important with select lists, always have 
 // a blank/zero value 
 // 
 $result=mysql_query("select st_id,abbrev from your_states_table order by abbrev asc"); 
 if (!$result) { die("can't get states list); } 
 while ($row=mysql_fetch_array($result)) { 
 $list .= '<option value="' . $row['st_id'] . '"'; 
 if (isset($value) and ($value == $row['st_id'])) { $list .= ' selected'; } 
 $list .= '>' . $row['abbrev'] . '</option>'; 
 } 
 mysql_free_result($result); 
 $list .= '</select>'; 
 // 
 return $list; 
} 

You can/should do the same thing with radio buttons, checkboxes, etc . . . makes for much easier to debug programs.

[togethercomms]

thanks all, have got it done, and the client is mildly happy... clients!, wish they understood how much effort goes into things like this