CODEWITHSUNDEEP
phpmysql
Arijanit Nitti Salihu
Arijanit Nitti Salihu

Reputation: 112

Search different columns by a separation of a comma

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

Upvotes: 0

Views: 159

Answers (3)

Don't Panic
Don't Panic

Reputation: 41810

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);

Upvotes: 1

Chris
Chris

Reputation: 76

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;

?>

Upvotes: 0

Anton
Anton

Reputation: 4052

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

Upvotes: 0

Related Questions