help with some SQL

[amarafzal]

Can some one help me iwth this select statment:

$query = "SELECT * FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$POST[region]' OR job_sector = '$POST[job_sector]'";

Right what i want to do is:
1. if user just chooses a keyword
2. user chooses key word and region
3. user chooses keyword and job_sector
4. user chooses office
5. user chooses region
6. user chooses office and region

at the moment the following from the above dont work:

1, 3, 6

how can i modify the above select statement so that all the points mentioned above work. Ive been trying to sort this out all day

[stolzyboy]

your gonna have to write seperate sql statements dependent on which items are selected, for example:

if ($_POST['keyword'] && !$_POST[job_title] && !$_POST[job_summary])
{
//build sql on just keyword
}
else if ((!$_POST['keyword'] && $_POST[job_title] && !$_POST[job_summary])
{
//build sql on just job_title
}
else if................

//do each combination possible, like 6

[amarafzal]

THANKS FOR THAT

i done the following but only some of them work, any reason why?

if ($POST['keyword'])
{
$query = "SELECT FROM $table_name WHERE job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%'";
}
else if ($POST['keyword'] && $POST['job_sector'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND job_sector = '$POST[job_sector]'";
}
else if ($POST['keyword'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$POST[region]'";
}
else if ($POST['keyword'] && $POST['job_sector'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$POST[region]' AND job_sector = '$POST[job_sector]'";
}
else if ($POST['job_sector'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE region = '$POST[region]' AND job_sector = '$POST[job_sector]'";
}
else if ($POST['job_sector'])
{
$query = "SELECT FROM $table_name WHERE job_sector = '$POST[job_sector]'";
}
else if ($POST['region'])
{
$query = "SELECT * FROM $table_name WHERE region = '$POST[region]'";
}

[stolzyboy]

have you figured out which ones do and do not work, that may narrow down your problem better

[amarafzal]

YES, only 2 work. This is weird do you know why this is happening. They all pull out results beside the last 2, but the results they pull out are wrong

if ($POST['keyword'])
{
$query = "SELECT FROM $table_name WHERE job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%'";
}
else if ($POST['keyword'] AND $POST['job_sector'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND job_sector = '$POST[job_sector]'";
}
else if ($POST['keyword'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$POST[region]'";
}
else if ($POST['keyword'] && $POST['job_sector'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$POST[region]' AND job_sector = '$POST[job_sector]'";
}
else if ($POST['job_sector'] && $POST['region'])
{
$query = "SELECT FROM $table_name WHERE region = '$POST[region]' AND job_sector = '$POST[job_sector]'";
}
else if ($POST['job_sector'])
{
$query = "SELECT FROM $table_name WHERE job_sector = '$POST[job_sector]'";
}
else if ($POST['region'])
{
$query = "SELECT * FROM $table_name WHERE region = '$POST[region]'";
}

[stolzyboy]

well for troubleshooting purposes, i would just do a simple check to make sure your if's are working the way you expect, then build the sql one by one like i advised before, so if instead of building the sql when only a keyword is chosen, simply do

echo "in the keyword only spot";

then for keyword and job region do

echo "in the keyword and job region spot";

this will ensure that your if's are working correctly

[amarafzal]

ill give it a try and let you know what i get tommorow

thanks again

[stolzyboy]

instead of

if ($_POST['keyword']) 
{ 
$query = "SELECT * FROM $table_name WHERE job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%'"; 
} 
else if ($_POST['keyword'] AND $_POST['job_sector']) 
{ 
$query = "SELECT * FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND job_sector = '$_POST[job_sector]'"; 
} 
else if ($_POST['keyword'] && $_POST['region']) 
{ 
$query = "SELECT * FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$_POST[region]'"; 
} 
else if ($_POST['keyword'] && $_POST['job_sector'] && $_POST['region']) 
{ 
$query = "SELECT * FROM $table_name WHERE (job_summary LIKE '%$keyword%' OR job_title LIKE '%$keyword%') AND region = '$_POST[region]' AND job_sector = '$_POST[job_sector]'"; 
} 
else if ($_POST['job_sector'] && $_POST['region']) 
{ 
$query = "SELECT * FROM $table_name WHERE region = '$_POST[region]' AND job_sector = '$_POST[job_sector]'"; 
} 
else if ($_POST['job_sector']) 
{ 
$query = "SELECT * FROM $table_name WHERE job_sector = '$_POST[job_sector]'"; 
} 
else if ($_POST['region']) 
{ 
$query = "SELECT * FROM $table_name WHERE region = '$_POST[region]'"; 
}

put

if ($_POST['keyword']) 
{ 
echo "this is keyword only selected"; 
} 
else if ($_POST['keyword'] AND $_POST['job_sector']) 
{ 
echo "this is keyword and job_sector";
} 
else if ($_POST['keyword'] && $_POST['region']) 
{ 
echo "this is keyword and region";
} 
else if ($_POST['keyword'] && $_POST['job_sector'] && $_POST['region']) 
{ 
echo "this is keyword, job_sector, and region";
} 
else if ($_POST['job_sector'] && $_POST['region']) 
{ 
echo "this is job_sector and region";
} 
else if ($_POST['job_sector']) 
{ 
echo "this is job_sector";
} 
else if ($_POST['region']) 
{ 
echo "this is region";
}

then when you are sure your if's are working when you choose different methods, start with one if and get the sql working, then go to next if and get the sql working for that one, and so on...

remember baby steps