0

Is it possible to check a sql to see if any records contain any of the words in a string?

For example

<?
$tags = "rock indie pop britpop alternative";


$result1 = mysql_query("SELECT * FROM `Stations` WHERE `tags` LIKE '%$tags%'" );


$num_rows1 = mysql_num_rows($result1);


echo $num_rows1;

?>

would return 1 result.

At the moment, because the string has multiple words it isn't returning anything.

Jamie

Jamie Warren
  • 123
  • 1
  • 9

3 Answers3

1

You can do something like this:

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

$tags = implode("%' OR `tags` LIKE '%", $tags);

which should give you something to the effect of:

SELECT * FROM `Stations` WHERE `tags` LIKE '%rock%' OR `tags` LIKE '%indie%' OR `tags` LIKE '%pop%' OR `tags` LIKE '%britpop%' OR `tags` LIKE '%alternative%'

So:

<?
    $tags = "rock indie pop britpop alternative";

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

    $tags = implode("%' OR `tags` LIKE '%", $tags);

    $result1 = mysql_query("SELECT * FROM `Stations` WHERE `tags` LIKE '%$tags%'" );

    $num_rows1 = mysql_num_rows($result1);

    echo $num_rows1;
Samsquanch
  • 8,866
  • 12
  • 50
  • 89
  • I think you may have missed a `$` for `tags` after `OR`, or is that how it's supposed to be? – Funk Forty Niner May 13 '14 at 23:44
  • 1
    The `tags` after the `OR` is referring to the column in the DB. I did, however, notice that I had left a reference to `pieces` in the final answer thanks to Sublime. I've fixed that now. – Samsquanch May 13 '14 at 23:46
  • Ah ok. I just had a bit of trouble following. – Funk Forty Niner May 13 '14 at 23:47
  • Hi, What if in the database the tags are stored as 'rock,indie,pop, britpop,alternative' Is there still a way to do this? – Jamie Warren May 14 '14 at 21:54
  • If it's just commas you can use `$tags = explode(',', $tags);` instead, if it could be just a comma or a comma and a space, you can use `explode(',', str_replace(' ', '', $tags))` which would strip the spaces and then `explode()` on the commas. – Samsquanch May 14 '14 at 22:24
0

The easy, but bad answer is:

foreach(explode(' ', $tags) as $tag) {
  // SELECT * FROM Stations WHERE tags LIKE '%$tag%'
}

or construct something to generate a query like:

SELECT *
FROM Stations
WHERE tags LIKE '%$tag1%'
  OR tags LIKE '%$tag2%'
  OR tags LIKE '%$tag3%'
...

But the solution in which the tables are properly normalized and are not likely to bring your servers to their knees trying to do wildcard matches on strings goes something like this:

TABLE stations
  st_id UNSIGNED INTEGER NOT NULL AUTO_INCREMENT,
  st_name VARCHAR(255),
  ...
  PRIMARY KEY(st_id),
  INDEX(st_name)

TABLE tags
  tag_id UNSIGNED INTEGER NOT NULL AUTO_INCREMENT,
  tag_name VARCHAR(64) NOT NULL,
  PRIMARY KEY(tag_id),
  UNIQUE(tag_name)

TABLE station_tags
  st_id UNSIGNED INTEGER,
  tag_id UNSIGNED INTEGER,
  PRIMARY KEY(st_id, tag_id),
  INDEX(tag_id),
  FOREIGN KEY fk_st_id (st_id) REFERENCES stations(st_id) ON DELETE CASCADE,
  FOREIGN KEY fk_tag_id (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE

This lets you define a tag once, assign unlimited tags to unlimited stations, manage tags centrally, use proper indexes, etc, etc.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
0

I Know it is Old Question but i the best way to do it is to use REGEXP it's much faster than LIKE

Reem Aziz
  • 1,425
  • 2
  • 17
  • 22