----- Original Message ----- From: "Skip Taylor" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, October 16, 2004 5:08 PM Subject: Can MySQL do this?
> Hello, > > I'm new to MySQL, SQL in general and even PHP which may be needed to do this. > My intention is to implement this on an Internet webpage. > > I want to set up a database for listing people in cities for various specialties with a > time limit on their availability (date limited). > > I envision the tables as follows: > > US_States > (index, state name) > > US_Cities > (index, stateindex, city name) > > Persons_Specialty > (index, specialty name) > > TimeLimitedSpecialtyInfo > (index, personinfo_index, persons_specialty_index, city_index, state_index, ending > date) > > person_info > (index, person's name, address, city_index, state_index, phone, fax, email, url) > > I envision it working like this: > > From a dropdown list, the state would be selected. > > The selection would change to the cities in that state from the us_cities table for > selection in a drop down list . > > Then select the specialty you are looking for from a drop down list. > > Click Search > > At this point it should search the TimeLimitedSpecialtyinfo table, and return the links > to the appropriate entries in the personinfo table for display. > > I am at odds for the best way to do this. Previous experience in databases is not on > the web and I'm unsure as to time required to learn all of this. > You are using two common words in non-standard ways and that is causing me a bit of confusion. Those two words are "index" and "link". I *think* you mean to say "key" instead of "index" and "join" (or maybe "lookup") instead of "link"; I'll explain that in a minute. IF those are reasonable substitutions on my part, then you should have no trouble getting MySQL to accomodate your database design. In your table descriptions, you have an "index" in each of your tables. I think you should use the term "key". It might sound nitpicky but an index is always built on a table column but it is not a table column itself. For example, given your first table, I would probably create it as follows: create table US_States (state_code char(2) not null, state_name char(20) not null, primary key(state_code)); The contents of the table would then be: state_code state_name ------------ ------------- AL Alabama AK Alaska NH New Hampshire The state_code column is serving as a key. Your queries to find the description for a given state will seach on the state_code column. For example: select state_name from US_States where state_code = 'AL'; should return 'Alabama'. An index, properly speaking, is a structure that is separate from the table itself, i.e. a separate file, which you can choose to construct to improve the performance of queries like the one I just gave in my example. The index is created with a separate statement like this: create unique index state_ix on US_States(state_code); You are not required to build indexes but they are frequently a good idea if you don't overdo them. The other term that you are misusing is "link". I think you mean "join" or maybe "lookup" where you said "link". For example, if you want to know the full name of a state that is referenced in the US_Cities table, you will use a join to do a lookup of that information in the US_States table. Therefore, if the US_Cities table looks like this: state_code city_name ------------ ------------ AL Springfield AK Anchorage NH Springfield This query will determine the full name of each state that contains a city named Springfield. select city_name, state_name from US_States s inner join US_Cities c on s.state_code = c.state_code where c.city_name = 'Springfield' Which should give this result: city_name state_name ------------- -------------- Springfield Alabama Springfield New Hampshire The process of finding something like the full state name that corresponds to a state code is usually called "doing a lookup". The technique for doing a lookup in SQL is a "join": the 'from US_States s inner join US_Cities c on s.state_code = c.state_code' line is the part of the statement that combines the two tables where the state codes match; this is called a join. Again, I don't say these things to be pedantic, I'm just trying to put this information in terms that most experienced database people will understand. As I said before, if this is what you mean, you should have no great difficulty creating a MySQL database to do what you want. How long that will take is another matter. I've been doing database stuff like this for 20 years so I could build what you are describing in a few hours. If you're new to SQL and database, it might take you days or weeks, depending on if you are a quick study or not and how much help you get. The first time through anything can be quite painful due to the learning curve, as you probably know from other things you've learned. The good news is that database work is, for the most part, pretty logical so it shouldn't be too hard to get your head around. > Is there a database generation system that would be able to handle this task and > generate a webpage as output? > As for tools that would generate your tables and PHP code, I really can't say. I write my own SQL by hand (and by choice) and execute it from the command line and/or scripts; I also write my own Java code to display web pages. However, I believe Stuart Felenstein, another relative newbie on this list, is using a tool much like you describe so I am taking the liberty of copying him with this reply. Stuart, when you get this, can you please respond to Skip with information about the tool you are using? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
