How to search multiple columns in a MySQL query?

Question

How to search multiple column, this code search single column , please customise my code

$keywords= "search keywords";
$keywords= htmlspecialchars($keywords);    
$keywords= mysqli_real_escape_string($con, $keywords);
$countSql_s = "SELECT *, MATCH(website) AGAINST ('%".$keywords."%') AS score FROM search WHERE MATCH(website) AGAINST ('%".$keywords."%') ORDER BY score DESC";

score 0 · Answer 1 · answered Dec 01 '16 at 03:30

0

Just change your "WHERE" to include a check for each column, like this:

... WHERE MATCH(website) AGAINST('%".$keywords."%') > 5
AND MATCH(username) AGAINST('%".$keywords."%') > 5
...

answered Dec 01 '16 at 03:30

Jeremy Gurr

1,613
8
11

score 0 · Answer 2 · answered Dec 01 '16 at 03:41

0

You need to alter your table as:

ALTER TABLE --table name-- ADD FULLTEXT fulltext_search (column1,colum2,column3);

Then you can search like this:

MATCH (column1,column2) AGAINST ('%".$keywords."%')

answered Dec 01 '16 at 03:41

Vikas Umrao

2,800
1
15
23

i have small doubt what is meant by fulltext_search @ Jeremy Gurr – Dec 01 '16 at 04:07
FULLTEXT is index for search.To use full text you need to define he columns as FULLTEXT – Vikas Umrao Dec 01 '16 at 04:11
i have another doubt, how to change my db ENGINE=InnoDB into ENGINE=MyISAM – Dec 01 '16 at 04:24
r u using phpmyadmin? – Vikas Umrao Dec 01 '16 at 04:51
http://stackoverflow.com/questions/4240977/changing-table-engine-in-mysql – Vikas Umrao Dec 01 '16 at 04:54

How to search multiple columns in a MySQL query?

2 Answers2