Querying multiple columns at the same time in MySQL

I’m trying to convert a script I found online to search data from numerous columns rather than one. The problem with my code is that it will only grab data from one column. It will search and focus on all columns as I type but if I use two words they HAVE to be in the same column to display.

This is the original query in the script.

WHERE webslesson_post_title LIKE "%'.str_replace(' ', '%', $_POST['query']).'%"

This was my adjustment.

WHERE (Name LIKE '%".str_replace(' ', '%', $_POST['query'])."%' OR Address LIKE '%".str_replace(' ', '%', $_POST['query'])."%' OR City LIKE '%".str_replace(' ', '%', $_POST['query'])."%' OR State LIKE '%".str_replace(' ', '%', $_POST['query'])."%' OR ZIP LIKE '%".str_replace(' ', '%', $_POST['query'])."%')

The script started with searching one column of data. I needed it to search a name, address, city, state, zip columns. I want to be able to search a street and a zip and see all results. Or a name and city. Just any combination.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can form a concatenated string from multiple columns and compare it to your search pattern.

WHERE CONCAT_WS(' ', Name, Address, City, State, Zip) LIKE ?

This shows using a parameter placeholder. This is recommended, because it avoids all that complicated string-concatenation, and it is more secure.

Run it in PHP like this:

$sql = "SELECT ...full query... ";
$search = "%" . str_replace(' ', '%', $_POST['query']) . "%";
if (($stmt = $pdo->prepare($sql)) !== false) {
  if ($stmt->execute( [ $search ] ) !== false) {
    $result = $stmt->fetchAll();
  } else {
    error_log(print_r($stmt->errorInfo(), true));
  }
} else {
  error_log(print_r($pdo->errorInfo(), true));
}

Method 2

An alternative to searching through individual columns manually is to create a full text index on the desired columns. The query then becomes much simpler.

For example, to search through columns a and b create a table will full text index over desired columns:

CREATE TABLE `test` (
  `a` text,
  `b` text,
  FULLTEXT KEY `a` (`a`,`b`)
)

And the query would look like:

select * from test where match(a,b) against ('ddd');

https://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html#function_match


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x