Thanks again for a wonderful detailed response. The part that was still bothering me was the multiple-same-category fields. I get locked into this thinking and well, it's difficult to let go.
Then I tried something on a table that had MemID, Doc1, Doc2, Doc3 I had put Doc1 in a few hours earlier. I went back to the form, not an update but thought a regular insertion on Doc2 would work, but mysql spit back a duplicate record error. Then I started thinking, well even if I had an update form, would I be able to just enter Doc2, and it retained Doc1 from the first insertion. Then the lightbulb went off in my head and it all made sense to me. Everything you said and all the examples. Plus I realized I was getting into a horrible habit early on. So to confirm before proceeding. I have a form where I allow 10 categories of "certifications". Now to correct it I'm thinking of creating a "certifications" table. Then if ID 15 wants to enter 10 certifications it would look like the following in the certifications table (one column to search from): ID Certification 15 C# 15 DCOM 15 MCS 15 Cisco 15 Red Hat 15 MySQL 15 Oracle 9 I think this is right. Please let me know what you think. Stuart --- Rhino <[EMAIL PROTECTED]> wrote: > This note is a followup to the thread entitled > "Dynamic Queries" which was initiated yesterday by > Stuart Felenstein. Stuart, I am deliberately > replying to your private followup question on the > list rather than privately because this thread could > potentially benefit other MySQL users who might be > having the same question today or some time in the > future. Having this discussion on the list means > that others can benefit from it; if we have this > discussion privately, you and I are the only ones > that benefit. Also, having the discussion on the > list is a good sanity check: if I say something that > is unclear or even flat-out wrong, there is a chance > that others reading this thread will see it and > correct us both. Otherwise, I could inadvertenly > mislead you and cause you all kinds of grief. > > First things first, Stuart. On re-reading my reply > to your question, I came across some typos that > could cause confusion so let me take the liberty of > repeating my reply, including the relevant bits of > your questions, with my amendments in square > brackets: > > ------------------------------ > > Not sure what to call what I'm attempting to do, > > decided on dynamic queries. I should mention that > I've > > been working with databases for just a little over > a > > month. > > > > Example could be many web sites, but let's say > Expedia > > (the travel site..booking flights, cars, etc) > > My understaning is that the SQL statements are > > contained in a recordset, but variables would be > > passed back into the sql statement based on the > > particulars of the user's input? Is that correct, > is > > that the only way ? > > > I've been working with relational databases, mostly > DB2, since 1985 and I'm > not entirely sure of the precise meaning of the term > "recordset". I've never > seen a formal definition of that term. However, I'm > pretty sure that a > "recordset" is not the query that you pass to the > database but the result > that you get back. It's always been my practice to > refer to the result of > the query as a result set, not a recordset. I'm not > trying to tell you not > to use the term recordset - it might be widely used > by some groups of people > for all I know - I'm just trying to explain where > I'm coming from. > > As to your specific question, yes, SQL statements > can be written to contain > variables. For example, if you were searching on > Expedia, you could have a > query that tells the database to return hotel names > and addresses given a > specific city and date, which the user supplies at > execution time. The > result set will then contain all of the rows that > represent the prices of > hotel rooms in that city on that date. > > In this case, the query would typically contain > placeholders for the > variables and the query would be prepared (compiled) > without knowing the > values of those placeholders. In other words, when > the statement was > prepared, the computer wouldn't know what city or > date the user wanted. For > example, the query might look like this: > > String query = "select hotel_name, hotel_address, > hotel_cost_per_night from > hotels where city = ? and date = ?"; > > That's how I would do it in Java. The question marks > are the placeholders > representing the variables for city name and date. > > It would also be possible to do it via string > concatenation, although this > can be bit problematic when the variables contain > quotes or other special > characters. For example: > > String query = "select hotel_name, hotel_address, > hotel_cost_per_night from > hotels where city = " + city_name + " and date = " + > stay_date; > > You can also combine the two approaches. For > example, > > String query = "select hotel_name, hotel_address, > cost_per_night from " + > table_name + " where city = ? and date = ?"; > > Naturally, in all of these approaches, the values of > the variables have to > be determined at execution time. There are a variety > of ways that this could > be accomplished but the most common is for the user > to > supply all variable values in text fields on a form > before pressing the "go" > button that launches the query. Then the firm [the > preceding word should be 'form', not 'firm'] > (should) [I should have omitted the round brackets > around 'should'. I was trying to say that, in my > view, input values should be validated. However, I > have seen plenty of applications that don't do this. > In my view, these applications are badly written but > maybe I'm just being anal.] validate the data and > return error messages if it finds any of the input > variable values to be > inappropriate. For example, if the city name was > left blank or if the date > supplied was "fred", the query probably should > [there should be a 'not' right here between 'should' > and 'even'] even be attempted since you > know it will produce an unrealistic or empty result. > Instead, your program > should tell the user what problems were found and > let them modify the > variable values and try again. When you get > valid-looking values, you can > then attempt to execute the statement with those > values replacing the > placeholders in the query. > > There are other approaches to getting the values of > the variables but they > are not usually appropriate or would be much less > attractive to the user. > For example, the program could ask for the value of > each variable *after* > the user has pressed the "go" button but I think > most users would prefer to > give the values first, then press the "go" button. > > > Further along, when someone wants to save a search > , > > say like on Monster, is that done via a view or a > > "personal" recordset they can call too when > needed? > > > > I'm reading around and trying to determine the > best > > way. > > > What you're describing could be done and sometimes > is. For example, an > initial search might turn up all of the hotels in a > city on a given date > that had rooms available regardless of the specific > location within the city > or the price of the room. A user might very well > want to do that to get an > initial sense of the range and variety of rooms > available. However, the user > might want to refine their search after getting that > overview to get > something a little more specific to their needs. For > example, they might > want to search just the set that they got with their > initial query but then > filter out rooms over a certain price and hotels > that were more than a > certain distance from a given tourist attraction. > > It would certainly be possible to save the initial > result and then query > that initial result rather than the full table that > the initial query > targetted. I'm not sure of the best mechanism to do > that in MySQL though > since I've never had to do that in MySQL. DB2 > provides for temporary tables > but I don't recall if MySQL has those. I doubt I > would use a view for this > purpose, even if you were using a version of MySQL > that supported views. > (Remember, MySQL is only just acquiring views in the > Version 4.1.x stream, > which is still in beta. Older versions don't have > views at all.) If > temporary tables are not available, I would likely > use a new table and then > delete it when I know I don't need it any more. > However, I'd want to do some > benchmarking before locking in on that solution: it > might === message truncated === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
