Search for a string in a cell
I have kept the various functions separated by commas in the database shown in this image below: when I select one feature like this
SELECT rt.*, rb.*, rf.*, rs.*
FROM business rt
INNER JOIN business_company_info rb
ON rt.id=rb.business_id
INNER JOIN business_attributes rf
ON rt.id=rf.business_id
INNER JOIN business_basic_info rs
ON rt.id=rs.business_id
WHERE business_keyword LIKE '%3000%' AND features LIKE '%feature3%' and
status= 0 ORDER BY rt.id DESC
the data is fetched from the database, but when I have a lot of functionality the search fails. This is my request for many selected features
SELECT rt.*, rb.*, rf.*, rs.*
FROM business rt
INNER JOIN business_company_info rb
ON rt.id=rb.business_id
INNER JOIN business_attributes rf
ON rt.id=rf.business_id
INNER JOIN business_basic_info rs
ON rt.id=rs.business_id
WHERE business_keyword LIKE '%3000%' AND features LIKE'%feature3,feature2,feature4%' and
status= 0 ORDER BY rt.id DESC
This is my code.
if(isset($_POST['advance_search']) && !empty($_POST['advance_search']) && ((isset($_POST['area']) && !empty($_POST['area'])) ||
(isset($_POST['salary']) && !empty($_POST['salary'])) || (isset($_POST['industry']) && !empty($_POST['industry'])) || (isset($_POST['jobtype']) && !empty($_POST['jobtype']))
|| (isset($_POST['keyword']) && !empty($_POST['keyword'])) || (isset($_POST['empnum']) && !empty($_POST['empnum'])) || (isset($_POST['year']) && !empty($_POST['year']))
|| (isset($_POST['features']) && !empty($_POST['features'])) || (isset($_POST['employ_status']) && !empty($_POST['area']))
)){
//echo '<pre>';print_r($_POST);exit;
$area = $_POST['area'];
$salary = $_POST['salary'];
$industry = $_POST['industry'];
$jobtype = $_POST['jobtype'];
$keyword = $_POST['keyword'];
$empnum = $_POST['empnum'];
$year = $_POST['year'];
//echo '<pre>';print_r($ids);print_r($salary);print_r($keyword);print_r($jobtype);print_r($industry);exit;
//echo '<pre>';print_r($area);print_r($salary);print_r($industry);print_r($jobtype);print_r($keyword);print_r($empnum);print_r($year);exit;
/*if(isset($_POST['genre'])){
$genres_search = implode(',', $_POST['genre']);
}*/
if(isset($_POST['employ_status'])){
$employment_search = implode(',', $_POST['employ_status']);
}
if(isset($_POST['features'])){
$features_search = implode(',', $_POST['features']);
}
$conditions = array();
if (!empty($area)){
$conditions[] = "business_location LIKE '%$area%'";
}
if (!empty($salary)){
$conditions[] = "business_salary_rule LIKE '%$salary%'";
}
if (!empty($keyword)){
$conditions[] = "business_keyword LIKE '%$keyword%'";
}
if (!empty($jobtype)){
$conditions[] = "business_job_type >= '$jobtype'";
}
if (!empty($industry)){
$conditions[] = "business_industries LIKE '%$industry%'";
}
if (!empty($empnum)){
$conditions[] = "employees_number LIKE '%$empnum%'";
}
if (!empty($year)){
$conditions[] = "established_year LIKE '%$year%'";
}
if (!empty($employment_search)){
$conditions[] = "employment_status LIKE '%$employment_search%'";
}
if (!empty($features_search)){
$conditions[] = "features LIKE '%$features_search%'";
}
$sql = "SELECT rt.*, rb.*, rf.*, rs.*
FROM business rt
INNER JOIN business_company_info rb
ON rt.id=rb.business_id
INNER JOIN business_attributes rf
ON rt.id=rf.business_id
INNER JOIN business_basic_info rs
ON rt.id=rs.business_id
WHERE ". implode(' AND ', $conditions) . " and
status= 0 ORDER BY rt.id DESC"; echo '<pre>';print_r($sql);exit;
}
Any help would be greatly appreciated. Thanks you
+3
source to share
3 answers
If you want to match all functions, then the first part of the WHERE clause should be:
WHERE business_keyword LIKE '%3000%' AND (features LIKE '%feature3%' AND features LIKE '%feature2%' AND features LIKE '%feature4%') .....
if you want to match with any of the functions use
WHERE business_keyword LIKE '%3000%' AND (features LIKE '%feature3%' OR features LIKE '%feature2%' OR features LIKE '%feature4%') .....
+2
source to share