Skip to main content
P
Phox68
Participating Frequently
August 28, 2008
Question

complex query MySQL / CF

  • August 28, 2008
  • 4 replies
  • 831 views
I've created a MySQL database which is for a blog. I have five fields called keyword1, keyword2, etc. I want to write a query to populate a form list with "unique" keywords after comparing all five keyword fields. The reason I don't want to have just one field is to avoid using commas, or spaces in storage.

I'm not that comfortable with arrays in coding and think that's the only way to do this, but am not sure how to go about it.

My assumptions:

The query would look up all records in keyword1 and dump those results and I'd create a loop query to compare those to the words in keyword2 in a secondary query inside the first.

The brain melt is how I get the compared results to make a new master query list so I can then compare it to keyword3 field contents, and so on.

I presume it involves an array, but need help.

Thanks.
This topic has been closed for replies.

4 replies

D
Dan_Bracuk
Inspiring
August 28, 2008
You could modify your keywords table so that the primary key is both the blog_id and the keyword. Then you wouldn't have to worry about duplicate records.
N
Newsgroup_User
Inspiring
August 28, 2008
no, what Dan is saying is "I know you want to fix your car, but maybe
you should first learn how the cars are made and how to fix them"

google "date modelling" and "normalized database design" for starters.

telling you how to structure your tables a) will not teach you anything;
b) may end up being totally wrong, depending on a multitude of factors
in your applications business logic.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
P
Phox68Author
Participating Frequently
August 28, 2008
Okay Dan... perhaps instead of eluding your idea you could actually provide how you would set it up so that I can understand what a "normalized" database would be. Then once you provided what normalized is, then you could actually answer the inquiry instead of saying something like, "I know you want to fix your car, but perhaps you could buy a better car and the problem would go away."
K
Kronin555
Participating Frequently
August 28, 2008
First, no need for the attitude.

Second, did you try to learn what a normalized database was before firing back? If not, here's somewhere to get you started:
http://en.wikipedia.org/wiki/Database_normalization

Now, here's what I would do with my data model:
BlogEntry table
--------------------
BlogEntryID
Title
Content
Date

BlogEntryKeywords
--------------------------
BlogEntryID
Keyword

Then, all you need for the query you originally asked for is:
select distinct(keyword) from blogentrykeywords;
P
paross1
Participating Frequently
August 28, 2008
quote:
I've created a MySQL database which is for a blog
Having done that without knowing what "normalization" means tells me that you are probably in over your head to the extent that a single answer to your question would be of little use to you until you obtained a context for it to be meaningful. In fact, if you spent a little time researching what data modeling and normalization is first, your post would probably be unecessary. In other words, creating a database without really understanding how to create a proper relational database is going to be a nightmare for you, and this can't be "taught" in a couple of replies.

Phil
D
Dan_Bracuk
Inspiring
August 28, 2008
If it were my project, I'd have a more normalized database design so that I could have as many keywords as I wanted. That would make everything else a lot easier.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices