Using php to post user submitted info to mysql database

Question

I cannot get my code to work. I'm new to php/mysql but I am certain I am doing most things right here. I'm not getting any errors. With the code posted below, if I put info into the feilds and click the button the screen appears to refresh but when I check the mysql database I created, that info is not there. I know that my connection to the database is working because I have fake data already entered into the database and the webpage is pulling it over and displaying it just fine.

The db.php is a separate file that contains the formatted php code for connecting to the database (server, username, password), which I know works because that is how I pull the data into the webpage as well. And if it is needed, my server is set up for php 4.0.10.7 and I can't change that unfortunately.

So, here is my code:

<div class="a" id="add_customer">
<form id="customerdata" name="customerdata">
<input type="text" align="center" id="name" name="NAME" placeholder="Customer Name">
<input type="text" align="center" id="address" name="ADDRESS" placeholder="Address">
<b>Paid?:</b>
<select id="PAID" name="PAID">
<option value="select">Make a Selection</option>
<option value="yes">Yes</option>
<option value="no">No</option>
</select>
<input type="text" align="center" id="comments" name="COMMENTS" placeholder="Comments">
<input type="submit" id="submit" name="submit" value="Add Customer">
</form>
</div>


<?php
if(isset($_POST['submit']))
{
include('db.php');
$database="mysql_database";

$con = mysql_connect($server,$username,$password);

$sql="INSERT INTO mysql_database (NAME, ADDRESS, PAID, COMMENTS)
VALUES
('$_POST[NAME]','$_POST[ADDRESS]','$_POST[PAID]','$_POST[COMMENTS]')";
$a=mysql_query($sql);

if (!$a)
{
die("Error addding record. " . mysql_error());
}
else
{
echo "1 record added";
}
mysql_close($con);
}
?>

Just confirming in your code snippet, you've got your database name the same as your table name? — Menztrual, Aug 05 '15 at 07:12
Is your column name really in upper case? Column names are case sensitive. As well as the passed-on values. — Logan Wayne, Aug 05 '15 at 07:15
@BrendanScarvell actually, no. They are different. I edited that info out and apparently didn't put the correct info there. It should be mysql_table_name I suppose. But even with the correct info in the correct places it is still just refreshing my screen but no adding anything to the database. — Rage, Aug 05 '15 at 07:15
attributes inside $_POST should be included with quotations. — , Aug 05 '15 at 07:18
@LoganWayne Yes, I did check the column names. I have done a lot of reading in the last 6 hours and found that pitfall early on. — Rage, Aug 05 '15 at 07:18
@Rage : I would suggest : `...VALUES (' ".$_POST['NAME']." ',...` (Because you are already using both quotations in your query, so to avoid any conflict) — , Aug 05 '15 at 07:22

score 1 · Accepted Answer · answered Aug 05 '15 at 07:17

<div class="a" id="add_customer">
<form id="customerdata" name="customerdata" method="post">
<input type="text" align="center" id="name" name="name" placeholder="Customer Name">
<input type="text" align="center" id="address" name="address" placeholder="Address">
<b>Paid?:</b>
<select id="paid" name="paid">
<option value="select">Make a Selection</option>
<option value="yes">Yes</option>
<option value="no">No</option>
</select>
<input type="text" align="center" id="comments" name="comments" placeholder="Comments">
<input type="submit" id="submit" name="submit" value="Add Customer">
</form>
</div>


<?php
if(isset($_POST['submit']))
{
include('db.php');
$database="mysql_database";

$con = mysql_connect($server,$username,$password);
$name = $_POST['name'];
$address= $_POST['address'];
$paid= $_POST['paid'];
$comments= $_POST['comments'];
$sql="INSERT INTO mysql_database (NAME, ADDRESS, PAID, COMMENTS)
VALUES
('$name','$address','$paid','$comments')";
$a=mysql_query($sql);

if (!$a)
{
die("Error addding record. " . mysql_error());
}
else
{
echo "1 record added";
}
mysql_close($con);
}
?>

Everyone submitted awesome advice. I've got a lot more reading to do to fully learn it all. — Rage, Aug 05 '15 at 08:10

bnahin · Answer 2 · 2015-08-05T07:26:03.873

mysql_* functions are deprecated, use mysqli_*.
Inputs are not sanitized or escaped, therefore vulnerable to SQL injection.
You are referencing the POST variables incorrectly. In your code, you are literally inserting '$_POST[NAME]'.
What table are you inserting into? That should be after INSERT INTO.

You should set all of the sanitized POST inputs into separate variables, however, this is what your current code could look like:

$con = mysqli_connect($server,$username,$password,$database);

$sql="INSERT INTO table_name (NAME, ADDRESS, PAID, COMMENTS)
VALUES
('".mysqli_real_escape_string($_POST['NAME'])."','".mysqli_real_escape_string($_POST['ADDRESS']."','".mysqli_real_escape_string($_POST['PAID']."','".mysqli_real_escape_string($_POST['COMMENTS']."')";
$a=mysqli_query($con,$sql);

if ($a)
{
echo "1 record added";
}
mysqli_close($con);

aldrin27 · Answer 3 · 2015-08-05T07:28:36.027

<?php
  include('db.php'); //include db first

  $con = mysql_connect($server,$username,$password); //connect to db

   if(isset($_POST['submit'])) {
     $database="mysql_database"; //I think this will not work.
     $sql="INSERT INTO $database (NAME, ADDRESS, PAID, COMMENTS) VALUES ('$_POST[NAME]','$_POST[ADDRESS]','$_POST[PAID]','$_POST[COMMENTS]')";
//Try this
$sql = "INSERT INTO (your_db_name) (NAME, ADDRESS, PAID, COMMENTS) VALUES ('".$_POST['name']."','".$_POST['address']."','".$_POST['paid']."','".$_POST['comments']."')"; //I used concantenation here

     $a=mysql_query($sql);
   }

   if (!$a) {
     die("Error addding record. " . mysql_error());
   }else{
     echo "1 record added";
   }
   mysql_close($con);
?>
Try this piece of code.

score 0 · Answer 4 · edited May 23 '17 at 11:44

You did not include the database when establishing your connection. Do this:

$con = mysql_connect($server,$username,$password,$database);

Then on your INSERT query, instead of database name, change it to your table name

INSERT INTO mysql_table_name...

And use mysqli_real_escape_string() to prevent SQL injections before you bind values to your query.

$name = mysqli_real_escape_string($con,$_POST["NAME"]);

And take note that mysql_* API is already deprecated and you should be using mysqli prepared statement instead.

You can try the code below if you want to move to prepared statement. No need to sanitize manually the values, prepared statement will do this for you.

<?php

  /* ESTABLISH YOUR CONNECTION */
  $con = new mysqli($server,$username,$password,$database);

  if($stmt = $con->prepare("INSERT INTO mysql_table_name (NAME, ADDRESS, PAID, COMMENTS) VALUES (?,?,?,?)")){ /* CHECK IF THE QUERY IS TRUE */
    $stmt->bind_param("ssss",$_POST["NAME"],$_POST["ADDRESS"],$_POST["PAID"],$_POST["COMMENTS"]); /* BIND THE PASSED ON VALUES TO YOUR QUERY */
    $stmt->execute(); /* EXECUTE THE QUERY */
    $stmt->close();
  } /* END OF PREPARED STATEMENT */

?>

score 0 · Answer 5 · answered Aug 05 '15 at 07:32

Do this changes

MySQL Insert function
```
INSERT INTO <tbale_name> ......
```
MySQL DB Select
```
mysql_select_db('database_name')
```
Make sure table name and Database name is correct (mysql_database)
make sure include('db.php'); contain $server, $username, $password variables defined
Make sure input field name="" are Capital Letters(name="NAME" or name="Name"). Bcz You use define $_POST[NAME] as Capitalized.
Don't just use $_POST[NAME], This will be cause to SQL Injection. Use mysql_real_escape_string
```
 $name =  mysql_real_escape_string($_POST['name']);
```
Don't use MySQL, Bcz This extension is deprecated as of PHP 5.5.0, and will be removed in the future

So Final Code will be

<?php
    if(isset($_POST['submit']))
    {
        include 'db.php';

        $database="mysql_database";

        $con = mysql_connect('$server','$username','$password');
        mysql_select_db($database);

        $name =  mysql_real_escape_string($_POST['name']);
        $address =  mysql_real_escape_string($_POST['address']);
        $paid =  mysql_real_escape_string($_POST['paid']);
        $comments =  mysql_real_escape_string($_POST['comments']);


        $sql="INSERT INTO <table_name> (name, address, paid, comments) VALUES('$name','$address','$paid','$comments')";

        $a = mysql_query($sql);

        if (!$a)
        {
            die("Error addding record. " . mysql_error());
        }
        else
        {
            echo "1 record added";
        }

        mysql_close($con);
    }
?>

score 0 · Answer 6 · edited May 23 '17 at 10:26

Your <form> tag is missing the method attribute, which by default is set to GET. This way, you're checking by the $_POST["submit"] existence but it can't be, 'cause you should instead use $_GET.

Try to specify method="POST" in the first place.

That said, you should seriously follow all the good advices already given in this thread about the deprecated mysql PHP extension (i.e. use mysqli instead) and possible SQL injection attacks prevention, using prepared statements with mysqli.

Use this and this as entry points to mysqli prepared statements, and this SO post as a brief discussion about why prepared statements are a SQL injection countermeasure.

Using php to post user submitted info to mysql database

6 Answers6