Adding Information to a Database

Before we can read data from a database we will need to insert data into the database.

Most databases speak a specific language called SQL. The good news is most of them speak the same language, so learning it for one works for most of them. The bad news is that they tend to all speak a slightly different dialect. This means that while about 85-90% of SQL will transfer between most databases, it is always good to check documentation when moving to a new database - just to make sure you are correct.

Inserting information into a database table requires using the INSERT INTO command. It follows the general form of:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

table_name - Is the table where the data will be stored.

(column1, column2, column3,...) - Optional, but recommended, are the fields of the table that will have information inserted into, in the order that you pass in via the VALUES section.

VALUES (value1, value2, value3,...) - The values passed into the database table.

Example #1 - Inserting Fixed Data into a Database

In this example, we will pass specific data, that we as the developers, have defined. This information cannot be changed by any user.

 

<?php
include('dbconnect.php');
$sql = 'INSERT INTO guestbook (`name`, `entry`, `entered`) VALUES (' 	
	.SQLValueString('John Smith', 'text') .', '
	.SQLValueString("I'm loving this site...", 'text') .', '
	.'CURDATE() )';
$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());
?>

The PHP Code Explained

OK, there are a lot of things going on in this simple file, which is technically only 3 lines long.

include('dbconnect.php'); - This imports the DB connection information from the last part. This will auto connect us to the database, and also gives us a helper function (which I am giving you)

$sql = - This is setting up the SQL string for creating the entry into the guestbook. We'll look at the SQL statement in a minute.

$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error()); - mysql_query takes the SQL statement, and executes it on the database server, based upon the commands within the string. We specity $db_connection as to let PHP know which database connection to use. This is because we can have more than one connection to a database open at a time. While this parameter is optional, I recommend always using it - so to avoid potential confusion. We've seen the die() and mysql_error() commands before, and won't cover them again.

The SQL Statement Explained

INSERT INTO - This lets us know what type of command we are performing on the database. INSERT INTO is going to add data to an existing table.

guestbook - Guestbook the table we will insert data into. This will potentially change for each INSERT INTO command we run, as we may have many tables

(`name`, `entry`, `entered`) - These are the fields, in the order, which we will be inserting data into. If we are entering in every field, in the order it is listed in the database table, then we do not have to list the fields. However, this is rarely done (especially if an autonumber primary key is used) so it is a good habit to list the fields.

VALUES - This is the list for information to go into the fields.

SQLValueString(<info>, <data type>) - This is a user function - which you will get from me, which lets the system know what type of data you are sending the database, and encloses it within single quotes if the database needs it. The data type could be "date", "text", "int", "float", and "long". The function also helps protect against SQL injection attacks, and allows the user to post a single quote into the database.

CURDATE() - This is a built in MySQL funciton which returns the current date. This way we don't have to calculate the date ourselves using PHP.

 

Example #2 - Inserting User Defined Data into a Database

In this example we will look at adding data to the system which is entered by a user from a web form. We will use the example of the guestbook from earlier.

<?php
include('dbconnect.php');
$sql = 'INSERT INTO guestbook (`name`, `entry`, `entered`) VALUES (' 	
	.SQLValueString($_POST['name'], 'text') .', '
	.SQLValueString($_POST['comment'], 'text') .', '
	.'CURDATE() )';
$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());
?>

If you look at these two examples, you will notice they are almost identical. The difference is solely within the INSERT INTO SQL statement. Instead of hard-coding the information into the string, we are looking to see what has been entered from a form that was sent via the POST method.