querie for array in array?

LiquidIceB

hi, i am writing this little article manager thing. I have this keywords thing where the article would be displayed with a thing on the side saying "Related articles are....." and show list of related articles to the article

keywords are stored in a database, separated by coma.. say "LAN, LCD, party"

every article would have this info...now how can i write a quierie to do this related article thing

SELECT article_type, article_cat_name, article_title, article_image FROM articles WHERE article_keywords IN ('" . $article_keywords . "') LIMIT 10

i have this, but the problem is it would see article_keywords as only "1 word" instead of list of stuff comparing to the array of keywords

the other thing i can do is have the array exploaded and do sumthing like

SELECT article_type, article_cat_name, article_title, article_image FROM articles WHERE article_keywords LIKE '%$article_keywords[0]%' OR article_keywords LIKE '%$article_keywords[1]%' LIMIT 10

and so on but say there like 6 keywords wouldnt the quierie be a bit slow???

just wondering if any of you know how can i do this with the least stress on the server...thank you

Danicek

If you will store your keywords in way you currently do it, you need to use second query.
Yes, if you have many rows in table and many keywords in one query, it will be slow (anyhow, if you plan to use it for small database, do not worry about it too much).

However if you can remake your db design, then much clearer way how to do this is to have separate table for articles and separate table for keywords like this:

table: articles
collumns: article_id, name, text...

table:keywords
collumns: id, article_id, keyword

With this design you will be able to do much better queries.

LiquidIceB

Danicek thank you for your reply....i really dont see a need for 2 tables..and i really dont see why i need to do 2 querys to do what i need to do

there would be about 5 or less words separaeted by coma..so its not much

what im really asking is how can i compare the "array" of these keywords to the "array" of the keywords in ohter articles to see if they are there or not...

Danicek

You aslo asked

and so on but say there like 6 keywords wouldnt the quierie be a bit slow???

so other than that, your second query should work, just add right number of arguments in or clause.

And why are you talking about two queries? If you mean that if you will have to do two queries if you have two tables, then I so NO, you have to do only one query and possibly faster and more efficient one.

Ok, you can stick with one table, if you are sure that your db will be always small and will have just few tables. Otherwise with your approach db could be soon very difficult to understand.

LiquidIceB

oo sorry, by you saing two i ment you was talking about having 2 quiries for gettng info out...but you ment second query my bad LOL

i dont know if i should change teh design of the tables....it looks pretty straight forward to me ( see image )

the keywords would only be about 6 or so words, separated by coma...

Danicek

Of course your design is straightforward. You can keep it, if you want.

However your design breaks basic rules of relational databases. This rules are not law for me, however clever people made them and therefor it is ussually good to follow them. If your db will develop quicker than you expect and will grow for several years, you will realize why they set such rules.

In relational databases we talk about "Normal Forms" (basic rules that every relational db should follow) - there are three basic normal forms (1NF, 2NF, 3NF) and several more.
Your design breaks first of all this rules given in first normal form (1NF):

Reduce entities to first normal form by removing repeating or multivalued attributes to another, child entities.

This mean that your design is not normalized, not even in basic first form of this normalization.

End of preaching, do what you want :].

LiquidIceB

ok then... so say now i have 2 tables one that has all the stuff + keywords... and one thats just keywords

i would do sumthing like this then right?

$sql = "SELECT article_keywords FROM article_keywords
WHERE article_id = '10' LIMIT 10";
$result = mysql_query($sql, $sqlid) OR exit("u broke it");
$row = mysql_fetch_assoc($result);

//explode keywords array into single keywords	
$keys = explode(",", $row['article_keywords']);

$sql = "SELECT article_type, article_cat_name, article_title, article_image FROM articles
WHERE article_keywords LIKE '%$keys[0]%' ";

for (i = 1; i <= count($keys-1); i++)
	$sql .= "OR article_keywords LIKE '%$keys[$i]%' ";


$sql .= "LIMIT 10"

even if i redesign the table i dont see how this is gonna speed up the whole query deal when searching for articles with these keywords...

Danicek

No. Say you have article with id 10 and you are searching for all articles that have at least one same keyword. And of course you want this article just ones in your result (also in case it is same in several keywords) - this is (I hope) what related articles are. You want name and id of related articles. Your query will look like this:

SELECT DISTINCT d.article_name, d.article_id
FROM article_keywords b, article_keywords c, articles d
WHERE b.article_id = 10
AND b.article_keywords = c.article_keywords
AND c.article_id = d.article_id

I hope you understand, that you will have only one keyword in each row in table article_keywords, something like this

article_id, keyword_id, keyword
1, 1, "bye"
1, 2, "ciao"
1, 3, "good"
2, 4, "a"
2, 5, "b"

This means that article with id 1 has 3 keywords and aritcle with id 2 has 2 keywords...

LiquidIceB

i see....but i dont see why you have

b.article_keywords = c.article_keywords

if B and C are the same exact table

otherwise thanks alot for your help, helping me figure this out 🙂

Danicek

This is standart selfreferencing.

I can describe it like this:

You say to your server via this query - give me all rows from table b for certain article, then give me every article that has same keyword from (table c) and each give just once (distinct). Then give me name for each of these articles (table d).
Basicaly: if you are asking for two different results from same table in one query, you must use this table as several different tables (with aliases).
Simply it is not possible to do this task in one query without selfreferencihg. And also I could add, that selfreferencing is standart approach in SQL.

LiquidIceB

wow ok thanks alot for you smart and informational answers

will try it when i get back from school, will post back what with results 🙂

LiquidIceB

ok after doing alot of testing and stuff, i came to the conclusion that your way takes more time to make a query then mine

where your method took 0.15 seconds, mine was comming up with 0.0014

maybe ill be wrong in the long run when there would be alot more rows then now, but i think ill stay with my way

i would like to thank you alot for you time and patience helping me figure this out, thanks