逗号分隔值和类似php / mysql的问题 [英] Comma Seperated Values and LIKE php/mysql Troubles
问题描述
设置
这或多或少是我先前发布的有关逗号分隔值(爆炸,爆破)的后续问题。由于我是菜鸟,最近几天这种情况一直困扰着我-对冗长的帖子表示抱歉。
The Set up This is more or less a follow up question to something I had previously posted regarding comma separated values (explode,implode). Here's the scenario which has been stomping me the last few days as I'm a noob--sorry for the lengthy post.
我通过url(index.php?id = variable)传递变量,然后我使用
I'm passing a variable via the url (index.php?id=variable), I then check the database to find the rows containing that variable using
SELECT * FROM table WHERE column LIKE '%$variable%'
我使用通配符是因为结果是逗号分隔的值,并且变量在数据库中多次出现。
I'm using the wildcards because the results are a comma separated value with the variable appearing multiple times in the database.
因此,如果我们要分配-假设学校为热门电视节目。.我的数据库已建立,以便用户可以为电视节目分配一所以上的学校。 IE。
So if we were assigning-- say schools to popular tv shows..my database is set up so that the user can assign more than one school to the tv show. IE.
South Park-> fsu , nyu , mit
South Park--> fsu, nyu ,mit
Archer-> 哈佛, nyu
Archer --> harvard, nyu
索引.php?id = nyu将显示Sourth Park&弓箭手。
Index.php?id=nyu would display Sourth Park & Archer.
问题
因为我使用的是就像'%variable%'
如果我具有以下条件:
South Park-> uark
Archer-> ua
If I have the following: South Park-->uark Archer-->ua
index.php?= ua
Southpark也会显示Archer,而不仅仅是Archer。
index.php?=ua Instead of just Archer showing, Southpark would also show.
由于通配符,这是有道理的...但是,谁能想到一种方法来实现我想要的结果呢?。是否有任何方法可以实现更多目标使用逗号分隔的值来获得准确的结果吗?。我完全踩了,将不胜感激。
Which makes sense due to the wildcards...but can anyone think of a way to do this achieving the results I want?..Is there any way to achieve more precise results using a comma separated value?..I'm completely stomped and will appreciate any help.
推荐答案
一个更好的选择是创建另外两个表。
一个表是您已经拥有的 tv_shows表,第二个表是 tags表,第三个表是连接表。
A better option is to create two other tables. One table is the one you already have 'tv_shows' and the second table is the 'tags' table and the third table is the connection table.
这里是一个示例:
CREATE TABLE tags(id INT, tag VARCHAR(200));
CREATE TABLE tv_tags(show_id INT, tag_id INT);
SELECT FROM tv_shows,tv_tags WHERE tv_shows.id=tv_tags.show_id AND tag_id IN (SELECT id FROM tags WHERE tag LIKE 'tag');
这篇关于逗号分隔值和类似php / mysql的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!