Inserting data to database via web form

Question

I have looked for the answer to my question and seeing as all programming varies I can't seem to fix my problem. I have created a php file that does in fact connect to my database. However, when I try submitting data to my database via my php webpage it won't go through. The same happens when I try to display info from my database to a webpage. Seeing as it is in fact connecting to the database, I'm not sure what the issue is. Any help is appreciated, try to dumb it down for me as much as possible when you answer. Also, I have triple-checked my database name and table names to make sure they match up with my coding. Here's my code:

Connection to database:

<?php

DEFINE ('DB_USER', 'root');
DEFINE ('DB_PSWD', '');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'art database');

$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PSWD, DB_NAME);

?>

My form to insert data to my database:

<?php

if (isset($_POST['submitted'])) {

    include('connect-mysql.php');

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $sqlinsert = "INSERT INTO users (first name, last name) VALUES ('$fname','$lname')";

    if (!mysqli_query($dbcon, $sqlinsert)) {
        die('error inserting new record');
        } //end of nested if

        $newrecord = "1 record added to the database";


} // end of the main if statement
?>

<html>
<head>
<title>Insert Data into DB</title>
</head>
<body>


<hl>Insert Data into DB</hl>

<form method="post" action="insert-data.php">
<input type="hidden" name="submitted" value="true"/>
<fieldset>
    <legend>New People</legend>
    <label>First Name:<input type="text" name="fname" /></label>
    <label>Last Name:<input type="text" name="lname" /></label>
</fieldset>
<br />
<input type="submit" value="add new person" />
</form>
<?php
echo $newrecord;
?>

</body>
</html>

Is space allowed in DB name and Column name as well? I think its bed practice. — Roopendra
– Roopendra, Commented Dec 1, 2013 at 3:11
Spaces are allowed in DB and columns, however it's not recommended. Using backticks solves this problem, just to let you know. @Roopendra — Funk Forty Niner
– Funk Forty Niner, Commented Dec 1, 2013 at 3:29

Community · Accepted Answer · 2017-05-23 12:09:17Z

1

The reason it's not working is because you have spaces in your columns/query.

INSERT INTO users (first name, last name)

wrap them in backticks like this:

INSERT INTO users (`first name`, `last name`)

It is not recommended to use spaces in column names or tables.

Try and use underscores instead, or remove the spaces and make the appropriate changes to your columns in your DB also, if you do.

You should also consider using:

('" . $fname . "','" . $lname . "')

instead of ('$fname','$lname')

I'm also questioning this => DEFINE ('DB_NAME', 'art database');

There is a space in between art and database. If that is the case and is in fact the name you've given your DB, do rename it to art_database and use DEFINE ('DB_NAME', 'art_database'); instead.

And do use the following for added protection:

$fname = mysqli_real_escape_string($dbcon,trim($_POST['fname']));
$lname = mysqli_real_escape_string($dbcon,trim($_POST['lname']));

Interesting article to read on protection:

How can I prevent SQL injection in PHP?

EDIT: (options)

OPTION 1, in 2 files:

First, rename your columns to firstname and lastname and use the following code and naming your file insert-data.php

DB query file (insert-data.php)

<?php

if (isset($_POST['submit'])) {

    include('connect-mysql.php');

$fname = mysqli_real_escape_string($dbcon,trim($_POST['fname']));
$lname = mysqli_real_escape_string($dbcon,trim($_POST['lname']));

    $sqlinsert = "INSERT INTO `users` (firstname, lastname) VALUES ('" . $fname . "','" . $lname . "')";

    if (!mysqli_query($dbcon, $sqlinsert)) {
        die('error inserting new record');
        } //end of nested if

        echo "1 record added to the database";


} // end of the main if statement
?>

Then in a seperate file, your HTML form; name it db_form.php for example:

HTML form (db_form.php)

<html>
<head>
<title>Insert Data into DB</title>
</head>
<body>
<hl>Insert Data into DB</hl>

<form method="post" action="insert-data.php">
<input type="hidden" name="submitted" value="true"/>
<fieldset>
    <legend>New People</legend>
    <label>First Name:<input type="text" name="fname" /></label>
    <label>Last Name:<input type="text" name="lname" /></label>
</fieldset>
<br />
<input type="submit" name="submit" value="add new person" />
</form>
</body>
</html>

NEW EDIT - OPTION 2, all in one file:

Use this in one page, with nothing else added:

<?php

if (isset($_POST['submit'])) {

if(empty($_POST['fname'])) {
die("Fill in the first name field.");
}

if(empty($_POST['lname'])) {
die("Fill in the last name field.");
}

    include('connect-mysql.php');

$fname = mysqli_real_escape_string($dbcon,trim($_POST['fname']));
$lname = mysqli_real_escape_string($dbcon,trim($_POST['lname']));

    $sqlinsert = "INSERT INTO `users` (firstname, lastname) VALUES ('" . $fname . "','" . $lname . "')";

    if (!mysqli_query($dbcon, $sqlinsert)) {
        die('error inserting new record');
        } //end of nested if

        echo "1 record added to the database";


} // end of the main if statement
?>

<html>
<head>
<title>Insert Data into DB</title>
</head>
<body>


<hl>Insert Data into DB</hl>

<form method="post" action="">
<fieldset>
    <legend>New People</legend>
    <label>First Name:<input type="text" name="fname" /></label>
    <label>Last Name:<input type="text" name="lname" /></label>
</fieldset>
<br />
<input type="submit" name="submit" value="add new person" />
</form>
<?php
echo $newrecord;
?>

</body>
</html>

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Dec 1, 2013 at 3:04

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

24 Comments

Graychamp Over a year ago

Thank you, I went ahead and changed that but it still fails to add any data to the database. Is there code in which I could place in so I get an error code? That could help out to narrow down the issue.

Funk Forty Niner Over a year ago

You're welcome. Have a look at this link us1.php.net/mysqli_error on the PHP.net website for error handling. @user3053547

Graychamp Over a year ago

Alright, I've changed what you've suggested. Even after changing the database from art database to art_database I'm still not getting the data to send through to my database. Also, I appreciate the security tips; however, as of now this is just a learning experience for me.

Graychamp Over a year ago

Also, I'm not sure if this makes a difference but I get this message on my input web page. Notice: Undefined variable: newrecord in C:\Program Files\EasyPHP-DevServer-13.1VC11\data\localweb\insert-data.php on line 42

Funk Forty Niner Over a year ago

A few more things you could do is change all your instances of first name to first_name and rename your column to that in your DB, and also wrapping the word users in INSERT INTO users with backticks. Plus your hidden field called submitted could also be playing tricks on you. Try naming your submit button to <input type="submit" name="submit" value="add new person" /> then use if (isset($_POST['submit'])) { instead of if (isset($_POST['submitted'])) { my recommendations should work, it's basically what I use also.

|

Neertel · Accepted Answer · 2014-05-27 13:34:21Z

I have made some changes, which is working fine for me Where i can ignore if data is already in database You Can try this to

<?php

   if (isset($_POST['submit'])) {

  include('db.inc.php');

   $fname = mysqli_real_escape_string($dbcon,trim($_POST['fname']));
   $lname = mysqli_real_escape_string($dbcon,trim($_POST['lname']));

   // $sqlinsert = "INSERT INTO `user` (firstname, lastname) VALUES ('" . $fname . "','" . $lname . "')";
   $sqlinsert = "INSERT IGNORE INTO `dbname`.`user` (`fname`, `lname`) VALUES ( '$fname', '$lname')";

   if (!mysqli_query($dbcon, $sqlinsert)) {
    die('error inserting new record');
    } //end of nested if

    echo "1 record added to the database";


   } // end of the main if statement
   ?>

Where db.inc.php is a different file in same directory for connecting database

<?php

  $dbcon=mysqli_connect("localhost","dbuser","yourpassword","dbname");
  if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  ?>

Collectives™ on Stack Overflow

Inserting data to database via web form

2 Answers 2

EDIT: (options)

OPTION 1, in 2 files:

NEW EDIT - OPTION 2, all in one file:

Use this in one page, with nothing else added:

24 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT: (options)

OPTION 1, in 2 files:

NEW EDIT - OPTION 2, all in one file:

Use this in one page, with nothing else added:

24 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related