Adding data – MySQL with PHP

It’s the solution that all PHP/MySQL newbies ask for – how to add data to a MySQL database table using PHP via a web page. Once you have understood the basics, it really is quite simple.

Requirements

You will need to ensure that your server runs PHP and MySQL. PHP is a server-side language and MySQL – a highly popular relational database management system. This is a very basic tutorial – trimmed down solution without error checking, so don’t worry too much about which versions of either are installed. Please Note: The following exercises do not include security features. Take a look at this login tutorial.

Building your MySQL database table

For this, you are going to need to login to a MySQL server administration, such as ‘phpmyadmin‘. Contact your server host for more details. Copy and paste the follow MySQL code into your query panel. This show generate a database table called ‘my_table‘.

CREATE TABLE `my_table` (
`id` int(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default ”,
`email` varchar(255) NOT NULL default ”,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Build an HTML form to collect data

This takes us a little away from PHP and MySQL and back to simple HTML. Copy and paste the following into a document and label it as ‘add.php‘.

<form id=”FormName” action=”added.php” method=”post” name=”FormName”>
<table width=”448″ border=”0″ cellspacing=”2″ cellpadding=”0″>
<tr>
<td width = “150” align=”right”><label for=”name”>Name</label></td>
<td><input id=”name” name=”name” type=”text” size=”25″ value=”” maxlength=”255″></td>
</tr><tr>
<td width = “150” align=”right”><label for=”email”>E-mail</label></td>
<td><input id=”email” name=”email” type=”text” size=”25″ value=”” maxlength=”255″></td>
</tr><tr>
<td width=”150″></td>
<td>
<input type=”submit” name=”submitButtonName” value=”Add”></td>
</tr></table>
</form>

Capturing the form data with PHP

Now we need to capture the data sent via the form into a new page called ‘added.php‘ as specified within the top of our form. Add the following to ‘added.php‘. This page will now capture the to form item values of ‘name’ and ’email’, as inputted by your visitor.

<?php
$name = $_POST[‘name’]; /// Retrieve the value of ‘name’ from form
$email = $_POST[’email’];/// Retrieve the value of ’email’ from form
?>

Connecting to your MySQL database

Modify the following code in order to connect to your MySQL database and database tables with PHP. Many servers use ‘local_host’ as the host connector. If you are unsure, ask your server host. Replace the above with the following (added.php)

<?php
// Connection to MySQL database table
$my_db = mysql_connect(‘local_host’, ‘my_username’, ‘my_password’);
$sel3Q = mysql_select_db(‘my_database_name’, $my_db);
//

$name = $_POST[‘name’]; /// Retrieve the value of ‘name’ from form
$email = $_POST[’email’];/// Retrieve the value of ’email’ from form
?>

Now you have opened a connection to your MySQL database ‘my_database_name‘.

Add the data to the MySQL database with PHP

We will now need to tell the PHP to add this data to our MySQL database table. We will modify the above code with the following (added.php).

<?php
// Connection to MySQL database table
$my_db = mysql_connect(‘local_host’, ‘my_username’, ‘my_password’);
$sel3Q = mysql_select_db(‘my_database_name’, $my_db);
//

$name = $_POST[‘name’]; /// Retrieve the value of ‘name’ from form
$email = $_POST[’email’];/// Retrieve the value of ’email’ from form

/// Add it to the database
$query = “INSERT INTO my_table (id, name, email)
VALUES (”, ‘$name’, ‘$email’ )”; /// the value of ‘id’ is left blank as it is set to ‘auto_increment’
$results = mysql_query($query); /// Process the above
///

if ($results) /// If it worked echo statement
{
echo “Details added.”;
}
else /// If it didn’t worked echo statement
{
echo “Didn’t work!!!”;
}
mysql_close(); /// Close connection with MySQL database – recommended!
?>

As you can see from the above, the communication with the MySQL is sent via a string ‘$query’. The string is then passed through mysql_query to send the contents of that string to the MySQL for processing.