Coding made simple.....

furious5

Ok I have been working on a script to return paged recordsets on and off for over 2 weeks and am pretty much there and I am just trying to link this to a search page.

Currently I have hardcoded all the variables, but am looking for a way in which to write some compact code to finish the script off.

The basic search page will have 3 variables, with a more advanced search page to be added later with up to 20 variables.

At present the 3 variables are - title, location, sector. Either field can be searched and there will be no required.

Initially someone had suggested coding the SQL like this:

if($title!= "") 
$sql = "SELECT jobid, date, title, description, location, industry, salary FROM job WHERE title='".$title."'";

if($sector!= "") 
$sql_where .= " AND industry = '$sector'"; 

if($location!= "")
$sql_where .= " AND location = '$location'";

But this makes the assumption that $title is a required field and cannot be null for the query to succesfully execute.

The only other way I see around this is by doing 3 individual queries all using the SELECT statement and then 3 further queries using the SELECT statement depending on which fields are full.

Can anyone suggest a more compact way to code this?

DeltaRho2K

if($title){
$sql = "SELECT jobid, date, title, description, location, industry, salary FROM job WHERE title='".$title."'";
}
if($sector){
$sql_where .= " AND industry = '$sector'"; 
}
if($location){
$sql_where .= " AND location = '$location'"; 
}

Something about this doesn't look quuute right to me, but I can't give it my full attention right now... I will look later when I have more time, but for now, here is something to go on (maybe)

acidbox

I didn't test this, and if you know how to use $HTTP_POST_VARS you could probably turn it into a for loop. However, this might work for what you are trying to do:

<?php
$sql = "SELECT jobid, date, title, description, location, industry, salary FROM job WHERE ";
$sql_where = "";

if($title!= "") {
		if($sql_where != "") {
				$sql_where = $sql_where . "AND ";
			}
		$sql_where = $sql_where . "title = '" . $title . "' ";
	}

if($sector!= "") {
		if($sql_where != "") {
				$sql_where = $sql_where . "AND ";
			}
		$sql_where = $sql_where . "industry = '" . $sector . "' ";
	}

if($location!= "") {
	if($sql_where != "") {
				$sql_where = $sql_where . "AND ";
			}
		$sql_where = $sql_where . "location = '" . $location . "' ";
	}
?>

Lemme know what happends =)

furious5

I've just tested the code and it seems to work fine.

This itself will save loads of coding - I will integrate it in to the full code this evening and let you know if i have any further problems.