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
<?php include("db_connect.php"); $username = $_POST['username']; $comment = $_POST['comment']; if(get_magic_quotes_gpc()){ $username = stripslashes($username); $comment = stripslashes($comment); } $username = mysql_real_escape_string($username); $comment = mysql_real_escape_string($comment); $result = mysql_query("INSERT INTO comments (name, comment) VALUES ('$username', '$comment')"); if($result == true) { echo "The comment was added successfully"; } else { echo "The comment could not be added, there was an error"; } ?> <br/><a href="mysql_form.php">Go back to the comments page</a>
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)
<?php $link = mysql_connect('localhost', 'root', 'pass'); //Connects to the database at "localhost" mysql_select_db ('test', $link); //Assuming you have a database named "test" set up ?>
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
<?php include("db_connect.php"); $username = $_POST['username']; $comment = $_POST['comment']; if(get_magic_quotes_gpc()){ $username = stripslashes($username); $comment = stripslashes($comment); } $username = mysql_real_escape_string($username); $comment = mysql_real_escape_string($comment); if(strlen($username) == 0 || strlen($comment) == 0){ echo "You didn't enter all the data. Please go back and retry"; } else { $result = mysql_query("INSERT INTO comments (name, comment) VALUES ('$username', '$comment')"); if($result == true) { echo "The comment was added successfully"; } else { echo "The comment could not be added, there was an error"; } } ?> <br/><a href="mysql_form.php">Go back to the comments page</a>
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
<?php include("db_connect.php"); $comments = mysql_query("SELECT * FROM comments ORDER BY id DESC LIMIT 0, 10"); ?> 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> <h2>Here are the comments submitted so far</h2> <?php if($row = mysql_fetch_array($comments)){ do { echo "<h3>{$row['name']}</h3>"; echo "{$row['comment']}"; } while ($row = mysql_fetch_array($comments)); } else { echo "There are no comments"; } ?>
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
September 14th, 2008 at 9:34 am
nice to see beginning……..
September 15th, 2008 at 1:15 pm
It’s insecure? For beginners out there this is not a secure example to PHP, maybe the owner would like to explain about SQL injections?
September 15th, 2008 at 1:30 pm
@Ben
Thanks for the comment, that’s a perfectly valid point - perhaps I should make it clearer in the article, but I have mentioned near the end…
“Note that I’ve intentionally left out some important security concerns in this example, which I will outline in detail in the next tutorial. These points are extremely important and I’d recommend reading through the tips in the next tutorial to get a good grasp of what they are and how they can affect your site”
This is referring to the article - http://www.roughguidetophp.com/cleaning-up-user-data-in-mysql - where I outline the basics behind preventing SQL injection attacks in the last section
Perhaps I’ll rewrite some sections of this article to make the point clearer though, and to emphasise the importance of preventing SQL injections with rewritten code to cover this issue (a very serious issue indeed, as you’ve mentioned, and to push the point further, yep, any beginners should be aware of such things when writing their scripts)
Update : Tutorial updated to reflect this
September 28th, 2008 at 7:26 pm
Thank you for this tutorial. Most tutorials that are for basic learning that someone like I can understand usually will have a work around such as the SQL Injection attacks.. Although this is a very good tutorial for me to run on my local box to get a result from learning this stuff. Very well written tutorial.
October 8th, 2008 at 10:05 am
Nice tutorial for beginners, thank you.
October 9th, 2008 at 12:31 am
Great Job.
Also I both agree & disagree with Ben’s comment.
I think the code is insecure however the educational benefit from reading this page is great. It is not for creating a profecional web site rather than learning the basic concepts of PHP.
I repeat … GREAT JOB.
Thanks
October 11th, 2008 at 7:54 am
Nice
October 24th, 2008 at 6:24 am
I just have a little problem near the end here… when I put
INSERT INTO `users` (`username`, `password`)
VALUES (`admin`, MD5( `pass` ));
into the database, it says:
#1054 - Unknown column ‘admin’ in ‘field list’
November 9th, 2008 at 8:54 pm
@Morgan
To fix this you want to use ` quotes for the table name and fields and ‘ quotes for the value names (ie admin and password) I changed it to INSERT INTO `users` (`username`, `password`)
VALUES (’admin’, MD5( ‘pass’ )); .
you can find some hints here:http://www.dech.co.uk/2006/02/mysql-trouble-fix-unknown-column-in-field-list/
hope this helps,
Brandon