-1

I'm sending json via dataloader like this:

{"person":{"name":[{"tag":"peter"},{"tag":"frank"},{"tag":"jeff"}]}}

I have the below query and as of right now I filter by exact values. But how do I search 'like' for the values this example?

php:

...

$arr = json_decode($data,true);

$name = $arr['person']['name'];
foreach($name as $t=>$tag){
    $name[] = $tag['tag'];
}

$query = "SELECT name, id 
          FROM accounts 
          WHERE visible != '1'";
if ($name) {
    $query.=" AND name IN ('".implode("', '", $name)."')"; 
} 
    $query.=" ORDER BY name DESC";

...
Philipp M
  • 3,306
  • 5
  • 36
  • 90
  • I'm afraid you cannot combine `LIKE` with `IN` - at least not that I know. You'd have to build a `LIKE 'xy' OR LIKE 'z' OR...` query – Jeff Nov 15 '17 at 21:54
  • maybe even fill text matching depeding on your needs: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html –  Nov 15 '17 at 21:56
  • what is the foreach for? right now you are just doubling the entries!? You are sure you wanna add the `$name` inside the loop? – Jeff Nov 15 '17 at 21:58
  • Sorry I should have mentioned ... I send the data via json and dataloader ... I edited above. – Philipp M Nov 16 '17 at 07:43

3 Answers3

0

Adding an OR and the REGEXP function will allow both exact and like.

$query.=" AND name IN ('".implode(', ', $name)."') OR name REGEXP ('".implode('|', $name)."')";
JoeCrash
  • 532
  • 2
  • 9
  • Sorry I should have mentioned ... I send the data via json and dataloader ... I edited above. Does your example still fit? – Philipp M Nov 16 '17 at 07:41
  • yes, the example still fits. Its only altering what's going on in your query string. This just grabs your name array and splits it 2 times, once for the = search, and again for the like regexp – JoeCrash Nov 16 '17 at 20:35
0

Theoretically and not recommended since it will not be very performant, you could use OR (not tested):

$name = $arr['person']['name'];

if ($name) {
  $arr = [];
  foreach($name as $t=>$tag){
    $n = $tag['tag'];
    $arr[] = “(name LIKE ‘%$n%’)”;
  }
  $query .= “ AND (“ . implode(“ OR “, $arr) . “)”;
}

But I think you should re-structure your data to either use JSON column and then you could search the data (e.g. take a look at this SO question).

Or the other option (which complies more to a normalized db structure) is to put the names in their own table, join the table to the accounts table and search do a simple LIKE search.

dev7
  • 6,259
  • 6
  • 32
  • 65
0

Loop your name array to create a new array with LIKE operator and implode it with OR operator in your query.

$data = '{"person":{"name":[{"tag":"peter"},{"tag":"frank"},{"tag":"jeff"}]}}';

$arr = json_decode($data,true);

$name = $arr['person']['name'];
$search_term =array();
// loop it to add like operator
foreach($name as $t=>$tag){
    $search_term[] = "name LIKE '%".$tag['tag']."%'";
}

$query = "SELECT name, id FROM accounts WHERE visible != '1'";
if (!empty($search_term)) {
    // implode you like with or operator   
    $query.=" AND ".implode( ' OR ' , $search_term); 
} 
$query.=" ORDER BY name DESC";

echo $query;

SELECT name, id FROM accounts WHERE visible != '1' AND name LIKE '%peter%' OR name LIKE '%frank%' OR name LIKE '%jeff%' ORDER BY name DESC
Ravinder Reddy
  • 3,869
  • 1
  • 13
  • 22