In this tutorial series, we’re using PHP and MySQL together to create a comment form so we can receive and display user comments on a website. We’ve already setup our database and our HTML form, and so our next step is to actually write the user’s comment to the database once it has been submited.
Step 1 – Determining the correct SQL Insert command
When you write data to a database, you use SQL statements, specifically the INSERT command. It is straightforward, the INSERT command inserts data into the database. When you use phpMyAdmin, you use a GUI to manage your database, but it also shows you the MySQL commands that it ran when performing your requested tasks. We will use this feature to our advantge to find the correct code to use. What we will do is insert a test comment using phpMyAdmin, and then copy the INSERT command it used.
To INSERT using phpMyAdmin
- Log into your cPanel and click the phpMyAdmin icon
- In the left menu, first click your database name and then click the table to work with. If you’re following our example, we’ll first click on “_mysite” and then “comments”.
- In the top menu, click “Insert”
- Type in a sample comment (refer to our screenshot below) and then click GO
- After you have run the query, phpMyAdmin will display the insert command it used (see the screenshot below). Copy this SQL statement to a temporary location, such as a text file on your computer.
Step 2 – Writing the PHP code that will execute MySQL Query
Now that we have a sample query, we need to modify it and run in once a user has submitted a comment. Below is example code that will do this. If you’re not familiar with php, any line that begins with // is a comment. It is intended for programmers to leave comments about what their code is doing so that either themselves or other people who work on the code have an idea as to what the code is doing. In the example below, we’ve put in comments explaining what exactly certain peicies of code are doing:
<?
// When someone submits a comment, they "POST" the comment to the server.
// Therefore, we only want to insert a comment to the database if there
// is POST data. The if statement below checks to see if someone has
// posted data to the page
if( $_POST )
{
// At this point in the code, we know someone has posted data and
// is trying to post a comment. We therefore need to now connect
// to the database
// Below we are setting up our connection to the server. Because
// the database lives on the same physical server as our php code,
// we are connecting to "localhost". inmoti6_myuser and mypassword
// are the username and password we setup for our database when
// using the "MySQL Database Wizard" within cPanel
$con = mysql_connect("localhost","inmoti6_myuser","mypassword");
// The statement above has just tried to connect to the database.
// If the connection failed for any reason (such as wrong username
// and or password, we will print the error below and stop execution
// of the rest of this php script
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// We now need to select the particular database that we are working with
// In this example, we setup (using the MySQL Database Wizard in cPanel) a
// database named inmoti6_mysite
mysql_select_db("inmoti6_mysite", $con);
// We now need to create our INSERT command to insert the user's
// comment into the database.
//
// Let's first take a look at the sample INSERT code we received when we
// used phpMyAdmin to create a test comment:
//
// INSERT INTO `inmoti6_mysite`.`comments` (`id`, `name`, `email`, `website`,
// `comment`, `timestamp`, `articleid`) VALUES (NULL, 'John Smith',
// 'johns@domain.com', 'johnsmith.com', 'This is a test comment.',
// CURRENT_TIMESTAMP, '1');
//
// If we ran this command, it would insert the same exact comment from John
// Smith every time. What we need to do is update this query so that it
// includes all of the data that the user typed in.
//
// When we setup our HTML Form, some of the text boxes we used were:
// <input type='text' name='name' id='name' />
// <input type='text' name='email' id='email' />
// The important information we need from this is the "id" that is set.
// For example, to get the user's name, we can grab the 'name'. To
// get their email address, we need to get the value of 'email'.
//
// Using the $_POST variable, we can get this data. This is what we're
// doing below
$users_name = $_POST['name'];
$users_email = $_POST['email'];
$users_website = $_POST['website'];
$users_comment = $_POST['comment'];
// We now have all of the data that the user inputed. What you don't want
// to do is trust the user's input. Savy users / hackers may attempt to use
// an sql injection attack in order to run sql statements that you did not
// intend to run. For example, the following is a basic query for checking
// someone's username and password:
//
// SELECT * FROM users WHERE user='USERNAME' AND password='PASSWORD'
//
// In the above, we're assuming the user typed USERNAME as their username and
// PASSWORD as their PASSWORD. But, what if the user typed the following as
// their password?
//
// ' OR ''='
//
// The new query would then be the following:
//
// SELECT * FROM users WHERE user='USERNAME' AND password='' OR ''=''
//
// Running the above query would allow anyone to login as any user! We can use
// the mysql_real_escape_string function to escape the user's input. If used in
// the above example, the new query would read:
//
// SELECT * FROM users WHERE user='USERNAME' AND password='\' OR \'\'=\''
//
// Because the single quotes are "escaped" (i.e. appended with a backslash), the
// hackers attempt would fail.
$users_name = mysql_real_escape_string($users_name);
$users_email = mysql_real_escape_string($users_email);
$users_website = mysql_real_escape_string($users_website);
$users_comment = mysql_real_escape_string($users_comment);
// We also need to get the article id, so we know if the comment belongs
// to page 1 or if it belongs to page 2. The article id is going to be
// passed in the URL. For example, looking at this URL:
//
// http://phpandmysql.inmotiontesting.com/page1.php?id=1
//
// The article id is 1. To get data from the url, use the $_GET variable,
// as in:
$articleid = $_GET['id'];
// We also want to add a bit of security here as well. We assume that the $article_id
// is a number, but if someone changes the URL, as in this manner:
// http://phpandmysql.inmotiontesting.com/page2.php?id=malicious_code_goes_here
// ... then they will have the potential to run any code they want in your
// database. The following code will check to ensure that $article_id is a number.
// If it is not a number (IE someone is trying to hack your website), it will tell
// the script to stop executing the page
if( ! is_numeric($articleid) )
die('invalid article id');
// At this point, we've grabbed all of the data that we need. We now need
// to update our SQL query. For example, instead of "John Smith", we'll
// use $users_name. Below is our updated SQL command:
$query = "
INSERT INTO `inmoti6_mysite`.`comments` (`id`, `name`, `email`, `website`,
`comment`, `timestamp`, `articleid`) VALUES (NULL, '$users_name',
'$users_email', '$users_website', '$users_comment',
CURRENT_TIMESTAMP, '$articleid');";
// Our SQL stated is stored in a variable called $query. To run the SQL command
// we need to execute what is in the $query variable.
mysql_query($query);
// We can inform the user to what's going on by printing a message to
// the screen using php's echo function
echo "<h2>Thank you for your Comment!</h2>";
// At this point, we've added the user's comment to the database, and we can
// now close our connection to the database:
mysql_close($con);
}
?>
Don’t let all of that code be intimidating! When we take out all of the comments, the code is much shorter and looks like this:
<?
if( $_POST )
{
$con = mysql_connect("localhost","inmoti6_myuser","mypassword");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("inmoti6_mysite", $con);
$users_name = $_POST['name'];
$users_email = $_POST['email'];
$users_website = $_POST['website'];
$users_comment = $_POST['comment'];
$users_name = mysql_real_escape_string($users_name);
$users_email = mysql_real_escape_string($users_email);
$users_website = mysql_real_escape_string($users_website);
$users_comment = mysql_real_escape_string($users_comment);
$articleid = $_GET['id'];
if( ! is_numeric($articleid) )
die('invalid article id');
$query = "
INSERT INTO `inmoti6_mysite`.`comments` (`id`, `name`, `email`, `website`,
`comment`, `timestamp`, `articleid`) VALUES (NULL, '$users_name',
'$users_email', '$users_website', '$users_comment',
CURRENT_TIMESTAMP, '$articleid');";
mysql_query($query);
echo "<h2>Thank you for your Comment!</h2>";
mysql_close($con);
}
?>
Step 3 – Placing our php code in our pages
Now that we have the php code to insert the comments into the database, we need to put the code into our pages (page1.php and page2.php). In our previous article, we showed you how to use php’s include function to help manage blocks of code effeciently, and we will again use the include function.
To incorporate our php code:
- Create a file named manage_comments.php
- Paste in the sample code above
- Update both page1.php and page2.php to include manage_comments.php by using
<? include(“manage_comments.php”); ?>
at the top of the file
At this time, we are now working with 4 different files, and they are all in the same directory:
Also, after incorporating <? include(“manage_comments.php”); ?>, our page1.php file now looks like this:
<? include("manage_comments.php"); ?>
<h1>This is page1.php</h1>
<div><a href='page2.php?id=2'>Click here</a> to go to page2.php</div>
<div style='margin:20px; width:100px; height:100px; background:blue;'></div>
<? include("formcode.php"); ?>
In our next article, we’ll take a look at what we’ve done thus far and test out our comment system to see exactly how it works.
SynapseIndia Php Development 16 