Not every field has to be completed

[wazo]

Hi guys this is my search query

At the moment every field must be completed to get a result, how can i edit this query so that not every field has to be completed

$query ="select * from jobs where 
category='$category'  AND 
location='$location' AND
salarytype='$salarytype' AND 
salary='$salary' AND 
jobstatus='$jobstatus'

 ORDER BY '$order'"; 

Many thanks in advance

[MrTufty]

Directly? You can't, not easily.

You could change it to OR instead of AND, but I doubt that will give you the results you're looking for.

Instead, the easiest way would be to write some PHP to inspect the values of those variables, and if they're set, add in the SQL code to filter on that field.

BTW, I hope those fields aren't coming directly from a form.... baaaad mojo.

[wazo]

where else to search variables come from apart from a form

[Dan Grossman]

$query = "SELECT * FROM jobs WHERE 1=1 ";
if (!empty($category)) {
  $query .= "AND category='$category' ";
}
if (!empty($location)) {
  $query .= "AND location='$location' ";
} 
if (!empty($salarytype)) {
  $query .= "AND salarytype='$salartytype' ";
}
if (!empty($salary)) {
  $query .= "AND salary='$salary' ";
}
if (!empty($jobstatus)) {
  $query .= "AND jobstatus='$jobstatus' ";
} 

where else to search variables come from apart from a form

If these variables are assigned *directly* from $_GET or $_POST, then your query is vulnerable to SQL injection. Someone could write SQL into the input to alter the query you execute. Escape user input with mysql_real_escape_string() before using it in a query.

[wazo]

Thanks will look into it

One more thing

salary='$salary'

What would be the code to return all rows with greater than the value?

[Dan Grossman]

Thanks will look into it

One more thing


What would be the code to return all rows with greater than the value?

salary > $salary

[wazo]

if (!empty($salary)) {
  $query .= "AND salary > $salary ";
} 

does that look right

I tried it but it returned no results

[spikeZ]

Looks ok but quote your variable

if (!empty($salary)) { 
  $query .= "AND salary > '$salary' "; 
} 

echo the final $query variable to see if it looks right.

[Dan Grossman]

Numeric columns should NOT be quoted.

[wazo]

Thank you guys

Do you have any tutorials regarding mysql_real_escape_string()

[spikeZ]

Numeric columns should NOT be quoted.

I know but can it be guaranteed that the salary will be numeric....?
eg 12,500 or 12.500.

Just being cautious

[Dan Grossman]

Thank you guys

Do you have any tutorials regarding mysql_real_escape_string()

The PHP manual is extensive, easy to read, and filled with useful comments from other programmers. You'll find it at http://php.net. There's a search box in the upper right; type mysql_real_escape_string in there and you'll find what it's for, how to use it, and lots of other info.

I know but can it be guaranteed that the salary will be numeric....?
eg 12,500 or 12.500.

Just being cautious

A job for the application, not the database. Input should be parsed before it's placed in a query

[spikeZ]

A job for the application, not the database. Input should be parsed before it's placed in a query

agreed