Add a record to a MySQL database

 

To add records to a table in your database, you use more or less the same code as previously. The only thing that needs to change is your SQL statement. The steps we're going to be taking are these:

  1. Open a connection to MySQL
  2. Specify the database we want to open
  3. Set up a SQL Statement that can be used to add records to the database table
  4. Use mysql_query( ) again, but this time to add records to the table
  5. Close the connection

 

Set up a SQL Statement to add records to the database

In our previous script, we used some SQL to grab records from our Address Book database table. We then used a While loop to print all the records out. Because we're now going to be adding records to the Address Book table, we need some different SQL. Here's the script. The new line is in blue (The double and single quotes need to be entered exactly, otherwise you'll get errors when you run the code):

<?PHP

$user_name = "root";
$password = "";
$database = "addressbook";
$server = "127.0.0.1";

$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "INSERT INTO tb_address_book (First_Name, Surname, Address) VALUES ('bill', 'gates', 'Microsoft')";

$result = mysql_query($SQL);

mysql_close($db_handle);

print "Records added to the database";

}
else {

print "Database NOT Found ";
mysql_close($db_handle);

}

?>

You met all of this code from the previous section. The only difference is the new SQL statement! What the code does is to set up some variables, open a connection to the database, and then execute the SQL query. Let's have a look at the new, and rather long, statement.

 

INSERT INTO … VALUES

To add records to your database, you can use the INSERT statement. There are plenty of ways to use this statement, but we'll stick with something simple: adding
new values to all of our table columns.

You start by typing the words "INSERT INTO". This can be in any case you like: upper, lower or a mix. It's easier for you to read if it's in uppercase letters.

The next thing you need is the name of a table to insert your new values into. For us, this is the table that we've called tb_address_book.

Following the name of your table, type a pair of round brackets. Inside the round brackets, you can type the names of the columns in your table:

INSERT INTO tb_address_book (First_Name, Surname, Address)

Notice how we haven't included the ID column from our table. That's because the ID column was the one we set up to be an auto-incrementing number. We don't need to worry about this column because MySQL will take care of adding 1 to this field for us.

Now that you've specified which table you want to insert values into, and specified your column names, you can add the values you want to insert.

To add values, you type the word "VALUES" after the round brackets of your column names:

INSERT INTO tb_address_book (First_Name, Surname, Address) VALUES

After the word "VALUES", you type another pair of round brackets. Inside of these brackets, you can type your values. Each value should be separated by a comma. You can use either direct text, like we've done, or variables. You can even get these values straight from your HTML form, which we'll see how to do later.

So our whole line reads:

$SQL = "INSERT INTO tb_address_book (First_Name, Surname, Address) VALUES ('bill', 'gates', 'Microsoft')";

Notice how we've surrounded all of our text with double quotes. But inside of the round brackets of VALUES, we've used single quotes.

The syntax is really this (The SQL keywords are in bold):

INSERT INTO table_name ( Columns ) VALUES ( values for columns)

But try your code out now, and see if it's all working properly. You should find that you now have two records in your database table.

Exercise
Replace the values 'bill', 'gates', and 'Microsoft' with values of your own. Run your script again to add your new record to the database. Now run your other script to read the values back out.

In the next part, you'll learn about something called Magic Quotes.