Lesson 19: Insert data into a database

In this lesson, we look at how you can insert data into the database directly from your PHP scripts.

Insert data using SQL

You use SQL to insert data in a database in the same way that you can use SQL to create databases and tables. The syntax of the SQL query is:

	INSERT INTO TableName(column1, column2, ...) VALUES(value1, value2, ...)

As you can see, you can update multiple columns in the SQL statement by specifying them in a comma-separated list. But of course, it is also possible to specify just one column and one value. The columns that are not mentioned in the SQL statement will just be empty.

Example: Insert a new person in the table

In this example we use the database from lesson 18. Let's say we want to insert a person into the database. It could be the person Gus Goose with the phone number 99887766 and 1964-04-20 as the date of birth.

The SQL statement would then look like this:

	
	$strSQL = "INSERT INTO people(FirstName,LastName,Phone,BirthDate) VALUES('Gus','Goose','99887766 ','1964-04-20')"; 

	mysql_query($strSQL) or die(mysql_error());

As you can see, SQL statements can get quite long, and you can easily lose track. Therefore, it can be an advantage to write the SQL statement in a slightly different way:

	strSQL = "INSERT INTO people(";

	strSQL = strSQL . "FirstName, ";
	strSQL = strSQL . "LastName, "
	strSQL = strSQL . "Phone, ";
	strSQL = strSQL . "birth) ";

	strSQL = strSQL . "VALUES (";

	strSQL = strSQL . "'Gus', ";
	strSQL = strSQL . "'Goose', ";
	strSQL = strSQL . "'99887766', ";

	strSQL = strSQL . "'1964-04-20')";
	 
	mysql_query($strSQL) or die(mysql_error());

This way, the SQL statement is built up by splitting the sentence into small parts and then putting those parts together in the variable $strSQL.

In practice, it makes no difference which method you choose, but once you start working with larger tables, it's crucial that you always keep track, so choose the method you find most convenient.

Try running the following code to insert Gus Goose into the database:

	<html>
	<head>
	<title>Insert data into database</title>
	</head>
	<body>
	<?php

	// Connect to database server
	mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ());

	// Select database
	mysql_select_db("mydatabase") or die(mysql_error());

	// The SQL statement is built

	$strSQL = "INSERT INTO people(";

	$strSQL = $strSQL . "FirstName, ";
	$strSQL = $strSQL . "LastName, ";

	$strSQL = $strSQL . "Phone, ";
	$strSQL = $strSQL . "BirthDate) ";

	$strSQL = $strSQL . "VALUES(";

	$strSQL = $strSQL . "'Gus', ";

	$strSQL = $strSQL . "'Goose', ";
	$strSQL = $strSQL . "'99887766', ";

	$strSQL = $strSQL . "'1964-04-20')";

	// The SQL statement is executed 
	mysql_query($strSQL) or die (mysql_error());

	// Close the database connection
	mysql_close();
	?>

	<h1>The database is updated!</h1>
	</body>
	</html>

Save user input into a database

Often you want to save user input in a database.

As you've probably already figured out, this can be done by creating a form as described in lesson 11 - where the values from the form fields can be inserted in the SQL statement. Suppose you have a simple form like this:

	<form action="insert.php" method="post">
	<input type="text" name="FirstName" />
	<input type="submit" value="Save" />

	</form>

The form submits to the file insert.php where you, as shown in lesson 11, can get the user's input by requesting the form content. In this particular example, an SQL statement could look like this:

	strSQL = "INSERT INTO people(FirstName) values('" . $_POST["FirstName"] . "')"

In the same way, it is possible to retrieve data from cookies, sessions, query strings, etc.

Most common beginner mistakes

In the beginning, you will probably get a lot of error messages when you try to update your databases. There is no room for the slightest inaccuracy when you work databases. A misplaced comma can mean the database is not being updated, and you get an error message instead. Below, we describe the most common beginner mistakes.

Wrong data types

It is important that there is consistency between the type of data and column. Each column can be set to a data type. The screenshot below shows the data types for the table "people" in our example.

Specifying data types

An error occurs if you, for example, attempt to insert text or numbers in a date field. Therefore, try to set the data types as precisely as possible.

Below is the most common data types listed:

Setting	Data Type	Size
CHAR	Text or combinations of text and numbers. Can also be used for numbers that are not used in calculations (e.g., phone numbers).	Up to 255 characters - or the length defined in the "Length"
TEXT	Longer pieces of text, or combinations of text and numbers.	Up to 65,535 characters.
INT	Numerical data for mathematical calculations.	4 bytes.
DATE	Dates in the format YYYY-MM-DD	3 bytes.
TIME	Time in the format hh:mm:ss	3 bytes.
DATETIME	Date and time in the format YYYY-MM-DD hh:mm:ss	8 bytes.

SQL statements with quotes or backslash

If you try to insert text that contains the characters single quote ('), double quote (") or backslash (), the record may not be inserted into the database. The solution is to add backslashes before characters that need to be quoted in database queries.

This can be done with the function addslashes this way:

	<?php

	$strText = "Is your name O'Reilly?";
	$strText = addslashes($strText);

	?>

All single quotes ('), double quotes (") and backslashs () will then get an extra backslash before the character. This would only be to get the data into the database, the extra will not be inserted. Please note that PHP runs addslashes on all $_GET, $_POST, and $_COOKIE data by default. Therefore do not use addslashes on strings that have already been escaped.

In the next lesson you will learn to retrieve data from your database. But first, try to insert some more people in your database (as shown in the example above with Gus Goose).

Username:
Password: