CODEWITHSUNDEEP
phpmysqlsql
localhost
localhost

Reputation: 1082

Finding a list of comma separated keywords

When I run my code I cannot retrieve the entry that I am targeting because the keywords that are being run through the query are not the same as the keywords in the database.

Keywords:

$keywords = 'spaghetti,macaroni,hamburger';

Query:

mysql_query("SELECT * FROM ---- WHERE keywords LIKE '%$keywords%' ORDER BY id DESC LIMIT 1");

I am targeting an entry with these keywords:

food, restaurant, hamburger, spaghetti, taco,pie

I may be missing something simple here since I've been working for 12 hours straight. However, I've had no luck with anything I've tried to do to work around this issue.

From my point of view the query should be returning the target entry since there are 2 matching keywords.

What should I be doing differently?

Upvotes: 1

Views: 1081

Answers (6)

Gavin Towey
Gavin Towey

Reputation: 3200

Storing multiple values as a CSV inside a column is a common SQL anti-pattern. It's hard to use and even harder to optimize:

How to fix the Comma Separated List of Doom

Upvotes: 2

maček
maček

Reputation: 77778

You need to break up the keywords and do a separate LIKE comparison for each one

SELECT * FROM mytable
WHERE keywords LIKE '%spaghetti%' OR keywords LIKE '%macaroni%'
-- ...

You could do something like this

<?php

$keywords = 'spaghetti,macaroni,hamburger';

$query = "SELECT * FROM mytable WHERE 1=1";

$keywords = explode(',', $keywords);

if (count($keywords)) {
    $query .= sprintf(
        " AND (%s)",
        implode(' OR ', array_map(function($word){
            return "keywords LIKE '%{$word}%'";
        }, $keywords))
    );
}

echo $query;

// SELECT * FROM mytable WHERE 1=1 AND (keywords LIKE '%spaghetti%' OR keywords LIKE '%macaroni%' OR keywords LIKE '%hamburger%')

As you can see from the comments, there are better ways to handle this, but I'm assuming that this rudimentary question is looking for a simple answer.

I'm not doing any input sanitization, or adding any other fail safes. Building queries like this is not very reliable and you should continue to learn about PHP/MySQL to avoid common pitfalls in the future. However, I believe more advanced approaches are a bit out of your reach at the moment. If someone else would like to provide better techniques that they think the OP can grasp, by all means, go for it :)

Upvotes: 2

Majid Fouladpour
Majid Fouladpour

Reputation: 30252

$like = array();
$keywords = explode(',' , $keywords);
foreach($keywords as $keyword) {
  $like[] = "`keywords` LIKE '%$keyword%'";
}
$like = implode(' OR ', $like);
$query = "SELECT * FROM `table` WHERE $like ORDER BY id DESC LIMIT 1";

Upvotes: 2

NSjonas
NSjonas

Reputation: 12032

Try this, it may be faster than using multiple or clauses

Select * from mytable
where keywords REGEXP 'spaghetti|macaroni|hamburger'

Upvotes: 1

user1440875
user1440875

Reputation: 

mysql_query("SELECT * FROM ---- WHERE keywords LIKE '%". implode("%' OR keywords LIKE '%", explode(",", $keywords)) ."%' ORDER BY id DESC LIMIT 1");

Upvotes: 1

Marcio Mazzucato
Marcio Mazzucato

Reputation: 9295

You have to use LIKE with separeted works, you can try this:

$keywords = 'spaghetti,macaroni,hamburger';

$arrayWords = explode(',', $keywords);

$like = '';

foreach($arrayWords as $word)
    $like .= empty($like) ? " keywords LIKE '%$word%' " : " OR keywords LIKE '%$word%' ";

mysql_query("SELECT * FROM ---- WHERE $like ORDER BY id DESC LIMIT 1");

Upvotes: 1

Related Questions