CODEWITHSUNDEEP
phpmysql
Jackson
Jackson

Reputation: 820

PHP: search comma separated string in mysql?

I have a mysql query which simply looks into mysql to find LIKE strings and displays the result.

Within the same mysql query, I have 2 LIKE.

1 is always a single string and the other one can be single and sometimes multiple strings separated by commas.

when I use my code, I get no results at all even though I have all the fields in the mysql database and I also have all the search strings in the columns.

This is my code:

$area = 'London';
$res = 'santandar, HSBC, RBS, ';
$sql = "SELECT * FROM banks WHERE location LIKE '%$area%' AND name LIKE '%$res'";

I also tried it with preg_match and it didn't return anything:

$sql = "SELECT * FROM banks WHERE location LIKE '%$area%' AND name LIKE '".preg_match($res)."'";

If I remove the second LIKE and my code looks like below, it works just fine:

sql = "SELECT * FROM banks WHERE location LIKE '%$area%'";

So the issue starts when I try to search using a comma separated string.

Could someone please advise on this issue?

EDIT:

The PHP varibles are POSTS so they can be anything in each post.

they are like so:

$area = $_POST['area'];
$res = $_POST['res'];

Upvotes: 2

Views: 1973

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

you should use an OR condition:

 $res_array  = explode(',' $res)

 $num_elem= count($res_array)  // with this value you can build dinamically the query 

"SELECT * FROM banks WHERE location LIKE '%$area%' 
 AND ( name LIKE concat('%', $res_array[0]), 
     OR  LIKE concat('%', $res_array[1]) 
     OR  LIKE concat('%', $res_array[2]) ";

Upvotes: 2

random_user_name
random_user_name

Reputation: 26160

You are going to need to blow this out into separate LIKEs with an OR, such as:

...WHERE location LIKE '%{$area}' AND (name LIKE '%{$name1}%' OR name LIKE '%{$name2}' OR ...)

You could write this fairly simply with some PHP logic:

function build_like_or( $values, $field_name ) {
    // Create an array from the comma-separated values
    $names = explode( ',', $values );
    // Trim all the elements to remove whitespaces
    $names = array_map( 'trim', $names );
    // Remove empty elements
    $names = array_filter( $names );
    $where = array();
    // Loop over each, placing the "LIKE" clause into an array
    foreach( (array)$names AS $name ) {
        $where[] = "{$field_name} LIKE '%{$name}%'";
    }

    // Glue up the LIKE clauses.
    $where = '(' . implode(' OR ', $where) . ')';
    // Results will be something like:
    // $where = "(name LIKE '%santadar%' OR name LIKE '%HSBC%')"
    return $where;
}

Usage:

$area = 'London';
$res = 'santandar, HSBC, RBS, ';
$name_where = build_like_or( $res, 'name');
$sql = "SELECT * FROM banks WHERE location LIKE '%$area%' AND {$name_where}";
// echo $sql outputs "SELECT * FROM banks WHERE location LIKE 'London' AND (name LIKE '%santadar%' OR name LIKE '%HSBC%' OR name LIKE '%RBS%')

Upvotes: 1

Related Questions