Solved

PHP Search facility

Posted on 2009-04-01
10
268 Views
Last Modified: 2012-05-06
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,000 - £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.jobSalaryTo 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
0
Comment
Question by:djfenom
10 Comments
 
LVL 3

Expert Comment

by:pkoops
ID: 24037148
I don't know if the "," might be a problem.
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.
0
 
LVL 14

Expert Comment

by:shobinsun
ID: 24037173
Hello,

Might be because of :

change after this line " list($from, $to) = split('-', $salary); " :

$from = ''.$from.'';
$to = ''.$to.'';

And try again.

Regards.
0
 

Author Comment

by:djfenom
ID: 24037206
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.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24037223
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.jobSalaryTo 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.
0
 

Author Comment

by:djfenom
ID: 24037289
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 24037362
In that case, perhaps your quesy is selecting on exclusive conditions. For example a query to pick people called "Mary" might return some records, but one to pick someone called "Mary" and sex='Male' will return no records. I have restructured the query as follows

$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"] . "%' AND
                          mind_client_jobs.jobSalaryTo BETWEEN '$from' AND '$to'
                    ORDER BY jobDate DESC";

take all the WHERE conditions out and try adding them back one at a time and see if the query starts returning no records. With this

$query_RSvac = "SELECT mind_client_jobs.*, mind_client.* FROM mind_client_jobs
                    LEFT JOIN mind_client ON mind_client_jobs.jobClient = mind_client.clientID
                    ORDER BY jobDate DESC";


then the BETWEEN stuff

$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_jobs.jobSalaryTo BETWEEN '$from' AND '$to'
                    ORDER BY jobDate DESC";


add another condition...

$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.jobSalaryTo BETWEEN '$from' AND '$to'
                    ORDER BY jobDate DESC";

and so forth.
0
 
LVL 6

Expert Comment

by:twocandles
ID: 24037380
did you try to issue an "echo" of the actual query to see what's the exact string?
0
 

Author Comment

by:djfenom
ID: 24037453
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.jobSalaryTo BETWEEN '30000' AND '34000' ORDER BY jobDate DESC
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24037475
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?
0
 

Author Comment

by:djfenom
ID: 24037716
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
$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";

							 }

						  }

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now