Inserting data into MySQL using PHP

Now we're going to add our first entry into the database. First of all, make sure you have either the Database connection code, or have included the path to the PHP file containing this code. Otherwise your script can't connect to your database.

To add an entry to the table 'addresses' in your database, use this syntax:

PHP:

mysql_query("INSERT INTO addresses(name,address,phone) VALUES('John Doe', '100 Harborway', '555-4839')");


Now if you run this PHP script, it adds an entry in the database. As you can see, I am pointing the order in which I want to add data. This order is 'name', 'address', 'phone'. The values are therefore inserted in the order I've specified the column names.

You don't have to enter a value for 'id', because it has AUTO_INCREMENT enabled.

Everytime you pass an instruction to MySQL, you should use the mysql_query() function in PHP.

We can even create a form to add addresses to our database.
The HTML part:

HTML:

<form method="post" action="">
Name: <input type="text" name="name" />
Address: <input type="text" name="address" />
Phone Number: <input type="text" name="phone" />
<input type="submit" name="add" value="Add Person"/>
</form>

Below that, we then use the following PHP code between PHP tags:

PHP:

// Only run the code if form has been submitted. Otherwise it will immediately execute the query, and therefore enter empty data into the database!
if(isset($_POST['add'])) {

// Creating an easier readable variable for each item
$name = $_POST['name'];
$address = $_POST['address'];
$phone = $_POST['phone'];

// The query
mysql_query("INSERT INTO addresses(name,address,phone) VALUES('$name', '$address', '$phone')");
}


Now try it out for yourself. If it doesn't work, make sure you've ended the MySQL query with a semicolon, and don't forget the additional ") at the end.

This does create a security risk. People can do something which is called SQL Injection. This allows someone to put an SQL query inside a form, which can delete an entire table or output a list of users and passwords. I'll explain this in detail in a later lesson.

Meanwhile you should get familiar with a PHP function called mysql_real_escape_string(). Basically it processes the contents of a variable and converts all quotation marks into so-called Escaped Characters. This makes PHP recognize quotation marks as text instead of the end and beginning of a new instruction.

It sounds complicated, but it's easy. Remember when you assigned a new variable to each $_POST array item? We're going to have the mysql_real_escape_string() function process each item before we assign it to each variable:

PHP:

// Old situation (unsafe):
$name = $_POST['name'];
$address = $_POST['address'];
$phone = $_POST['phone'];

// New situation (safe):
$name = mysql_real_escape_string($_POST['name']);
$address = mysql_real_escape_string($_POST['address']);
$phone = mysql_real_escape_string($_POST['phone']);