complex query MySQL / CF

[Phox68]

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.

[Dan_Bracuk]

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.

[Phox68]

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."

[Kronin555]

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;

[paross1]

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

[Phox68]

I didn't give an attitude, I responded with what tone I felt Dan's post gave me. I didn't even ask about how to create my database or anything remotely close to that. I asked specifically how to create a query in CF to accomplish what I wanted. So, as for my attitude, I don't believe I expressed anything other than my feelings about how I got a completely off topic answer to how to write a query. Perhaps if he answered with something like "I'd set up my database like this and then use this query" it would have avoided the perception that I'm an idiot and need to understand my abc's.

It's been a while since I did elaborate queries and I had to dig deeper in my archives of code to figure out how to set up a more optimal way to achieve my results.

At present, unless someone on here can spell out why it isn't going to work, I have removed all 5 fields from my database and set up a one-to-many relational database wherein I simply use the ID # from the main database and allow it to enter multiple records using the same key field. So I have a BLOG master database and a related BLOG_KEYWORDS database which records the blog ID and the keyword... and that's it.

I then populate my select form field from the BLOG_KEYWORDS database using a GROUP BY command to make the master list of usable keywords only show up once in the list.

I think this will achieve the goal. Apart from this method, I know I could use commas or some other separators in the 1 field of the main database, but then I've have no real way to find the unique keywords to make my select field populate alphabetically.

I apologize for any misunderstanding in my original post. I do work with databases quite often, but it's been several months since I've had to create new ones and I had a major brain vapor on the one-to-many concept. My bad.

[Newsgroup_User]

>
> I think this will achieve the goal.

And you have now normalized your database design and it sounds like you
have discovered the benefits of doing so.

> Apart from this method, I know I could
> use commas or some other separators in the 1 field of the main database, but
> then I've have no real way to find the unique keywords to make my select field
> populate alphabetically.

Which is why that would be even less normalized then your original
design and even more difficult to work with. Requiring a great deal of
string manipulation for just the simplest of data tasks and outright
eliminating the ability to do other tasks.

[Dan_Bracuk]

quote:

---

Originally posted by: Phox68
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."

---

I've heard good things about the book, Database Design for Mere Mortals.

[Newsgroup_User]

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/

[Dan_Bracuk]

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.