sql Like query doesn't work

[eluser]max123[/eluser]
I have a function to filter records from the database. I have used drop down boxes and a text box to do that. This is my function

function search_vacancies($data)
{
$key_word=$data['key_word'];
$category=$data['category'];
$city_state=$data['city_state'];

$sql = " SELECT tt_vac_vacancies.s_vac_designation,tt_rec_recruiters.s_rec_company_name,city_states.city_name,countries.country_name, tt_vac_vacancies.end_date, tt_vac_vacancies.i_vac_ID FROM tt_vac_vacancies
LEFT JOIN tt_rec_recruiters ON tt_vac_vacancies.l_vac_account_ID = tt_rec_recruiters.l_rec_ID
LEFT JOIN city_states ON tt_vac_vacancies.city_id = city_states.city_id
LEFT JOIN countries ON city_states.country_id = countries.country_id WHERE tt_rec_recruiters.s_rec_country = 1";

if($category!=0)
$sql = $sql. " AND tt_vac_vacancies.l_vac_job_category = ". $category;
if($key_word!='')
$sql = $sql. " AND tt_vac_vacancies.s_vac_designation LIKE '". $key_word."'";
if($city_state!=0)
$sql = $sql. " AND tt_vac_vacancies.city_id = ". $city_state;

$query=$this->db->query($sql);

return $query; // if using table class
}

Here I'm searching by category,keyword and city_state. Category and city_state parts are working. But the 'Like' query for keyword doesn't work. What might be the reason

[eluser]stommert[/eluser]
Hi,

If you use a join of any kind (inner, left, right) and you have a delimiter on the table you are joining and there is no record for the joined field, there will be no results. witch is quite logick, if you consider that you can't have a delimiter on a non existing field.

I would debug this by removing the joins and take it one join at the time.
I hope this helps to solve your problem