如何使用php选择mysqli进行标签搜索？

Question

How to select mysqli for tags search using php ?

I have table : test like this

 ___________________________________________
|_id_|_______name_______|________tag________|
| 1  |        dog       |  animal,pet,dog   |
| 2  |       donuts     |    food,donuts    |
| 3  |       BMW        |         car       |
| 4  |     football     |       sport       |
| 5  |     ferrari      |    car,supercar   |

and this is mysqli php code for select

$query = "SELECT * FROM test WHERE tag = '$_GET[tag]' order by id desc";

Then i test my code by this url

www.example.com/search_tag.php?tag=car

But it will show only row id 3 , how to select for show row id 3 and 5 ?

Answer 1

As @nogad suggested on the comment section, fix your database structure. I would suggest to separate a table for storing the tags for each name.

First, let's create a table for storing different tags (tags_tb):

tags_id | tags_desc
--------+----------
   1    |   animal
   2    |    pet
   3    |    dog
   4    |   food
   5    |   donuts
   6    |    car
/*** LIST GOES ON ***/

Then, create another table for storing the subjects (subject_tb):

subj_id | subj_desc
--------+----------
   1    |    dog
   2    |   donuts
   3    |    BMW
/*** LIST GOES ON ***/

Then, create a table for storing the related tags for each subject (selected_tags_tb):

 id | subj_id | tags_id
----+---------+---------
  1 |    1    |    1
  2 |    1    |    2
  3 |    1    |    3
  4 |    2    |    4
  5 |    2    |    5
  6 |    3    |    6

So, when a user selects a tag (for example, the user selects the tag - animal, all subjects that relates to that tag will be shown), a simple query will do it:

SELECT b.*
    FROM selected_tags_tb a
    LEFT JOIN subject_tb b ON a.subj_id = b.subj_id
    WHERE a.tags_id = ?

This approach will make your system dynamic.