CODEWITHSUNDEEP
phpmysqlsql
SmootQ
SmootQ

Reputation: 2122

Using like and % with the name of column in MySQL

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

Upvotes: 1

Views: 260

Answers (1)

juergen d
juergen d

Reputation: 204924

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

Upvotes: 4

Related Questions