Wickham's HTML & CSS tutorial

Sitemap | Home | Search | Forms

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 above form 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.

ID Name Title Message Email Date required Time required
1 Akash Random Update Test form to MySQL Feb.11.07 8:05 am
2 Brian Test message to MySql from PHP This is to test the length of the text box in MySQL which is TEXT without a length given so I'm not sure when it cuts off. Feb.11.07 8:14 am
3 Akash Another test If the date or time is entered automatically by PHP code the date or time is stated when the form is opened and doesn't change so if you take a long time to submit the form the time is old. If there is no automatic date or time entry, the user enters whatever date or time he wants (perhaps a preferred delivery time). Feb.11.07 8:19 am
36 Akash Random Update Test with or die( "Unable to select database or table"); in two places 13.02.2007 10:48
34 Akash Random Update Test again and to check Timestamp 13.02.2007 08:28
37 Harry Smith Test of data displayed on web page If the date or time have been entered automatically by PHP code in the form, the date and time fields on the form can be edited by the viewer, so it may be better not to include these unless they relate to a required delivery date and time. The automatic timesubmitted (timestamp) field has been omitted here but can be seen in the MySQL database on the server and can be displayed by adding the field to the output table code. harrysmith@hotmail.com 23 Oct 2008 20:00
38 Jon Dodds Another test into web page Testing again into web page. If a submission like an email address is one long text and too wide for the cell, it will expand and reduce the width of others. jonathandodds@hotmail.com 23 Oct 2008 20:57
39 Jonny Testing form to database Test from IE8 and from xhtml page jonny@xyz.com 13 Sep 2009 08:46
40 Ken W Date and time test Testing date and time as text kenw@gmail.com 3rd November 2009 3.15pm

The codes are:-

Styles in the head section:-
<style type="text/css">
table.data { width:100%; height: 50px; margin: 0; border: 1px solid black; border-spacing: 2px; }
.id { width: 30px; background-color: #c7c7c0; }
.whotd { width: 70px; background-color: #d8d8d1; }
.titletd { width: 100px; background-color: #c7c7c0; }
.messagetd { background-color: #d8d8d1; }
.emailtd { width: 130px; background-color: #c7c7c0; }
.datetd { width: 80px; background-color: #d8d8d1; }
.timetd { width: 60px; background-color: #c7c7c0; }
</style>

Body markup:-
<table class="data" border="0" cellspacing="2">
<tr>
<td class="id">ID</td>
<td class="whotd">Name</td>
<td class="titletd">Title</td>
<td class="messagetd">Message</td>
<td class="emailtd">Email</td>
<td class="datetd">Date required</td>
<td class="timetd">Time required</td>
</tr>
</table>

<?php
mysql_connect("localhost","your-username","your-password") or die(mysql_error());
mysql_select_db("your-database-name") or die(mysql_error());
$q = "SELECT * FROM your-table-name ORDER BY 'id' ";
$result = mysql_query($q) or die(mysql_error());
while ($row = mysql_fetch_array($result)){

echo "<table class='data' border='0' cellspacing='2'>
<tr>
<td class='id'>".$row['id']."</td>
<td class='whotd'>".$row['who']."</td>
<td class='titletd'>".$row['title']."</td>
<td class='messagetd'>".$row['message']."</td>
<td class='emailtd'>".$row['email']."</td>
<td class='datetd'>".$row['date']."</td>
<td class='timetd'>".$row['time']."</td>
<tr>
</table>";
}
?>

 

PHP form processing generally

The PHP code for processing forms normally requires a hosting service with Apache server to process PHP. A Linux operating system is often recommended for the hosting service in preference to a Windows OS, (it doesn't matter what you have on your computer), but I don't think that is vital as long as the host has Apache and PHP. There are problems using Godaddy as a hosting service and Godaddy have a special form and email recommendation, so the form above may not work on a Godaddy server.

You can put Apache server on your computer for local testing in localhost by installing WampServer or XAMPP which will also give you PHPmyAdmin and MySQL database.

You cannot send an email from localhost using WampServer or XAMPP unless you edit the php.ini file inside one of the WampServer or XAMPP folders. See roshanbh.com or Google for another tutorial. Save a copy of the original file first in case you make a mistake. It's easier and safer to upload your form and test by sending an email to yourself via your hosting service server. However, the form above does not include email confirmation although this could be added.


Notes

View/Source or View/Page Source in browser menu to see all html code.

The body of this page has margin: 20px. The examples above are in a containing div with width: 730px; and margin: auto; so that the page centralises at large screen resolutions.

A lot of codes have been put in html tags in my examples rather than in a stylesheet or in a style in the head. I have done this for the convenience of the viewer so that most (but not all) codes are in one place and the stylesheet does not always have to be viewed in addition. When coding your own page you should try to put as much as possible in a stylesheet and link with id or class to the html tag.

Remember that when a Doctype is included above the head before the html tag (as it should be) then the overall width of a div is its defined width plus borders, margins and padding widths.

If there are differences between Firefox and IE6 that cannot be overcome with one code, code first to get a satisfactory solution in Firefox then create an IF style which will only apply to IE6:-
for instance, if margin-top: 20px; in Firefox needs to be margin-top: 30px; in IE6 then put the following in the head of the html/xhtml page:-
<!--[if ie 6]> <style type="text/css"> div { margin-top: 30px; } </style> <![endif]-->
or if there are many different styles, create a separate stylesheet:-
<!--[if ie 6]> <link rel="stylesheet" href="ie6.css" type="text/css"> <![endif]-->
IE6 will contain just the amended styles such as div { margin-top: 30px; } and others (no head or body tags or Doctype).

When looking at a page source for this site you may see code like &lt;p>Little Egret&lt;/p> instead of <p>Little Egret</p>. The code &lt; is because in that instance the code is to be displayed on the screen as text. If the < symbol was placed in the code a browser would activate the code and it would not display as text. Such code is not normally required when writing html code tags which are to be activated.

© Wickham 2008


top | Sitemap | Forms | Server Side Includes

 

Google
web www.wickham43.net/