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

  1. Log into your cPanel and click the phpMyAdmin icon
  2. 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".
  3. In the top menu, click "Insert"
  4. Type in a sample comment (refer to our screenshot below) and then click GO
    inserting-a-test-record-in-phpmyadmin
  5. 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.
    sample-insert-query-in-phpmyadmin

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:

  1. Create a file named manage_comments.php
  2. Paste in the sample code above
  3. 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:
showing-all-files-in-one-folder

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.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve our Support Center:
Email Address
Optional, but our team may contact you for more information.
Like this Article?

Comments

Post a comment
2013-09-11 8:54 am
"After you have ran" should be "After you have run"...
Staff
10,655 Points
2013-09-13 3:03 pm
Hello Dubosqued,

Thanks for the input! Your correction has been applied!

Regards,
Arnel C.
n/a Points
2014-03-22 7:41 pm

Thank your for this but i get always invalid article.

I tryed delete articlied process but still can not insert a new comment in a database.

This is mine php code.

<?

  if( $_POST )

  {
  
  $con = mysql_connect("mysql1.000webhost.com","db_user","db_pass","comments");

  if (!$con)
  {
    die('Could not connect: ' . mysql_error());
  }

  mysql_select_db("db_user", $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);

  $query = "

  INSERT INTO `db_user`.`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);

  }  

?>
Staff
4,324 Points
2014-03-24 9:39 am
This code should appropriately insert data into a database. What is the specific error you are getting?
n/a Points
2014-03-28 2:11 pm

invalid article id

This error i get all time.

Staff
5,671 Points
2014-03-28 2:53 pm
Hello Amir,

It looks like you've got an extra entry in your MySQL connect line, here is yours:

$con = mysql_connect("mysql1.000webhost.com","db_user","db_pass","comments");


While the original mentioned in this article is:

$con = mysql_connect("localhost","inmoti6_myuser","mypassword");


It appears the code that you have pasted in the comment above is not the full PHP script detailed in this guide. As you seem to be missing this section of the code dealing with throwing that error invalid article id:

 $articleid = $_GET['id'];
if( ! is_numeric($articleid) )
die('invalid article id');


If you've removed this line, the script should still function and place a comment in the database. You might want to double-check to ensure that you've setup a database to handle form data correctly, and also that you properly create a HTML form to get user comments.

- Jacob

Post a Comment

Name:
Email Address:
Comment:
Are you a bot?
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!