MySQL Queries

You will be putting information about classes your organization has on your web site. It will be a lot easier for your students and prospective students to be able to get basic class information from your web site, instead of having to call you. You have decided that you want the following information on your web site:

1. Class Schedule
2. Information about your instructors and their qualifications
3. A place for viewers to request classes.

After designing and building the database, you will have to enter information about your instructors and your classes into the database. Then, you will be able to make the web pages that pull information from your database. All of these functions require the use of "database queries." Database queries are build with a "query language." SQL is the query language you will use. This language is commonly used with MySQL databases on web sites.

There are some basic functions in SQL: insert, update, and select. All these queries have a very specific syntax - or grammar. For each query, you have key words that belong to SQL and words that you need to know from your own database. SQL has much more functionality that what is covered here, but the following are some basics.

INSERT QUERY Keywords:    INSERT    INTO    VALUES

UPDATE QUERY Keywords:   UPDATE   SET   WHERE

SELECT QUERY Keywords:    SELECT    FROM    WHERE

INSERT
When you want to add a new record into a database, you will use the INSERT keyword like this: INSERT INTO your table (fields separated by commas) VALUES (data separated by commas with ' 'around each item). Read this before continuing: How your Database Design affects the INSERT

Here is some basic information about a Course.

Course Name: Introduction to Textile Designs Around the World
Instructor: Judy Hartz
Course Description: This course will feature specific traditional textile fibers and designs from 10 geographic areas of the world. The last class will be a hands on project with Japanese Shibori tie dyeing techniques.
Dates: Jan 8 – Feb 12, Tuesdays and Thursdays from 10:00 – 12:00
Materials: Kit for Shibori hands-on project - $5.00

In our scenario, the connection to the Instructor, the Date, and the time are in the CourseSchedule. So, for this Course, we will enter the following values into a record in the database: '', 'Introduction to Textile Designs Around the World', 'This course will feature specific traditional textile fibers and designs from 10 geographic areas of the world. The last class will be a hands on project with Japanese Shibori tie dyeing techniques.', 24, 'none', '$100.00'. '$5.00', 10, 16, 'Beginner' .

Notice the '' at the beginning of the list. That means that we aren't sending a value for the CourseID. (see the Link in the first paragraph for why.) If any of the values in the list is a different data type than what we build in the database, the INSERT will fail.

The complete query statement for this INSERT is:
INSERT INTO Courses VALUES ('', 'Introduction to Textile Designs Around the World', 'This course will feature specific traditional textile fibers and designs from 10 geographic areas of the world. The last class will be a hands on project with Japanese Shibori tie dyeing techniques.', 24, 'none', '$100.00'. '$5.00', 10, 16, 'Beginner');

In this case, there is something for each field; so, we don't have to include a field list. However, if we were only entering information for a few of the fields, we would have to say which fields the information goes in. Your INSERT will fail if your data types don't match the actual database.