 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / August 2008complex query MySQL / CF
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. 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. 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." 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/ 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 [Q]I've created a MySQL database which is for a blog[/Q]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 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 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. [q][i]Originally posted by: [b][b]Phox68[/b][/b][/i] 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."[/q] I've heard good things about the book, Database Design for Mere Mortals. 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.