Assigning multiple keywords to PHP/MySQL keyword based search

Question

I have a PHP keyword search script that searches a MySQL database and then displays the results. Currently, the script finds results based on a single keyword attached to a field in a MySQL database.

My question is; how can I attach multiple keywords to one field and return results if one or more of those keywords is matched in the users query?

Can I define the keywords in my database and separate them with commas? e.g. this, would, be, in, the, field

<?php

mysql_connect("localhost","username","password");
mysql_select_db("database");

if(!empty($_GET['q'])){
$query=mysql_real_escape_string(trim($_GET['q']));
$searchSQL="SELECT * FROM questions WHERE `keywords` LIKE '%{$query}%'  LIMIT 1";
$searchResult=mysql_query($searchSQL);

while($row=mysql_fetch_assoc($searchResult)){
$results[]="{$row['result']}";
}

echo implode($results);
}

?>

Marc B · Accepted Answer · 2011-10-21 16:56:12Z

2

Either use a fulltext index, or split the keyword list into a compound "where like ... or like ... or like ..." statement:

$keywords = explode(' ', $_GET['q']);
$likes = array()
foreach($keywords as $kw) {
   $kw = mysql_real_escape_string($kw);
   $likes[] = "keywords LIKE '%{$kw}%'";
}

$sql = "SELECT ... WHERE " . implode(' OR ', $likes);

Storing compound data in a single field in a relational database is rarely a good idea, especially if you're going to need to access sub-portions of the whole field. Keywords should be stored in a separate table, one keyword per record. You can then use a link table to relate multiple keywords to each individual record in your parent table.

answered Oct 21, 2011 at 16:56

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dejan Marjanović Over a year ago

@Callum Whyte, this is a full code example. You should really use fulltext search if possible since %% is not indexed.

Collectives™ on Stack Overflow

Assigning multiple keywords to PHP/MySQL keyword based search

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related