是否有一个SQL函数循环遍历数组以查看是否有任何元素在MYSQL行中？

Question

I have a small SQL table. There is a 'tags' column that has several words separated by a comma. Using PHP & MYSQLI I would like to take a Search Value and compare it's individual words with the individual words in the tag SQL 'tags' column.

It is a relatively small database. I can think of a way to do this where I create a seperate column for every tag. But I would rather not. Only if that is the only option.

Example SQL layout, Table: Books

"title" -- "author" -- "tags"

[Potter]-- [J.K.] -- [Wizards, WandsnShit,Magic]

[50 shades]-- [James] -- [Boobies, Sex]

[Ulysses]-- [Joyce] -- [WTF]

So far my direction has been:

//obtains searchValue from HTML
$searchValue=$_GET["searchValue"];

//turns the values individual words into an array
$proxy = $searchValue;
$tags = explode(" ", $proxy);

//This is where I need help
SELECT * FROM books WHERE tags CONTAINS (cycle through 'tags' array)

If all works correctly, typing "Gandalf is a wizard" should return the book "Potter". Because "Wizard" is a tag of "Potter" book.

Also while I'm at it. Does the PHP function "explode" alter the original string or create a copy string an alter that?

Thanks in advance.

Answer 1

This is a bad design. Let's search on the net for database normalization.

In your book table should be a unique id (primary key, int, not null, auto increment) field. After that, you need to create a relation table, what has the tags.

For example:

Book table:

id
name
author

Tags table

id
book_id
tag

After that you can use:

$sql = "SELECT * FROM books"
    . " INNER JOIN tags ON tags.book_id = books.id"
    . " WHERE tags.tag = " . mysqli_real_escape_string($_GET["searchValue"]);

Or you can use LIKE keyword.

Note:

I am always wondering, why a lot of developer create 2 variable for nothing?

$searchValue=$_GET["searchValue"];
//turns the values individual words into an array
$proxy = $searchValue;
$tags = explode(" ", $proxy);

instead: $tags = explode(" ", $_GET["searchValue");