Search String

[Billybong007]

I'm trying to perform a search on an SQL table using the code below - but it fails to work.
I have a form when a user can search/filter using multiple variables i.e. locations, industry, job type.

If i do a simple select statement using a WHERE with only one variable it works fine and if i set and WHERE and AND statement then it will find everything match both - the problem is that it won't find anything if only 1 option is searched when i Use the WHERE and AND statement together.

To over come this I tried making an isset statement that if the variable is set then search for it, else ignore it ... but it doesn't work

$locations = $_POST['locations'];
$industry = $_POST['industry'];
$job_type = $_POST['job_type'];

$query = "SELECT *"
       . "\n FROM #__jobposts"
       . "\n WHERE type = '". $job_type."'"
    if(isset($locations)){  
       $query.="\n AND location = '".$locations."'";
           }
        . "\n ORDER by date desc"
            ;

Can anyone tell me how i can search for multiple variables, but still allowing a SEARCH ALL function

Many thank,

Billybong

 

[feherke]

Hi

$query = "SELECT *"
  . "\n FROM #__jobposts"
  . "\n WHERE type = '". $job_type."'"
  [red]. (isset($locations)?"\n AND location = '".$locations."':"")[/red]
  . "\n ORDER by date desc"
  ;

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Let us remove those quotes, I find them confusing. As my above quoting issue exemplifies...

$query = "SELECT *
  FROM #__jobposts
  WHERE type = '$job_type'
  " . ( isset($locations) ? "\n AND location = '$locations'" : "" ) . "
  ORDER by date desc";

Feherke.

http://rootshell.be/~feherke/

 

[jpadie]

i am not sure whether Feherke's refinement will work as you are implicitly instantiating $locations above in line 1.

also, assuming that the fields are not checkboxes, they will always exist in the POST superglobal as the browser will submit them as empty strings (unless you are using js to alter this behaviour). so you need to make your checking a little bit more pointed.

you also do not need/want "\n" in queries. you can format the query perfectly well just using code formatting.

try this code as an alternative:

<?
$options = array("locations", "industry", "job_type");
foreach ($options as $option){
	if (!empty($_POST['option'])){
		$query[] = "$option = '".mysql_escape_string(trim($_POST[$option]))."'";
	}
}
if (count($query) > 0){
	$where = " Where ". explode(" AND ", $query);
} else {
	$where = '';
}
$query = "
		SELECT 
			*
		FROM 
			TABLENAME
		$where
		ORDER by 
			date desc";
?>

 

[feherke]

Hi

You are right jpadie, I corrected only the obvious syntax error. Yep, [tt]isset()[/tt] is just not my style...

Feherke.

http://rootshell.be/~feherke/

 

[Billybong007]

cheers to you both -
jpadie's code seems to be working - but its still showing up all results rather than the searched - i'm going to play around with it for a while and see if I can see what I've done wrong - I've had to change the $option strings cuz I have already declared $option for another use, so it may only be my typo. I'll let you know how i get on.

 

[jpadie]

BillyBong

your problems almost certainly stem from the fact that i did not debug my code before posting it. sorry for any wasted time: you would have caught the error if you had error_reporting turned on. here is an updated code snip

$options = array("locations", "industry", "job_type");
$query = array();
foreach ($options as $option){
	if (!empty($_POST[$option])){  //changed to $option
		$query[] = "$option = '".mysql_escape_string(trim($_POST[$option]))."'";
	}
}
if (count($query) > 0){
	$where = " Where ". implode(" AND ", $query); //changed from explode
} else {
	$where = '';
}
$query = "
		SELECT 
			*
		FROM 
			TABLENAME
		$where
		ORDER by 
			date desc";
?>