0

I have a table that contains information such as names, email-addresses, numbers etc. My Database

Let's pretend that I have 30 contacts by the same name but they all live in different cities . How do I split the comma and replace it with and ....

Example SELECT * WHERE name %$searchString% OR city %$searchString%...

Now if $searchString contains comma

SELECT * WHERE name %$searchString% OR city %$searchString%... AND SELECT * WHERE name %$searchString2% OR city %$searchString2%...

The $searchString2 contains information that's separated with comma.

Update

I want to search each row over and over again as many times as commas exist. I'm sorry that I can't explain myself

Arijanit Nitti Salihu
  • 112
  • 2
  • 9

3 Answers3

1

This depends on whether you want to return rows where name or city match the search values exactly (=), or rows where any part of name or city match the search values (LIKE).

Regardless of which one you need, you can start out by converting your search string into an array of strings like this:

$strings = array_map('trim', explode(',', $searchString));

The array_map('trim'... ensures that you don't try to match any spaces before or after the commas in your comma-separated search string.

Here are examples for how to execute your query using prepared statements in PDO. First, full matches using IN:

$phs = rtrim(str_repeat('?,', count($strings)),',');
$stmt = $pdo->prepare("SELECT * FROM your_table WHERE name IN ($phs) OR city IN ($phs)");
// double the string values to us in both INs
$values = array_merge($strings, $strings);
$stmt->execute($values);

and partial matches using LIKE:

$sql = '';
foreach ($strings as $string) {
    $sql .= ' name LIKE ? OR city LIKE ? OR';
    $values[] = $string;
    $values[] = $string;
}
$stmt = $pdo->prepare('SELECT * FROM your_table WHERE' . rtrim($sql, ' OR'));
$stmt->execute($values);
Don't Panic
  • 41,125
  • 10
  • 61
  • 80
0

You need to use WHERE IN in SQL statement. 

SELECT * WHERE name LIKE '%$searchString%' AND city IN ('city1', 'city2', 'city3'...)

Here is the good discussion on how to do it in PHP: Passing an array to a query using a WHERE clause

Community
  • 1
  • 1
Anton
  • 3,587
  • 2
  • 12
  • 27
  • `%$searchString%` strings need to be quoted. same for `IN (city1, city2, city3...)` those are strings, not integers as in the link you left here. This, as per your original answer http://stackoverflow.com/revisions/35588188/1 – Funk Forty Niner Feb 23 '16 at 21:08
  • Correct. Thanks! Fixed. Also, it's likely AND, not OR there. – Anton Feb 23 '16 at 21:13
  • You're welcome. However, it's a 2-part question. OP: *"How do I split the comma ... Now if $searchString contains comma"* – Funk Forty Niner Feb 23 '16 at 21:15
0

Something like this?

You will need to escape/clean the value of searchString.

<?php

// $searchString = "Cardiff,London,New York";

$SQL = 'SELECT * FROM table WHERE ';

$searchStrings = explode(',',$searchString);

$SQLArray = array();

foreach($searchStrings as $searchString) {
    $SQLArray[] = "name LIKE '%$searchString%'";
    $SQLArray[] = "city LIKE '%$searchString%'";
}

$SQL .= implode(' OR ',$SQLArray);

// print $SQL;

?>
Chris
  • 76
  • 1