Add MySQL Data One Row at a Time - dummies

By Steve Suehring, Janet Valade

If you have a small amount of data to add to your MySQL database, you can add one row at a time to the table. PHP scripts often need to add one row at a time. For instance, when a PHP script accepts the data from a customer in a form, it usually needs to enter the information for the customer into the database in a new row.

You use the INSERT statement to add a row to a database. This statement tells MySQL which table to add the row to and what the values are for the fields in the row. The general form of the statement is

INSERT INTO tablename (columnname, columnname,...,columnname)
     VALUES (value, value,...,value)

The following rules apply to the INSERT statement:

  • Values must be listed in the same order in which the column names are listed. The first value in the value list is inserted into the column that’s named first in the column list; the second value in the value list is inserted into the column that’s named second; and so on.

  • A column list, full or partial, is allowed. You don’t need to list all the columns. Columns that aren’t listed are given their default value or left blank if no default value is defined.

    Remember, any columns that are defined as NOT NULL must be included, with values, or the statement will fail.

  • A column list is not required. If you’re entering values for all the columns, you don’t need to list the columns at all. If no columns are listed, MySQL looks for values for all the columns, in the order in which they appear in the table.

  • The column list and value list must be the same. You must provide a value for every column that you list or you’ll get an error message like this: Column count doesn’t match value count.

The following INSERT statement adds a row to the Customer table:

INSERT INTO Customer (lastName, street,city,state,zip,
       VALUES ("Contrary","1234 Garden St","Garden","NV",”88888",
               "","(555) 555-5555",")

Notice that firstName isn’t listed in the column name list. No value is entered into the firstName field. If firstName were defined as NOT NULL, MySQL would not allow this. Also, if the definition for firstName included a default, the default value would be entered, but because it doesn’t, the field is left empty. Notice that the value stored for fax is an empty string.

To look at the data that you entered and ensure that you entered it correctly, use an SQL query that retrieves data from the database. The following query retrieves all the data in the Customer table:

SELECT * FROM Customer