0

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>
Marc Delisle
  • 8,879
  • 3
  • 29
  • 29
Graychamp
  • 15
  • 1
  • 1
  • 4

2 Answers2

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:

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>
Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • 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. – Graychamp Dec 01 '13 at 03:23
  • You're welcome. Have a look at this link http://us1.php.net/mysqli_error on the PHP.net website for error handling. @user3053547 – Funk Forty Niner Dec 01 '13 at 03:27
  • 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 Dec 01 '13 at 03:47
  • 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 – Graychamp Dec 01 '13 at 03:51
  • 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 `` then use `if (isset($_POST['submit'])) {` instead of `if (isset($_POST['submitted'])) {` my recommendations should work, it's basically what I use also. – Funk Forty Niner Dec 01 '13 at 03:54
  • Yes, that would also make a difference. When did `newrecord` come into play, is that not your full HTML form code above? Are you seperating your PHP from your html form or using it as all in one? @user3053547 – Funk Forty Niner Dec 01 '13 at 03:55
  • Ok, I think I know what's going on. You're using `$newrecord = "1 record added to the database";` in your DB query file and using `echo $newrecord;` in a seperate file (your form). Those two won't interact with each other, since they are in two seperate pages. @user3053547 – Funk Forty Niner Dec 01 '13 at 04:02
  • I suspect you are using your entire code that you posted in one file. You either must break up your DB query and your form, name your form as `db_form.php` and your DB query file to `insert-data.php` while keeping your form action as `action="insert-data.php"` or if you want to use it as one big piece of code like you have, then use `action=""` instead. That will make it execute in the same page. @user3053547 – Funk Forty Niner Dec 01 '13 at 04:05
  • Have a look at a few options I made under **EDIT: (options)** @user3053547 – Funk Forty Niner Dec 01 '13 at 04:18
  • My first file is the connection file. It is separate. The rest of the coding is all one file. – Graychamp Dec 01 '13 at 04:19
  • with my `insert-data.php` section, I'm having the file call upon itself..should I not do that? – Graychamp Dec 01 '13 at 04:24
  • Yes, since it's all in one file. You need to use `action=""` and not a filename, if you're using it all in one file. @user3053547 – Funk Forty Niner Dec 01 '13 at 04:24
  • Okay I posted the changes under the edit options. I no longer get a page saying it failed, but the names I place in the input boxes disappear when I submit yet don't show up in the database nor do I get the message that it was added to the db. Could it be a problem with my database? – Graychamp Dec 01 '13 at 04:42
  • You need to change this `` to this `` I just tested the code I gave you in a DB I setup, and was successful. @user3053547 (added `name="submit"` to submit button) – Funk Forty Niner Dec 01 '13 at 04:47
  • I'm going to re-edit my answer with the new code in its entirety. @user3053547 It's called **NEW EDIT - OPTION 2, all in one file:** – Funk Forty Niner Dec 01 '13 at 04:51
  • Hmm, I changed it and now it's going back and telling me the "error inserting new record". I'm about to just make a whole new database and give up on this one due to the fact it seems I've really messed up somewhere along the way. – Graychamp Dec 01 '13 at 04:54
  • I added something just a minute ago, don't know if you saw it `if(empty($_POST['fname']))` etc. you need that because if nothing is entered in either field, it will create a blank entry in your DB. @user3053547 – Funk Forty Niner Dec 01 '13 at 04:58
  • With the new edit, I just get a page that says "Fill in the first name field." – Graychamp Dec 01 '13 at 05:02
  • if you don't enter anything, you will get that error. You need to enter something in there. If you used my exactly as shown, you should be having success. @user3053547 – Funk Forty Niner Dec 01 '13 at 05:04
  • I copied and pasted then tested it. First thing to pop up was text that says "fill in the first name field". Completely replaced my other code with yours and that's what I'm getting currently. – Graychamp Dec 01 '13 at 05:09
  • Silly me... I put that code in the wrong place. Reload my edit (this page). I put the `if(empty...` after the `if (isset($_POST['submit'])) {` @user3053547 sorry about that. – Funk Forty Niner Dec 01 '13 at 05:12
  • Awesome! Worked like a charm. Truly appreciate the help. I'm sure I'll be back as I will be doing more things such as viewing the database on a webpage but I was having the same issue with it as I was the submitting data part so I might be able to figure it out myself. Again, thanks! – Graychamp Dec 01 '13 at 05:26
  • Just another thing that I noticed, no biggie but not valid. In `Insert Data into DB` I do believe you meant to do `

    Insert Data into DB

    ` --- you had `` an lowercase `L` instead of a number one `1`. You will see how big your letters will be after. @user3053547     – Funk Forty Niner Dec 01 '13 at 05:34
  • When I am click a submit Button Not Any changes are shown – Ashish Shahi Mar 01 '17 at 05:42
0

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();
  }
  ?>
Neertel
  • 1
  • 2