How to search comma separated words in different columns

Adem · June 23, 2023, 8:37am

Hello,

I can’t add too many search options since the lookup field is a single row and half a field at the bottom of the listing table.
So is it possible to do something like the following?

Example,
I have a table called phones
I searched “xxx_feature” in the search field and phones of multiple brands were listed, is it possible to list xxx-featured and samsung-branded phones when I see the brand name by separating “xxx_feature, samsung” with a comma?

Note: If there are comma-separated words, it means that different columns will be searched. Not a standard column

Sorry for not explaining clearly

$search = $_POST['search']:
$query = "SELECT * FROM tbl WHERE column1 LIKE ? OR column2 LIKE ? OR column3 LIKE ? OR column4 LIKE ?";
$params = array("%$search%", "%$search%, "%$search%, "%$search%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

pisumathu · June 28, 2023, 7:46am

I don’t understand the exact issue you are facing.
Something wrong with quotations on your code:

$params = array("%$search%", "%$search%, "%$search%, "%$search%");

Correct it like this:

$params = array("%$search%", "%$search%", "%$search%", "%$search%");

web1928 · June 28, 2023, 4:24pm

Are you asking whether you can apply search terms based on two values in a single input field, with the values seperate by a comma e.g. searching for “128gb, samsung” ?

If that is what you are asking, I’d simply explode the input value into two individual values and then move to search against those.

$searchArray = explode(',',  $_POST['search']);
$query = 'SELECT field1, field2, field3 FROM tbl WHERE column1 LIKE ? OR column2 LIKE ?';
$params = array("%$searchArray[0]%", "%$searchArray[1]%");

(try to avoid using the * selector from databases)

Not sure if this is what you mean.

/D

Adem · July 7, 2023, 11:43am

Yes, that’s exactly what I want to say

Example: Brand column separately, GB column separately, another property column separately.
Example: I searched for 128, it listed all 128 GB phones in the database. No problem so far
When I separate it with a comma and add the brand “128, samsung” I want it to list all 128GB samsung phones

phdr · July 7, 2023, 2:00pm

You would dynamically build the WHERE part of the query -

// note: the following will work for one or more comma separated values in the search input

// fake a search input
$search = '128, samsung';

// explode and trim the search input
$parts = array_map('trim',explode(',',$search));

// define a single search term
$term = "(column1 LIKE ? OR column2 LIKE ? OR column3 LIKE ? OR column4 LIKE ?)";

// arrays to hold the search terms and the prepared query input parameters
$terms = [];
$params = [];

// dynamically build the search terms and input parameters
foreach($parts as $part)
{
	$terms[] = $term;
	$params = array_merge($params,["%$part%", "%$part%", "%$part%", "%$part%"]);
}

// build the where part of the query
$where = 'WHERE ' . implode(' AND ',$terms);

// examine the values
echo $where;
echo '<pre>';
print_r($params);

You should be using a $_GET input for the search, so that if someone finds a result they want to return to or share with someone, they can bookmark the URL or share the URL and be able to return to the same search results. You cannot do this when using a $_POST input.

Strider64 · July 7, 2023, 3:52pm

code can even be improve slightly by using array_fill instead of array_merge.

// explode and trim the search input
$parts = array_map('trim', explode(',', $search));

// define a single search term
$term = "(column1 LIKE ? OR column2 LIKE ? OR column3 LIKE ? OR column4 LIKE ?)";

// arrays to hold the search terms and the prepared query input parameters
$terms = [];
$params = [];

// dynamically build the search terms and input parameters
foreach($parts as $part) {
    $terms[] = $term;
    array_push($params, ...array_fill(0, 4, "%$part%"));
}

// build the where part of the query
$where = 'WHERE ' . implode(' AND ', $terms);

// examine the values
echo $where;
echo '<pre>';
print_r($params);

Adem · July 8, 2023, 9:16am

Thank you very much
This idea taught me a lot.

Adem · July 18, 2023, 11:01am

This search system is a full professional search system, I did not apply it to my script. Thank you very much
if the search words contain double quotes, it removes the percent characters and searches for full results.
Example: word1,“word2”, word3
['%word1%','word2','%word3%']

I want to get your opinion on something
There is a product, there are numbers of inputs and outputs.
but you have separate column for input, separate column for output
The input/output expression for this product is expressed as 10X32
If “10”,“32” is searched, it finds exact results, but when the expression habit is 10x32 or 5x12 or 17x24, what should be added to the above code to find the product?

$pattern = '/^[0-9]+X[?0-9]/i';
if(preg_match($pattern, $search)){

}