Inserting Records


After your database and its table(s) have been created, you can start populating them using the INSERT command. Before demonstrating the syntax, I want to be explicit as to how you should treat the different data types in your SQL commands. Always abide by these rules:

  • Numeric values shouldn't be quoted.

  • String values (for CHAR, VARCHAR, and TEXT column types) must always be quoted.

  • Date and time values must always be quoted.

  • Functions cannot be quoted.

  • The word NULL must not be quoted.

With that in mind, there are two formats for inserting data. With the first, you specify the columns to be used:

 INSERT INTO tablename (column1,   'value2 ...) INSERT INTO tablename (column4,  Using this structure, you can add rows of records, populating only the columns that matter. The result will be that any columns not given a value will be treated as NULL (or given a default value, if that was defined). Note that if a column cannot have a NULL value (it was set as NOT NULL), not specifying a value will cause an error.

The second format for inserting records is not to specify any columns at all but to include values for every one.

 INSERT INTO tablename VALUES ('value1',  NULL, 'value3, 30, ...) 

If you use this second method, you must specify a value, even if it's NULL, for every column. If there are six columns in the table, you must list six values. Failure to match the number of values to the number of columns will cause an error. For this and other reasons, the first format of inserting records is generally preferable.

MySQL also allows you to insert multiple rows at one time, separating each record by a comma.

 INSERT INTO tablename (column1,   ('valueC, 'valueD'), ('valueE',  'valueF) 

While you can do this with MySQL, it is not acceptable within the ANSI SQL2 standard and is therefore not supported by all database applications.

To insert data into a table

Two MySQL Functions

Although I'll be discussing functions in more detail later in this chapter, I want to introduce two MySQL functions here: SHA() and NOW().

The SHA() function is one way to encrypt data. This function creates an encrypted string that is always exactly 40 characters long (which is why I set the users table's password column as CHAR(40)). SHA() is a one-way encryption technique, meaning that it cannot be reversed. It's useful for storing sensitive data that need not be viewed in an unencrypted form again, but it's obviously not a good choice for sensitive data that should be protected but later viewed (like credit card numbers). If you are not using a version of MySQL later than 4.0.1, you can use the MD5() function instead, and set the password column as CHAR(32). This function does the same task, using a different algorithm, and returns a 32-character long string.

The NOW() function is handy for date, time, and timestamp columns, since it will insert the current date and time (on the server) for that field.

When using any function in a SQL statement, do not place it within quotation marks. You also must not have any spaces between the function's name and the following parenthesis (so NOW() not NOW ()).


1.

Insert a new row of data into the users table (Figure 4.9).

Figure 4.9. This query inserts a single record into the users table.


Your syntax would be one of the following:

  •  INSERT INTO users (first_name,  last_name, email, password,  registration_date) VALUES  ('Larry, 'Ullman',  'phpmysql2@DMCInsights.com,  SHA('password), NOW()); 

    or

  •  INSERT INTO users VALUES (NULL,  'Larry, 'Ullman',  'phpmysql2@DMCInsights.com,  SHA('password), NOW()); 

Again, the first syntax (where the specific columns are named) is more foolproof but not always the most convenient. Two functions are used in both cases (see the sidebar "Two MySQL Functions" for more on these). I use the NOW() function to set the registration_date as this moment (notice the function is not enclosed by quotation marks). Another function, SHA(), is used to store an encrypted form of the password. It's available in versions 4.0.2 and later of MySQL (if you have an earlier version, use MD5() instead of SHA()).

In the second example (where I must insert a value for the user_id), I set the user_id field as NULL, which will cause MySQL to use the next logical number, per its AUTO_INCREMENT description. This wasn't necessary in the first example, as no value was specified at all.

2.

Insert several values into the users table (Figure 4.10).

 INSERT INTO users (first_name,  last_name, email, password,  registration_date) VALUES ('John', 'Lennon', 'john@beatles.  com, SHA('Happin3ss'), NOW()), ('Paul', 'McCartney', 'paul@beatles.  com, SHA('letITbe'), NOW()), ('George', 'Harrison',  'george@beatles.com,  SHA('something), NOW()), ('Ringo', 'Starr', 'ringo@beatles.  com, SHA('thisboy'), NOW()); 

Figure 4.10. This one querywhich MySQL allows but other databases will notinserts several records into the table at once.


Since MySQL allows you to insert multiple values at once, you can take advantage of this and fill up the table with records.

3.

Continue Steps 1 and 2 until you've thoroughly populated the users table.

Throughout the rest of this chapter I will be performing queries based upon the records I entered into my database. Should your database not have the same specific records as mine, change the particulars accordingly. The fundamental thinking behind the following queries should still apply regardless of the data, since the sitename database has a set column and table structure.

Tips

  • On the scripts page of the book's supporting Web site (www.DMCInsights.com/phpmysql2), you can download all of the SQL commands for the book. Using some of these commands, you can populate your users table exactly as I have.

  • If you need to insert a value containing a single quotation mark, escape it with a backslash:

     INSERT INTO tablename (last_name,  first_name) VALUES ('O\'Toole,  'Peter); 

  • The term INTO in INSERT statements is optional in current versions of MySQL.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net