2

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'];
Improve this question
4
  • looks like you want a LIKE IN / CONTAINS statement, e.g. name LIKE IN ('%santandar%', '%HSBC%', '%RBS%') . Not possible in mysql AFAIK. Commented Jun 11, 2016 at 14:35
  • Show us some sample records that contain the data you think should match. Commented Jun 11, 2016 at 14:36
  • are you creating $res code level. if so can you try in something like .. name in (".$res.")"; Commented Jun 11, 2016 at 14:38
  • @Iftikhar, the php variables $res and $area are both PHP POSTS. i just used the strings to show you guys what they will look like once they are posted. Commented Jun 11, 2016 at 14:40

2 Answers 2

Reset to default
2

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]) ";
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

hmm... but that is only good if we know the strings. the strings are PHP posts... So, they could be different on each post.
I have update the answer with a suggestion .. if you recive the string by post you explode the string in an array and build the query dinamcally
Your second edit is only good if you know how many strings we gonna get in the post. as I said, we never know what we get in the post. it could be 1 or it could be multiple (no set amount/number).
I have update then answer with the suggestion for manage the string you receive and typos correct
This won't work, because in the OP you can see there's an extra comma at the end, PLUS there's space(s) around the commas, so you'll be searching for " ", as well as "HSBC ".
|
1

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%')
Improve this answer

5 Comments

Thanks.. what is $names and where does it come from?
also, what are $values, $field_name and where are they coming from?
See the "Usage" example. The build_like_or is a function that you can pass in the arguments. The function accepts two arguments: $values (in your case, the $res string), and $field_name (in your case, the $name string). This allows it to be called as often as you need, with as many different field(s) or field name(s) as you need.
For more information on your qustions (about $names and where it comes from, etc), please see this article: tutorialspoint.com/php/php_functions.htm
@Jackson - I have tested it, it works perfectly, exactly as is. I apologize that you don't understand how functions work, but this code is exactly correct, and does exactly what you want. anyone who writes PHP functions would in fact understand where $values and $field_name come from. Did you read the article I linked to? I don't have the space in this answer to provide a full tutorial on how functions work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.