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 this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Like this Article?
2013-09-11 8:54 am
"After you have ran" should be "After you have run"...
Staff
15,484 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
7,372 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
9,521 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
n/a Points
2014-05-05 8:00 am

COULD NOT CONNECT MYSQL_ERROR

HELP ME BRO

 

 

<?php

if(isset($_POST['form']))

$con=mysql_connect("localhost","root","");

$db="dum";

if

(!$con)

{

die("could not connect:mysql_error()");

}

if (mysql_query("CREATE DATABASE dum;$con"))

{

echo"your Database created which name is:dum";

}

else

{

echo"Error creating database:".mysql_error();

}

//create table

if(!$con)

mysql_select_db("dum",$con);

{

die('could not connect:'.mysql_error());

}

if(! get_magic_quotes_gpc() )

$sql="CREATE TABLE 

(

name VARCHAR (50);

destination VACHAR (30);

sal int(10);

qualificaqtion VARCHAR (35);

)";

mysql_query($dum,$con);

echo"your table created which is as follows";

 

//insert data

 

{

$name=$_POST['name'];

$destinatiion=$_POST['destination'];

$sal=$_POST['sal'];

$qualification=$_POST['qualification'];

 

 

$sql="INSERT INTO $gum(id,name,destination,sal,qualification)

VALUES('$name','$destionation','$sal','$qualification')";

 

mysql_query($query);

 

if(! $retval )

{

  die('Could not enter data: ' . mysql_error());

}

echo "Entered data successfully\n";

 

}

 

mysql_close($con)

?>

<table border="1">

<tr>

<td align="center">Form Input Employees data</td>

</tr>

<tr>

<td>

<table>

<form method="post"actio="post">

<tr>

<td>Name</td>

<td><input type="text"name="name"size="50">

</td>

</tr>

<tr>

<td>Destination</td>

<td><input type="text"name="Destination"size="30">

</td>

</tr>

<tr>

<td>sal</td>

<td><input type="text"name="sal"size="10">

</td>

</tr>

<tr>

<td>Qualification</td>

<td><input type="text"name="Qualification"size="35" >

</td>

</tr>

<tr>

<td></td>

<td align="right"><input type="submit"

name="submit"value="sent" ></td>

</tr>

</table>

</td>

</tr>

</table>

 

</body>

</html><?php

if(isset($_POST['form']))

$con=mysql_connect("localhost","root","");

$db="dum";

if

(!$con)

{

die("could not connect:mysql_error()");

}

if (mysql_query("CREATE DATABASE dum;$con"))

{

echo"your Database created which name is:dum";

}

else

{

echo"Error creating database:".mysql_error();

}

//create table

if(!$con)

mysql_select_db("dum",$con);

{

die('could not connect:'.mysql_error());

}

if(! get_magic_quotes_gpc() )

$sql="CREATE TABLE 

(

name VARCHAR (50);

destination VACHAR (30);

sal int(10);

qualificaqtion VARCHAR (35);

)";

mysql_query($dum,$con);

echo"your table created which is as follows";

 

//insert data

 

{

$name=$_POST['name'];

$destinatiion=$_POST['destination'];

$sal=$_POST['sal'];

$qualification=$_POST['qualification'];

 

 

$sql="INSERT INTO $gum(id,name,destination,sal,qualification)

VALUES('$name','$destionation','$sal','$qualification')";

 

mysql_query($query);

 

if(! $retval )

{

  die('Could not enter data: ' . mysql_error());

}

echo "Entered data successfully\n";

 

}

 

mysql_close($con)

?>

<table border="1">

<tr>

<td align="center">Form Input Employees data</td>

</tr>

<tr>

<td>

<table>

<form method="post"actio="post">

<tr>

<td>Name</td>

<td><input type="text"name="name"size="50">

</td>

</tr>

<tr>

<td>Destination</td>

<td><input type="text"name="Destination"size="30">

</td>

</tr>

<tr>

<td>sal</td>

<td><input type="text"name="sal"size="10">

</td>

</tr>

<tr>

<td>Qualification</td>

<td><input type="text"name="Qualification"size="35" >

</td>

</tr>

<tr>

<td></td>

<td align="right"><input type="submit"

name="submit"value="sent" ></td>

</tr>

</table>

</td>

</tr>

</table>

 

</body>

</html>

Staff
15,308 Points
2014-05-05 1:06 pm
Hello Shams,

Running the code you provided, the first line evaluates as False. This means it will not run the second line of code, which is where your connection should set up. Since the connection is never set up nor connected it fails the check and gives the error code you see on the screen.

To learn more about this please check our article on Connecting to a Database using PHP.

Kindest Regards,
Scott M
n/a Points
2014-06-10 3:00 am

Hello, i have used this code and after pressing the submit button the query is executed and displays the message "Thank You For Your Comment " but when i open my datatbase the  data is not inserted into table, everything is correct but still data is not inserted...???? help??

Staff
7,372 Points
2014-06-10 8:19 am
Could you provide us with the code that is causing the issue?
n/a Points
2014-06-11 10:03 am

Can't figure out what's going on here. It's a singe page, single topic setup. I've embeded the code for manage_comments.php straight into index.html - and it all seems to work, except for actually putting the comment into the database. It does say "Thank you for your Comment! - but there's no database entry. Any ideas?

<?

                                                if( $_POST )

                                                {

                                                 $con = mysql_connect("localhost","xxxxx","yyyyy");

 

                                                  if (!$con)

                                                  {

                                                    die('Could not connect: ' . mysql_error());

                                                  }

 

                                                  mysql_select_db("databasename", $con);

 

                                                  $users_comment = $_POST['comment'];

 

                                                  $users_comment = mysql_real_escape_string($users_comment);

                                                  $query = "

                                                  INSERT INTO `databasename`.`comments` (`id`,`timestamp`, `comment`) VALUES (DEFAULT, CURRENT_TIMESTAMP, '$users_comment');";

 

                                                  mysql_query($query);

 

                                                  echo "<h2>Thank you for your Comment!</h2>";

 

                                                  mysql_close($con);

                                                }

                                                ?>

                                        <?php include('formcode.php'); ?>

 

The database has 3 columns. ID (whcih auto-increments with DEFAULT), timestamp, and the actualy comment. Nothing gets populated, no error message. Just the flash on the screen saying "Thank you for your Comment". 

Staff
7,372 Points
2014-06-11 11:28 am
Looking over your code, it looks fine. To further debug the issue, I recommend that you echo out the $query variable that you have set and take a look at it. If it looks fine, try to run it through PHPMyAdmin and see what happens.
n/a Points
2014-06-16 4:22 am

im trying to add data to my database bt its not. kindly assist guys

<?php

 

require_once('connect.php');

//receive values from form and assign them to variable

$school_id = $_POST['school_id'];

$sch_name = $_POST['sch_name'];

$team = $_POST['team'];

$amount = $_POST['amount'];

$telephone = $_POST['telephone'];

$year = $_POST['year'];

 

//execute insert statement

$mydata ="insert into registration (school_id,sch_name,team,amount,telephone,year)

values('$school_id','$sch_name','$team','$amount','$telephone','$year')";

if(!mysql_query($mydata))

{

echo "Record not added into the table";

}

else

echo "Recorded added successfully!";

 

 

mysql_close($mydata);

 

?>

This site has no rating
Staff
7,372 Points
2014-06-16 10:08 am
Could you clarify what exactly happens when you attempt to run your script?
n/a Points
2014-07-01 11:09 am

hi - everything seems to be working, but the rows that get inserted into mysql database are just blank, here is the code i'm using:

 

<? if( $_POST ) { $con =mysql_connect("localhost","amitpatel","denlax11"); if (!$con) {die('Could not connect: ' . mysql_error()); }mysql_select_db("Inquiries", $con); $users_firstname =$_POST['FNAME']; $users_lastname = $_POST['LNAME']; $users_email =$_POST['EMAIL']; $users_comments = $_POST['COMMENTS'];$users_firstname = mysql_real_escape_string($users_firstname);$users_lastname = mysql_real_escape_string($users_lastname);$users_email = mysql_real_escape_string($users_email); $users_comments= mysql_real_escape_string($users_comments); $query = " INSERT INTO`Inquiries`.`Inquiries` (`IDKEY`, `FNAME`, `LNAME`, `EMAIL`,`COMMENTS`, `DATE`) VALUES (NULL, '$users_firstname','$users_lastname', '$users_email', '$users_comments',CURRENT_TIMESTAMP);"; mysql_query($query); echo "<h2>Thank you foryour Comment!</h2>"; mysql_close($con); } ?>

 

also - what does the $users mean before the form id fields? do i need to change that to something else?

 

Thanks!

Staff
15,308 Points
2014-07-01 11:34 am
Hello amit,

I would have your code echo out the query to ensure they are getting populated. Also make sure you error check the query as well for any information it may be putting out.

Kindest Regards,
Scott M
n/a Points
2014-07-01 11:36 am

hi scott - thanks for the quick response. i'm pretty newb to this so can you elaborate on what you mean by have my code echo out the query?

 

THanks!

Staff
15,308 Points
2014-07-01 12:02 pm
Hello Amit,
What you want to do is to have a statement like the one below after you set your $query variable:
echo $query;

This should print out the full query statement that is being run against the database. It will show you whether or not the variable fields are being populated before the insert.

Kindest Regards,
Scott M
n/a Points
2014-07-15 2:42 am

This code should appropriately insert data into a database. What is the specific error you are getting?

n/a Points
2014-07-21 4:10 am

Hi guys, I'm very interested in PHP after looking at this tutorial and I decided to a small project for myself. I have created my own Web Control Interface and setup hardware already, by the way, I'm using Raspberry PI as my hardware.

The main issues right now is that I don't know why my INSERT method won't works and when I check mySQL database, is empty. Hope you guys could help me out with it as soon as possible, thanks in advance, guys.

This is my control.php:

<?php

error_reporting(E_ALL);

ini_set('display_errors','On');

 

session_start();

 

$MySQLUsername = "gpio";

$MySQLPassword = "kang";

 

$MySQLHost = "localhost";

$MySQLDB = "gpio";

 

If (($MySQLUsername == "USERNAME HERE") || ($MySQLPassword == "PASSWORD HERE")){

print 'ERROR - Please set up the script first';

exit();

}

 

$dbConnection = mysql_connect($MySQLHost, $MySQLUsername, $MySQLPassword);

mysql_select_db($MySQLDB, $dbConnection);

If (isset($_POST['action'])){

If ($_POST['action'] == "setPassword"){

$password1 = $_POST['password1'];

$password2 = $_POST['password2'];

If ($password1 != $password2){

header('Location: control.php');

}

$password = mysql_real_escape_string($_POST['password1']);

If (strlen($password) > 28){

mysql_close();

header('location: control.php');

}

$resetQuery = "SELECT username, salt FROM users WHERE username = 'admin';";

$resetResult = mysql_query($resetQuery);

If (mysql_num_rows($resetResult) < 1){

mysql_close();

header('location: control.php');

}

$resetData = mysql_fetch_array($resetResult, MYSQL_ASSOC);

$resetHash = hash('sha256', $salt . hash('sha256', $password));

$hash = hash('sha256', $password);

function createSalt(){

$string = md5(uniqid(rand(), true));

return substr($string, 0, 8);

}

$salt = createSalt();

$hash = hash('sha256', $salt . $hash);

mysql_query("UPDATE users SET salt='$salt' WHERE username='admin'");

mysql_query("UPDATE users SET password='$hash' WHERE username='admin'");

mysql_close();

header('location: control.php');

}

}

If ((isset($_POST['username'])) && (isset($_POST['password']))){

$username = mysql_real_escape_string($_POST['username']);

$password = mysql_real_escape_string($_POST['password']);

$loginQuery = "SELECT UserID, password, salt FROM users WHERE username = '$username';";

$loginResult = mysql_query($loginQuery);

If (mysql_num_rows($loginResult) < 1){

mysql_close();

header('location: control.php?error=incorrectLogin');

}

$loginData = mysql_fetch_array($loginResult, MYSQL_ASSOC);

$loginHash = hash('sha256', $loginData['salt'] . hash('sha256', $password));

If ($loginHash != $loginData['password']){

mysql_close();

header('location: control.php?error=incorrectLogin');

} else {

session_regenerate_id();

$_SESSION['username'] = "admin";

$_SESSION['userID'] = "1";

mysql_close();

header('location: control.php');

}

}

If ((!isset($_SESSION['username'])) || (!isset($_SESSION['userID']))){

print '

<html>

<head>

<title>GPIO Control - Login</title>

</head>

<body>

<table border="0" align="center">

<form name="login" action="control.php" method="post">

<tr>

<td>Username: </td><td><input type="text" name="username"></td>

</tr>

<tr>

<td>Password: </td><td><input type="password" name="password"></td>

</tr>

<tr>

<td colspan="2" align="center"><input type="submit" value="Log In"></td>

</tr>

</form>

</table>

</body>

</html>

';

die();

}

If (isset($_GET['action'])){

If ($_GET['action'] == "logout"){

$_SESSION = array();

session_destroy();

header('Location: control.php');

} else If ($_GET['action'] == "setPassword"){

print '

<form name="changePassword" action="control.php" method="post">

<input type="hidden" name="action" value="setPassword">

<p>Enter New Password: <input type="password" name="password1">  Confirm: <input type="password" name="password2"><input type="submit" value="submit"></p>

</form>

';

} else {

$action = $_GET['action'];

$storing = $_POST['storing'];

$pin = mysql_real_escape_string($_GET['pin']);

if ($action == "turnOn"){

$setting = "1";

mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");

mysql_close();

header('Location: control.php');

} else If ($action == "turnOff"){

$setting = "0";

mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");

mysql_close();

header('Location: control.php');

} else If ($storing == "turnOn"){

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '1', 'Red LED', NOW(), '') FROM pinStatus WHERE pinNumber='4';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '2', 'Blue LED', NOW(), '') FROM pinStatus WHERE pinNumber='17';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '3', 'LED', NOW(), '') FROM pinStatus WHERE pinNumber='18';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '4', 'LED', NOW(), '') FROM pinStatus WHERE pinNumber='21';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '5', 'LED', NOW(), '') FROM pinStatus WHERE pinNumber='22';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '6', 'Green LED', NOW(), '') FROM pinStatus WHERE pinNumber='23';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '7', 'LED', NOW(), '') FROM pinStatus WHERE pinNumber='24';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '8', 'LED', NOW(), '') FROM pinStatus WHERE pinNumber='25';");

mysql_close();

header('Location: control.php');

} else If ($storing == "turnOff"){

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '1', 'Red LED', '', NOW()) FROM pinStatus WHERE pinNumber='4';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '2', 'Blue LED', '', NOW()) FROM pinStatus WHERE pinNumber='17';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '3', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='18';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '4', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='21';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '5', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='22';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '6', 'Green LED', '', NOW()) FROM pinStatus WHERE pinNumber='23';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '7', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='24';");

mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '8', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='25';");

mysql_close();

header('Location: control.php');

} else IF ($action =="edit"){

$pin = mysql_real_escape_string($_GET['pin']);

$query = mysql_query("SELECT pinDescription FROM pinDescription WHERE pinNumber='$pin';");

$descRow = mysql_fetch_assoc($query);

$description = $descRow['pinDescription'];

print '

<html><head><title>Update Pin ' . $pin . '</title></head><body>

<table border="0">

<form name="edit" action="control.php" method="get">

<input type="hidden" name="action" value="update">

<input type="hidden" name="pin" value="' . $pin . '">

<tr>

<td><p>Description: </p></td><td><input type="text" name="description" value="' . $description . '"></td><td><input type="submit" value="Confirm"></td>

</tr>

</form>

</table>

</body></html>

';

mysql_close();

} else IF ($action =="update"){

$pin = mysql_real_escape_string($_GET['pin']);

$description = mysql_real_escape_string($_GET['description']);

mysql_query("UPDATE pinDescription SET pinDescription='$description' WHERE pinNumber='$pin';");

header('Location: control.php');

} else {

header('Location: control.php');

}

}

} else {

print '

<html>

<head>

<title>GPIO Control</title>

</head>

<font face="verdana">

<p><h1>Sensors</h1></p>

';

$query = mysql_query("SELECT pinNumber, pinStatus FROM pinStatus;");

$query2 = mysql_query("SELECT pinNumber, pinDescription FROM pinDescription;");

$totalGPIOCount = mysql_num_rows($query);

$currentGPIOCount = 0;

print '<table name="GPIO" border="1" cellpadding="5">';

print '<tr><th>GPIO #</th><th>GPIO Description</th><th>Status</th><th>Action</th><th>Edit</th></tr>';

while ($currentGPIOCount < $totalGPIOCount){

$pinRow = mysql_fetch_assoc($query);

$descRow = mysql_fetch_assoc($query2);

$pinNumber = $pinRow['pinNumber'];

$pinStatus = $pinRow['pinStatus'];

$pinDescription = $descRow['pinDescription'];

If ($pinStatus == "0"){

$buttonValue = "Turn On";

$action = "turnOn";

$image = "off.jpg";

} else {

$buttonValue = "Turn Off";

$action = "turnOff";

$image = "on.jpg";

}

print '<tr>';

print '<td align="center">' . $pinNumber . '</td><td>' . $pinDescription . '</td><td align="center"><img src="' . $image . '" width="50"></td><td align="center" valign="middle"><form name="pin' . $pinNumber . 'edit" action="control.php" method="get"><input type="hidden" name="action" value="' . $action . '"><input type="hidden" name="pin" value="' . $pinNumber . '"><input type="submit" value="' . $buttonValue . '"></form></td><td><form name="pin' . $pinNumber . '" action="control.php" method="get"><input type="hidden" name="action" value="edit"><input type="hidden" name="pin" value="' . $pinNumber . '"><input type="submit" value="Edit"></form></td>';

print '</tr>';

$currentGPIOCount ++;

}

print '</table>';

mysql_close();

print '

<br><br>

<a href="/support/control.php?action=logout">Log Out</a>

</font>

</html>

';

}

?>

Staff
15,308 Points
2014-07-21 12:24 pm
Hello,

When working with databases as well as other important functions and tasks, error trapping is important. For instance, you will need to ensure that you are actually connecting to the database without issue. To do so, you need to test the connection return variable. If an error occurs, then you want to know that specific error. You simply need to check that variable as in the sample code below:
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}


You can do the same for each INSERT statement you have so if any of them return an error you will see it:
$success= mysql_query("INSERT INTO 'gpio'.'sensor' ('id', 'sensorId', 'sensor', 'switchOnLog', 'switchOffLog') VALUES (NULL, '8', 'LED', '', NOW()) FROM pinStatus WHERE pinNumber='25';");;
if (!$success) {
die('Could not Insert: ' . mysql_error());
}

As you can see, they are very similar and simple. Do this for all SQL statements including the connection so you can see any errors you are getting.

One last thing, you may want to look into mysqli instead of mysql when working in php. The mysql functions are older and will be removed in a future version.

Kindest Regards,
Scott M
n/a Points
2014-07-21 9:35 pm

Thanks Scott, I found out my mistake and I needed your help again cause I want to do subqueries but I just can't get it right.

This is my error message for mySQL:

Could not Insert: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT '4' FROM pinStatus WHERE pinNumber='4'' at line 1

This is my new edited code:

*** Extra code removed ***

mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");

$success=mysql_query("INSERT INTO sensor VALUES(NULL, '1', 'Red LED', NOW(), NULL) SELECT '4' FROM pinStatus WHERE pinNumber='4';");

if (!$success) {

die('Could not Insert: ' . mysql_error());

}

$success=mysql_query("INSERT INTO sensor VALUES(NULL, '2', 'Blue LED', NOW(), NULL) SELECT '17' FROM pinStatus WHERE pinNumber='17';");

if (!$success) {

die('Could not Insert: ' . mysql_error());

}

*** Extra Code Removed ***
Staff
15,308 Points
2014-07-22 9:24 am
Hello,

When constructing a Select statement, you are typically looking to pull a column name (or * meaning all information from the referenced tables). You are asking it to pull a 4 where the pinNumber ='4'. If you want the number 4 to be pulled you may want to say something like:
SELECT pinNumber FROM pinStatus WHERE pinNumber='4';


This will return the 4 you are looking for.

Kindest Regards,
Scott M
2014-07-22 1:56 am
Hi guys, I would like to seek help on my INSERT method to mySQL database. I manage to store data but it always store the 1st data if I click second or third button, it will record all instead of just record that data which I have switch on or off.

This is my control.php:
<?php
error_reporting(E_ALL);
ini_set('display_errors','On');

session_start();

$MySQLUsername = "gpio";
$MySQLPassword = "kang";

$MySQLHost = "localhost";
$MySQLDB = "gpio";

If (($MySQLUsername == "USERNAME HERE") || ($MySQLPassword == "PASSWORD HERE")){
print 'ERROR - Please set up the script first';
exit();
}

$dbConnection = mysql_connect($MySQLHost, $MySQLUsername, $MySQLPassword);
mysql_select_db($MySQLDB, $dbConnection);
If (isset($_POST['action'])){
If ($_POST['action'] == "setPassword"){
$password1 = $_POST['password1'];
$password2 = $_POST['password2'];
If ($password1 != $password2){
header('Location: control.php');
}
$password = mysql_real_escape_string($_POST['password1']);
If (strlen($password) > 28){
mysql_close();
header('location: control.php');
}
$resetQuery = "SELECT username, salt FROM users WHERE username = 'admin';";
$resetResult = mysql_query($resetQuery);
If (mysql_num_rows($resetResult) < 1){
mysql_close();
header('location: control.php');
}
$resetData = mysql_fetch_array($resetResult, MYSQL_ASSOC);
$resetHash = hash('sha256', $salt . hash('sha256', $password));
$hash = hash('sha256', $password);
function createSalt(){
$string = md5(uniqid(rand(), true));
return substr($string, 0, 8);
}
$salt = createSalt();
$hash = hash('sha256', $salt . $hash);
mysql_query("UPDATE users SET salt='$salt' WHERE username='admin'");
mysql_query("UPDATE users SET password='$hash' WHERE username='admin'");
mysql_close();
header('location: control.php');
}
}
If ((isset($_POST['username'])) && (isset($_POST['password']))){
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$loginQuery = "SELECT UserID, password, salt FROM users WHERE username = '$username';";
$loginResult = mysql_query($loginQuery);
If (mysql_num_rows($loginResult) < 1){
mysql_close();
header('location: control.php?error=incorrectLogin');
}
$loginData = mysql_fetch_array($loginResult, MYSQL_ASSOC);
$loginHash = hash('sha256', $loginData['salt'] . hash('sha256', $password));
If ($loginHash != $loginData['password']){
mysql_close();
header('location: control.php?error=incorrectLogin');
} else {
session_regenerate_id();
$_SESSION['username'] = "admin";
$_SESSION['userID'] = "1";
mysql_close();
header('location: control.php');
}
}
If ((!isset($_SESSION['username'])) || (!isset($_SESSION['userID']))){
print '
<html>
<head>
<title>GPIO Control - Login</title>
</head>
<body>
<table border="0" align="center">
<form name="login" action="control.php" method="post">
<tr>
<td>Username: </td><td><input type="text" name="username"></td>
</tr>
<tr>
<td>Password: </td><td><input type="password" name="password"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="Log In"></td>
</tr>
</form>
</table>
</body>
</html>
';
die();
}
If (isset($_GET['action'])){
If ($_GET['action'] == "logout"){
$_SESSION = array();
session_destroy();
header('Location: control.php');
} else If ($_GET['action'] == "setPassword"){
print '
<form name="changePassword" action="control.php" method="post">
<input type="hidden" name="action" value="setPassword">
<p>Enter New Password: <input type="password" name="password1"> Confirm: <input type="password" name="password2"><input type="submit" value="submit"></p>
</form>
';
} else {
$action = $_GET['action'];
$pin = mysql_real_escape_string($_GET['pin']);
$pinNo = mysql_query("SELECT pinNumber FROM pinStatus WHERE pinNumber='$pinNo';");
if ($action == "turnOn"){
$setting = "1";
mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");
if ($pinNo = '4'){
mysql_query("INSERT INTO sensor VALUES(NULL, '1', 'Red LED', NOW(), NULL);");
}
else if ($pinNo = '17'){
mysql_query("INSERT INTO sensor VALUES(NULL, '2', 'Blue LED', NOW(), NULL);");
}
else If ($pinNo = '18'){
mysql_query("INSERT INTO sensor VALUES(NULL, '3', 'LED', NOW(), NULL);");
}
else If ($pinNo = '21'){
mysql_query("INSERT INTO sensor VALUES(NULL, '4', 'LED', NOW(), NULL);");
}
else If ($pinNo = '22'){
mysql_query("INSERT INTO sensor VALUES(NULL, '5', 'LED', NOW(), NULL);");
}
else If ($pinNo = '23'){
mysql_query("INSERT INTO sensor VALUES(NULL, '6', 'Green LED', NOW(), NULL);");
}
else If ($pinNo = '24'){
mysql_query("INSERT INTO sensor VALUES(NULL, '7', 'LED', NOW(), NULL);");
}
else If ($pinNo = '25'){
mysql_query("INSERT INTO sensor VALUES(NULL, '8', 'LED', NOW(), NULL);");
}
mysql_close();
header('Location: control.php');
} else If ($action == "turnOff"){
$setting = "0";
mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");
if ($pinNo = "4"){
mysql_query("INSERT INTO sensor VALUES(NULL, '1', 'Red LED', NULL, NOW());");
}
else if ($pinNo = "17"){
mysql_query("INSERT INTO sensor VALUES(NULL, '2', 'Blue LED', NULL, NOW());");
}
else If ($pinNo = '18'){
mysql_query("INSERT INTO sensor VALUES(NULL, '3', 'LED', NULL, NOW());");
}
else If ($pinNo = '21'){
mysql_query("INSERT INTO sensor VALUES(NULL, '4', 'LED', NULL, NOW());");
}
else If ($pinNo = '22'){
mysql_query("INSERT INTO sensor VALUES(NULL, '5', 'LED', NULL, NOW());");
}
else If ($pinNo = '23'){
mysql_query("INSERT INTO sensor VALUES(NULL, '6', 'Green LED', NULL, NOW());");
}
else If ($pinNo = '24'){
mysql_query("INSERT INTO sensor VALUES(NULL, '7', 'LED', NULL, NOW());");
}
else If ($pinNo = '25'){
mysql_query("INSERT INTO sensor VALUES(NULL, '8', 'LED', NULL, NOW());");
}
mysql_close();
header('Location: control.php');
} else IF ($action =="edit"){
$pin = mysql_real_escape_string($_GET['pin']);
$query = mysql_query("SELECT pinDescription FROM pinDescription WHERE pinNumber='$pin';");
$descRow = mysql_fetch_assoc($query);
$description = $descRow['pinDescription'];
print '
<html><head><title>Update Pin ' . $pin . '</title></head><body>
<table border="0">
<form name="edit" action="control.php" method="get">
<input type="hidden" name="action" value="update">
<input type="hidden" name="pin" value="' . $pin . '">
<tr>
<td><p>Description: </p></td><td><input type="text" name="description" value="' . $description . '"></td><td><input type="submit" value="Confirm"></td>
</tr>
</form>
</table>
</body></html>
';
mysql_close();
} else IF ($action =="update"){
$pin = mysql_real_escape_string($_GET['pin']);
$description = mysql_real_escape_string($_GET['description']);
mysql_query("UPDATE pinDescription SET pinDescription='$description' WHERE pinNumber='$pin';");
header('Location: control.php');
} else {
header('Location: control.php');
}
}
} else {
print '
<html>
<head>
<title>GPIO Control</title>
</head>
<font face="verdana">
<p><h1>Sensors</h1></p>
';
$query = mysql_query("SELECT pinNumber, pinStatus FROM pinStatus;");
$query2 = mysql_query("SELECT pinNumber, pinDescription FROM pinDescription;");
$totalGPIOCount = mysql_num_rows($query);
$currentGPIOCount = 0;
print '<table name="GPIO" border="1" cellpadding="5">';
print '<tr><th>GPIO #</th><th>GPIO Description</th><th>Status</th><th>Action</th><th>Edit</th></tr>';
while ($currentGPIOCount < $totalGPIOCount){
$pinRow = mysql_fetch_assoc($query);
$descRow = mysql_fetch_assoc($query2);
$pinNumber = $pinRow['pinNumber'];
$pinStatus = $pinRow['pinStatus'];
$pinDescription = $descRow['pinDescription'];
If ($pinStatus == "0"){
$buttonValue = "Turn On";
$action = "turnOn";
$image = "off.jpg";
} else {
$buttonValue = "Turn Off";
$action = "turnOff";
$image = "on.jpg";
}
print '<tr>';
print '<td align="center">' . $pinNumber . '</td><td>' . $pinDescription . '</td><td align="center"><img src="' . $image . '" width="50"></td><td align="center" valign="middle"><form name="pin' . $pinNumber . 'edit" action="control.php" method="get"><input type="hidden" name="action" value="' . $action . '"><input type="hidden" name="pin" value="' . $pinNumber . '"><input type="submit" value="' . $buttonValue . '"></form></td><td><form name="pin' . $pinNumber . '" action="control.php" method="get"><input type="hidden" name="action" value="edit"><input type="hidden" name="pin" value="' . $pinNumber . '"><input type="submit" value="Edit"></form></td>';
print '</tr>';
$currentGPIOCount ++;
}
print '</table>';
mysql_close();
print '
<br><br>
<a href="control.php?action=logout">Log Out</a>
</font>
</html>
';
}
?>
Staff
15,308 Points
2014-07-22 9:37 am
Hello aoiregion,

This sounds like a logic issue and not a syntax issue. Being a logic issue I would need to run the code against a database to figure out where the code is incorrect. That is not something we can do from here.

However, you should have the code echo out (print) the query to the screen so you can see which one it is firing off. Once you know which query it is sending, you can then check the code to see why it is setting those particular conditions. It can take many tries until you iron it out, but that is part of the fun of coding!

Kindest Regards,
Scott M

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

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

25 Questions & Comments

Post a comment

Back to first comment | top

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!