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
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;
$ for tags after OR, or is that how it's supposed to be?
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.$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.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.
