I have a field in database in which i have stored comma seperated keywords like: abc, def, ghi, jkl
Then I am doing a query like: "SELECT * FROM tablename WHERE keywords_field LIKE %def%";
I am doing a simple query like that but its not returning any result. How to do ? I have used * also instead of % but that is also giving any results. Is it due to the comma seperated keywords in database ?
How to fix ?
Just checking the obvious are you putting %def% in quotes. Without them it is an invalid sql syntax.
SELECT * FROM tablename WHERE keywords_field LIKE '%def%'
i urge you to redesign it
that's correct, it isn't returning any result because it's returning an error message instead
Why redesign it ? And yes you are correct it was returning an error.
plays swami with Rudy
Because if multiple entries have the same keywords, it's less redundant to store them in a separate table and join the two by id's...
because it violates first normal form
fix it, and your queries will be (1) simpler, and (2) faster
are those reasons sufficient?
sorry, swami be wrong
i would use the actual keywords, ~not~ ids
besides, the "redundancy" is not eliminated by using ids, that's a huge myth -- there would be just as many redundant id values as keyword values
We've had this conversation before - unless the keywords were shorter than 3 characters, id's are faster
comma seperated values do not necessarily violate 1NF, but should still be avoided. My google-fu is strong.
yes, and you lost the argument the last time, too
using ids requires a join
i don't care how fast your ids are when doing joins, nothing beats not doing the join at all
yes, they do, and yes they should
So many-to-many... you're suggesting he put... what? You dont want him to do a join, you dont want him to put CSV.... what was the actual suggestion? I'm lost.
"i'll explain it so even you can understand it" -- moe howard
(nothing personal intended, i just thought it was an apt quote)
PK ( prodcode )
PK ( prodcode , keyword )
will that suffice or would you like to see sample data?
this design ~is~ actually many-to-many, by the way
- the keywords table may or may not exist, depending on whether you want a controlled vocabulary or freeform
Oh okay, so it IS joining. You're just saying avoid the join table. gotcha.
productkeywords is the join table, the table between products and keywords (if the keywords table exists)
it's a separate table in order to avoid the comma-separated list in the products table
naturally, you ~do~ have to join back to the products table to find the product data
but you ~don't~ have to join to the keywords table, as would be required by the keyword ids you were suggesting in post #5
If the Keywords table exists (which would be the case if you wanted to limit keyword choices... otherwise the keywords table is redundant and irrelevant), you have to join to the keywords table....
How do you not join to it?
so far, so good
you don't join to it by not joining to it
i'm not sure what you aren't getting
list products along with their keywords for selected products --
, GROUP_CONCAT(pk.keywords) AS keywords
FROM product AS p
JOIN productkeywords AS pk
ON pk.prodcode = p.prodcode
WHERE p.descr LIKE '%pants%'
find products which have a certain keyword --
FROM productkeywords AS pk
JOIN product AS p
ON p.prodcode = pk.prodcode
WHERE pk.keyword = 'sandal'
see? no keywords table is required in the queries, even though it exists for referential integrity of keywords
methinks you've been using ids too long
an interesting point... though you've inflated your database by storing "sandal" over and over and over instead of 1 over and over...
so? your point is?
let me ask you this, when you create a persons table, do you extract all the first names and store them in a separate table, replacing them in the persons table with an id, just to avoid storing "john" and "mary" over and over instead of 42 and 63 over and over?
didn't think so