Wickham's XHTML & CSS tutorial

Search | Forms | Server Side Includes

Form submission to MySQL database from PHP file

View in Firefox, Safari, Opera and IE but IE6 often needs different solutions. In general IE7 and IE8 display like Firefox apart from the HTML5 and CSS3 features. The IE9 & above updates are mainly related to HTML 5 and CSS3 issues and display to a large extent like other main browsers. Google Chrome is based on the same WebKit engine as Safari.

Some of the examples are provided just to show problems, others show solutions that work in most major browsers. Use the example that suits you.
red icon problems   gold icon warning: works in some situations or some browsers or needs care to display as you wish   green icon OK in Firefox, Safari, Opera, Google Chrome and IE

The PHP file code

1 green icon This example shows how form data can be submitted to a MySQL database from a PHP file.

NOTE: It is now recommended that you use MySQLi code instead of MySQL as it has more advanced security features. You can't just edit mysql to mysqli in the code as MySQLi requires more than one parameter in many cases. The example below has not been upgraded to MySQLi but still shows general principles.

The MySQL version provided by my host is 5.0.81 - standard and the PHP version is 5.2.5. My host provides phpMyAdmin to access MySQL but you may have a stand-alone version of MySQL.

Form to database example

The form above has been disabled.

When the submit button is pressed to send the message this page is refreshed; the form disappears and the confirmation message "Thank You; your comments have been entered in my database." in red appears in its place (just above this paragraph) or an error message.

Once the data has been sent and the confirmation message shown, if the page is refreshed manually the data is sent again, resulting in duplicate data in the database which could be a problem as it might cause items to be purchased again. IE7, Firefox and Safari for Windows show a warning popup giving the option to re-send or cancel but Opera does not. It's best to show a comment in the confirmation message like "DO NOT REFRESH THE PAGE or data will be sent again". Viewers need to use the back button or go to another page or close the browser.

The date and time fields are text fields in the form and MySQL database on the assumption that you want a viewer to insert a required future date and time. You can use PHP functions to automatically set a future date and time. The Timestamp in the database records the submission date and time.

The PHP code used for this form and MySQL database insert is from spoono.com

The code is as follows:-

<?php
//initilize PHP
if ( isset ( $_POST['submit'] ) ) //If submit is hit
{
//then connect as user
//change username and password to your mySQL username and password
mysql_connect("localhost","your-username","your-password");
//if you wish to connect to a local MySQL database the code is mysql_connect("localhost","root",""); until you set a username and password
//select which database you want to edit
mysql_select_db("your-database-name")or die( "<p><span style=\"color: red;\">Unable to select database</span></p>");
//convert all the posts to variables:
$title = $_POST['title'];
$message = $_POST['message'];
$who = $_POST['who'];
$email = $_POST['email'];
$date = $_POST['date'];
$time = $_POST['time'];
//Insert the values into the correct database with the right fields
//mysql table = your-table-name
//table columns = id, title, message, who, email, date, time
//post variables = $title, $message, $who, $email, $date, $time
$result=MYSQL_QUERY("INSERT INTO your-table-name (id,title,message,who,email, date,time)".
"VALUES ('NULL', '$title', '$message', '$who', '$email', '$date', '$time')")or die( "<p><span style=\"color: red;\">Unable to select table</span></p>");
mysql_close();
//confirm
echo "<p><span style=\"color: red;\">Thank You; your comments have been entered in my database. DO NOT REFRESH THE PAGE or data will be sent again.</span></p>";
}
else
{
// close php so we can put in our code
?>

<?php
} //close the else statement
?>

The code for the styles is:-

Setting up MySQL

You can find some good tutorials on Google but here are some notes relating to the example above. The following notes relate to MySQL as it appears using phpMyAdmin.

The first requirement is to sign in with a username and password and to choose a name for your database. Once that has been done and you have opened the main page click on databases to open the database menu which will list your database. You may or may not be able to add more databases depending on the contract with your host.

Open the database and use an input box to create a table (you can create more than one table in any database) giving it a Name and Number of fields, eight in the above example, which will open another page where the table structure has to be completed, which for the above example is:-

Field Type Length/ Values Collation Attributes Null Default Extra Action
id int 11     No   auto_ increment Primary
title text   utf8_unicode_ci   Yes NULL    
message longtext   utf8_unicode_ci   Yes NULL    
who text   utf8_unicode_ci   Yes NULL    
email varchar 50 utf8_unicode_ci   Yes NULL    
date text   utf8_unicode_ci   Yes NULL    
time text   utf8_unicode_ci   Yes NULL    
timesubmitted timestamp     ON UPDATE CURRENT_ TIMESTAMP Yes CURRENT_ TIMESTAMP    

There is a useful MySQL tutorial at dev.mysql.com.

The following link describes the basic operation of the Timestamp:- dev.mysql.com timestamp and one of the comments is:-

If your table has a timestamp column then MySQL will automatically populate the field with the current time when a row is inserted without specifying a value for the timestamp column (or if the timestamp column is specified as being NULL). The timestamp column will also be updated to the current time whenever a row is updated with new information (once again as long as the column is either unspecified or set to NULL).

Data can be exported to a number of different formats including Excel, Open Document Text, PDF, SQL and XML. Just click "Export" when viewing the required table, select the file type, complete the options for that file type (not always necessary) and click Go. Once exported to Excel a long text sequence will be in one long horizontal cell so format that column to wrap text. Do other formatting as necessary.

Accessibility and security

The forms above only show the basic codes and accessibility aids have been ignored. Additional codes to help people with poor eyesight or those unable to use a mouse can be found on these sites:-
webSemantics; Accessify.com; Webaim.com and Devarticles.com.

Additional codes to assist security can be found on thesitewizard and in connection with SQL injection php.net and tizag.com and DiGiFUZZ.net.

Displaying the data on a web page

You can display the database on the webpage if you want people to see all the contents, or create a separate webpage for private viewing by only you. In the table below I haven't shown the timesubmitted (timestamp) field for reasons of space.

The texts "connected to mysql," "connected to my database," "made a query and the result of that query put in a variable" are automatically generated by the PHP so that if the table fails to show you can see which stage it failed at on a test web page or you can leave out the echo codes on a final web page for neatness.

ID Name Title Message Email Date required Time required