Now that we’ve got a simple understanding of how to perform SELECT, INSERT and UPDATE MySQL queries (and if not, have a quick review from the last couple of tutorials) we can now work on building ourselves a very simple PHP application to accept user data and save it into our database. This information will then be displayed to anyone visiting the site. We’ll also take a quick look at how we can generate a script to authenticate administrative users based on a username and password combination and allow those admin users to delete unwanted comments from the database.
The first thing we’ll need to do is generate the HTML form that we’ll use to accept the user data, and include a couple of important settings which will let us access this information later, once the user has submitted the form.
First, here’s the code you’ll need to use to generate the HTML form. Save this file somewhere in your PHP directory, with the name mysql_form.php
A simple PHP/MySQL form <h2>Please enter your name and a comment</h2> <form action="form_submit.php" method="post"> Name : <input size="32" type="text" name="username" /><br> Comment : <textarea cols="40" rows="5" name="comment"></textarea><br> <input type="submit" value="Send comment" /> </form>
Here, we’re using the <form> tag to generate a set of text-entry boxes where a user can enter their details, to be sent to the server for processing. We have specified that we wish the data to be sent using the POST delivery method. This simply means that the data will be sent within the request to the server, rather than attached to the query string, as would be the case if we were using the GET method.
Next, we need to create another file which will receive the request from the user, and process the data accordingly. We have specified that this file will be called form_submit.php. Create this file, and within it add the following code
Before we go any further here, I’ll need to give you two things. Firstly, the contents of the file db_connect.php. What I’ve done here is simply place the mysql_connect() function within the file db_connect.php to save us having to type out the database connection code twice, as we’ll be adding that into our initial HTML file, mysql_form.php, in just a moment. Here is the connection file as it is set up on my computer (you may need to change the values to suit your installation of PHP)
Secondly, you’ll need the structure for the “comments” table. This should be created in the same database as is defined in the connection file above
DROP TABLE IF EXISTS `comments`; CREATE TABLE IF NOT EXISTS `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `comment` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Notice how I’ve defined three columns, the first being an “id” column thay is also declared as a PRIMARY KEY, as well as having the AUTO_INCREMENT attribute set. This is very common practice in MySQL tables, and means we don’t need to concern ourselves with having comments with duplicate names, for example. These unique IDs also give us an easy way to refer to specific comments by number when administrating them at a future date, or deleting comments we don’t wish to keep.
Try out the code I’ve included above. You should be able to see an HTML form and, when you submit that form, you should see a page saying “The comment was added successfully”. If not, then something has gone wrong and you should go back and recheck your code.
One thing you might notice is if you try and submit the form without adding a name or a comment, then the MySQL query will still complete without a problem, and you’ll be left with a blank entry in the database. One way to get around this is to modify our code to check for such a situation and, if no data has been entered, prompt the user to go back and try again. The following change to form_submit.php will add this behaviour
If you try again, you’ll now see that the form will not accept blank data.
There’s a great deal more to consider when accepting user input into your database, and we’re going to look at that in further detail in the next section. For now though, we’ll continue with our example, and provide our visitors with some feedback on the comments posted so far
With a little bit of modification, we can adjust the file we created earlier, mysql_form.php to display all the comments submitted so far
A simple PHP/MySQL form Please enter your name and a comment Name : Comment : Here are the comments submitted so far
In our extended example, we’re making use of the ORDER BY and LIMIT keywords to show only the 10 latest comments, in order from newest to oldest.
If you try out the code above, you should see the same form on the page mysql_form.php, and underneath, a list of all the comments you’ve added. If you try adding a new one, and then return to this page, you should see it appear at the top of the list.
On the next page, we’re going to expand on our example, and see how it’s possible to add in some administration priveleges to a select group of people using the power of PHP and MySQL
