Using like and % with the name of column in MySQL

Question

I have 3 tables: Quote, hashtag and user.

As follows:

Table quote

Quote                 | user    |  Hashtags
hello world #greet    |  1      |  ,1,3
this is a #quote#this |  2      |  ,2,4

Table Hashtag

id   | hashtag
1    | greet
2    | quote
3    | hello
4    | this

Table user

id | username
1  | john
2  | michael

What I want to do, is to write an SQL query, using INNER JOIN, to fetch quotes (with username for every quote) using a given hashtag.

For example : I have the hashtag : "#greet" and I want to get the quotes under this hashtag.. I have to do something like this:

SELECT
     quo.quote,
     usr.username,
     hash.hashtag
FROM
     quote as quo
INNER JOIN
     user as usr
INNER JOIN
     hashtag as hash
ON
     usr.id=quo.user
AND
     hash.id LIKE '%,quo.hashtags,%'
AND 
     hash.id = :givenHashtag

As you can see in the SQL example, I want to check if the hashtag id, is contained in the quote hashtags varchar. using LIKE '%,x,%'

The question is that here, I don't know how to use the %..% with the name of the column quo.hashtag.

Note that I'm working based on MySQL/PHP

@jinCao , I have many quotes with hashtags each, once I search for the hashtag, I want to fetch quotes and users... like twitter do. Example of result : [hello world #greet | john | #greet] when I look for the quotes under the hashtag [greet]. — SmootQ
– SmootQ, Commented Nov 9, 2013 at 12:47
@SmootQ - How to use FIND_IN_SET()? Read the docs that I linked to in my comment — Mark Baker
– Mark Baker, Commented Nov 9, 2013 at 12:48
Precisely what I meant - the MySQL developers found so many people weren't normalising their database tables, and created the FIND_IN_SET() function for this very purpose — Mark Baker
– Mark Baker, Commented Nov 9, 2013 at 13:04

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

Never, never, never store multiple values in one column.

Like you see now this will only give you headaches. Normalize your DB. Create a new table that relates hashtags and quotes.

quote_tags
-----------
quote_id
hashtag_id

Then you can remove the hashtags column from the quote table

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 9, 2013 at 12:41

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SmootQ Over a year ago

I've got this idea, but It seems that I have to redo many functions and codes in the project :(... yeah, you're write, when I did this... I did it to limit the number of Joins.

SmootQ Over a year ago

I will use FIND_IN_SET() for now, and I'll normalize the tables once I have time later... +1

Collectives™ on Stack Overflow

Using like and % with the name of column in MySQL

1 Answer 1

Never, never, never store multiple values in one column.

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Never, never, never store multiple values in one column.

2 Comments

Related