CODEWITHSUNDEEP
phpmysql
Neha Prakash
Neha Prakash

Reputation: 581

search a string in cell

I have saved different features seperated by commas in the database shown in this image below:enter image description here when I select a single 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

data is fetched from the database, but when I have many features it is failing to search. This is my query for many features selected

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 appreciated. Thank you

Upvotes: 3

Views: 48

Answers (3)

Nikesh Gowda
Nikesh Gowda

Reputation: 26

You can explode the $feature and store it into the array. Something like this.

if (!empty($features_search))
{
    $feature_array= explode(",", $features_search);
    for each($feature_array as $key => $feature)
    {
        $conditions[] = "features LIKE '%$feature%'";
    }
}

hope it helped.

Upvotes: 1

Sadia Naseeba
Sadia Naseeba

Reputation: 221

if your data is not static and it is being fetched from database, then try this

WHERE business_keyword LIKE '%3000%' AND (features LIKE '%feature%') .....

try this.

Upvotes: 1

PaulF
PaulF

Reputation: 6773

If you want to find a match of all of the features 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 a match on any of the features then use

WHERE business_keyword LIKE '%3000%' AND (features LIKE '%feature3%' OR features LIKE '%feature2%' OR features LIKE '%feature4%') .....

Upvotes: 2

Related Questions