djfenom
asked on
PHP Search facility
I've setup a simple search facility to search for jobs. I have dropdowns for Job, Location, Type and Salary.
The salary dropdown is where I'm having issues, i have it as follows:
<select name="jobSalary" id="jobSalary">
<option value="" selected >Select...</option>
<option value="10,000" >Up to £10,000</option>
<option value="10,000-11,000" >£10,000 - £11,000</option>
<option value="12,000-13,000" >£12,000 - £13,000</option>
<option value="14,000-15,000" >£14,000 - £15,000</option>
<option value="16,000-17,000" >£16,000 - £17,000</option>
<option value="18,000-19,000" >£18,000 - £19,000</option>
<option value="20,000-21,000" >£20,000 - £21,000</option>
<option value="22,000-23,000" >£22,000 - £23,000</option>
<option value="24,000-25,000" >£24,000 - £25,000</option>
<option value="25,000-30,000" >£25,000 - £30,000</option>
<option value="30,000-34,000" selected="selected">£30,00 0 - £34,000</option>
<option value="35,000-39,000" >£35,000 - £39,000</option>
<option value="40,000-44,000" >£40,000 - £44,000</option>
<option value="45,000" >£45,000 +</option>
</select>
Whatever I select no jobs are displayed, my sql is as follows, I first split the jobSalary value into 2 so I can search between values:
$salary = $_POST["jobSalary"];
list($from, $to) = split('-', $salary);
mysql_select_db($database_ mindset, $mindset);
$query_RSvac = "SELECT mind_client_jobs.*, mind_client.*\n"
. "FROM mind_client_jobs LEFT JOIN mind_client ON mind_client_jobs.jobClient = mind_client.clientID WHERE mind_client.clientActive = 'Y' AND mind_client_jobs.jobActive = 'Y' AND mind_client_jobs.jobSector LIKE '%" . $_POST["jobSector"] . "%' AND mind_client_jobs.jobCounty LIKE '%" . $_POST["jobLocation"] . "%' AND mind_client_jobs.jobType LIKE '%" . $_POST["jobType"] . "%' AND mind_client_jobs.jobSalary To BETWEEN '$from' AND '$to' ORDER BY jobDate DESC";
$RSvac = mysql_query($query_RSvac, $mindset) or die(mysql_error());
$row_RSvac = mysql_fetch_assoc($RSvac);
$totalRows_RSvac = mysql_num_rows($RSvac);
I think it must be a problem with the salary as once I take this out of the sql query it works using the other fields.
Thanks
Chris
The salary dropdown is where I'm having issues, i have it as follows:
<select name="jobSalary" id="jobSalary">
<option value="" selected >Select...</option>
<option value="10,000" >Up to £10,000</option>
<option value="10,000-11,000" >£10,000 - £11,000</option>
<option value="12,000-13,000" >£12,000 - £13,000</option>
<option value="14,000-15,000" >£14,000 - £15,000</option>
<option value="16,000-17,000" >£16,000 - £17,000</option>
<option value="18,000-19,000" >£18,000 - £19,000</option>
<option value="20,000-21,000" >£20,000 - £21,000</option>
<option value="22,000-23,000" >£22,000 - £23,000</option>
<option value="24,000-25,000" >£24,000 - £25,000</option>
<option value="25,000-30,000" >£25,000 - £30,000</option>
<option value="30,000-34,000" selected="selected">£30,00
<option value="35,000-39,000" >£35,000 - £39,000</option>
<option value="40,000-44,000" >£40,000 - £44,000</option>
<option value="45,000" >£45,000 +</option>
</select>
Whatever I select no jobs are displayed, my sql is as follows, I first split the jobSalary value into 2 so I can search between values:
$salary = $_POST["jobSalary"];
list($from, $to) = split('-', $salary);
mysql_select_db($database_
$query_RSvac = "SELECT mind_client_jobs.*, mind_client.*\n"
. "FROM mind_client_jobs LEFT JOIN mind_client ON mind_client_jobs.jobClient
$RSvac = mysql_query($query_RSvac, $mindset) or die(mysql_error());
$row_RSvac = mysql_fetch_assoc($RSvac);
$totalRows_RSvac = mysql_num_rows($RSvac);
I think it must be a problem with the salary as once I take this out of the sql query it works using the other fields.
Thanks
Chris
Hello,
Might be because of :
change after this line " list($from, $to) = split('-', $salary); " :
$from = ''.$from.'';
$to = ''.$to.'';
And try again.
Regards.
Might be because of :
change after this line " list($from, $to) = split('-', $salary); " :
$from = ''.$from.'';
$to = ''.$to.'';
And try again.
Regards.
ASKER
Thanks guys, I've tried your suggestion shobinsun but it makes no difference.
I can actually write $from and $to to the page no problem, it just won't work in the SQL.
I can actually write $from and $to to the page no problem, it just won't work in the SQL.
Two things to check/try:
1. You are using $_POST, make sure your form has method='post' in it
2. Take the commas out of the value clauses like so. Rather than
<option value="20,000-21,000" >£20,000 - £21,000</option>
try
<option value="20000-21000" >£20,000 - £21,000</option>
because the query will look like mind_client_jobs.jobSalary To BETWEEN '30,000' AND '34,000' and MySQL does not store commas in integer or float fields. I am assuming that these fields are numeric, but that is where I would start.
1. You are using $_POST, make sure your form has method='post' in it
2. Take the commas out of the value clauses like so. Rather than
<option value="20,000-21,000" >£20,000 - £21,000</option>
try
<option value="20000-21000" >£20,000 - £21,000</option>
because the query will look like mind_client_jobs.jobSalary
ASKER
Hi bportlock, I've checked and it's definitely using method="post".
I've also taken out the comma in the values, but still the same? My field is set to INT in my database.
I've also taken out the comma in the values, but still the same? My field is set to INT in my database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
did you try to issue an "echo" of the actual query to see what's the exact string?
ASKER
I've had a play with changing the WHERE conditions and as soon as I take out the salary one, it works fine:
$query_RSvac = "SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs
LEFT JOIN mind_client ON mind_client_jobs.jobClient = mind_client.clientID
WHERE mind_client.clientActive = 'Y' AND
mind_client_jobs.jobActive = 'Y' AND
mind_client_jobs.jobSector LIKE '%" . $_POST["jobSector"] . "%' AND
mind_client_jobs.jobCounty LIKE '%" . $_POST["jobLocation"] . "%' AND
mind_client_jobs.jobType LIKE '%" . $_POST["jobType"] . "%'
ORDER BY jobDate DESC";
I've done an echo of the query and done a search just on the salary and this is what I got:
SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs LEFT JOIN mind_client ON mind_client_jobs.jobClient = mind_client.clientID WHERE mind_client.clientActive = 'Y' AND mind_client_jobs.jobActive = 'Y' AND mind_client_jobs.jobSector LIKE '%%' AND mind_client_jobs.jobCounty LIKE '%%' AND mind_client_jobs.jobType LIKE '%%' AND mind_client_jobs.jobSalary To BETWEEN '30000' AND '34000' ORDER BY jobDate DESC
$query_RSvac = "SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs
LEFT JOIN mind_client ON mind_client_jobs.jobClient
WHERE mind_client.clientActive = 'Y' AND
mind_client_jobs.jobActive
mind_client_jobs.jobSector
mind_client_jobs.jobCounty
mind_client_jobs.jobType LIKE '%" . $_POST["jobType"] . "%'
ORDER BY jobDate DESC";
I've done an echo of the query and done a search just on the salary and this is what I got:
SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs LEFT JOIN mind_client ON mind_client_jobs.jobClient
Have a look in your MySQL table. How many records are in there with a salary between 30000 and 34000? Can you dump some of those records and post them here?
ASKER
I've had a play and fixed it, I basically had to make sure that a salary was selected before the query was ran, here is my working version.
Thanks for your help
Thanks for your help
$query_RSvac = "SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs
LEFT JOIN mind_client ON mind_client_jobs.jobClient = mind_client.clientID
WHERE mind_client.clientActive = 'Y' AND
mind_client_jobs.jobActive = 'Y' AND
mind_client_jobs.jobSector LIKE '%" . $_POST["jobSector"] . "%' AND
mind_client_jobs.jobCounty LIKE '%" . $_POST["jobLocation"] . "%' AND
mind_client_jobs.jobType LIKE '%" . $_POST["jobType"] . "%'";
if ($_POST["jobSalary"] == "") {
$query_RSvac = $query_RSvac . " ORDER BY jobDate DESC";
}
else {
if ($from == "10000" && $to == "") {
$query_RSvac = $query_RSvac . "
AND mind_client_jobs.jobSalaryTo <= '$from' ORDER BY jobDate DESC";
}
else if ($from == "45000" && $to == "") {
$query_RSvac = $query_RSvac . "
AND mind_client_jobs.jobSalaryTo >= '$from' ORDER BY jobDate DESC";
}
else {
$query_RSvac = $query_RSvac . "
AND mind_client_jobs.jobSalaryTo BETWEEN '$from' AND '$to' ORDER BY jobDate DESC";
}
}
Try using 30000-34000 and split this.
You maybe need to alter your database then, but this might work better, if you add the "," later on, as you are showing the values.