mySQL database not updating from form

Question

Not to sure my error is. In my html I have a form, and when I click submit it gives me an error. My connection is working, but for some reason it won't actually add to the database

 <?php

    if( isset( $_POST['submit'] ) ){
        $username = $_POST['username'];
        $password = $_POST['password'];
        $connection = mysqli_connect( 'localhost', 'root', '', 'loginapp' );

        if($connection){
            echo "we are connected";
        }
        else{
            die( "connection failed" );
        }

        $query = "INSERT INTO users(username,password) ";
        $query.= "VALUES('$username','$password')";
        $result = mysqli_query( $connection, $query );

        if( !$result ){ // if not true, put query failed
            die('Query Failed' .mysqli_error($connection));
        }
    }

    ?>

I am wondering if there is something wrong with this here as this code isnt working as planned

  $query = "INSERT INTO users(username,password) ";
  $query.= "VALUES('$username','$password')";
  $result = mysqli_query( $connection, $query );

  if( !$result ){ // if not true, put query failed
      die( 'Query Failed' . mysqli_error( $connection ) );
  }
  }

First it says "We are connected" , so good my first if is being reached, the error is from :"we are connectedQuery FailedDuplicate entry '0' for key 'PRIMARY"' — Jason Genova
– Jason Genova, Commented May 28, 2016 at 18:19
That means you have a unique column that you are violating with your insert. Likely, your username column. You cannot have duplicate values in that column, or any unique column. — gmiley
– gmiley, Commented May 28, 2016 at 18:21
I see. I am following a tutorial and am rather new to sql, how do i fix this? Do i fix it through PHP my admin, or is it a code issue? — Jason Genova
– Jason Genova, Commented May 28, 2016 at 18:23
add your table structure and check if your field with the primary key is set to auto increment. — Sandeep
– Sandeep, Commented May 28, 2016 at 18:24

gmiley · Accepted Answer · 2016-05-28 18:52:33Z

2

You are trying to insert a duplicate entry into your table. likely the username column. You can get around this but either providing a value for username that is not in your table yet, or delete the record from your table so that you can re-insert it. I can get more specific if you include the definition for your users table in your question, but that is the basic problem you are having.

If your table is defined with a numeric typed id column as your primary key, and you do not specify a new value in your insert, like this:

insert into users (id, username, password) values (1, 'joeuser', 'password');

Instead, doing what you have in your question:

insert into users (username, password) values ('joeuser', 'password');

The record inserted will either have null or 0 as the default insert value. Each time the insert is performed the same value will be inserted into the id column, which will cause a duplicate value error as you are getting.

To get around that, defining the id column as auto-increment tells it that anytime you perform an insert, and do not specify a value for id it will use the next available integer as the default value.

The other approach would be to specify a new value for the id on each execute of the insert statement, like so:

insert into users (id, username, password) values (1, 'joeuser', 'password');

insert into users (id, username, password) values (2, 'daveuser', 'password');

insert into users (id, username, password) values (3, 'janeuser', 'password');

For a table like this, using auto-increment is the best way to go.

edited May 28, 2016 at 18:52

answered May 28, 2016 at 18:24

gmiley

6,6041 gold badge15 silver badges25 bronze badges

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

5 Comments

Jason Genova Over a year ago

jeez. I literally deleted my the whole database i made, and just made it again. Only this time I put auto increment on for the id. How the heck did that fix the problem?

gmiley Over a year ago

If you had an integer column, if you do not specify a value in your insert then either no value, or a default (probably 0) will get inserted every time. If you specify that the column is auto-increment, then it will add the next incremented value into the column if no value is specified.

gmiley Over a year ago

Updated my answer to provide a bit more of an explanation.

Jason Genova Over a year ago

I was just confused. The course I'm doing just suddenly went from things like if statements and loops, to using PHP my admin, all this syntax is really wacky and im kind of just memorizing it.

gmiley Over a year ago

No problem, that's what we are here for. Also, just as a tip, phpMyAdmin is handy to use, but a real benefit to your learning would be to understand database objects by learning how to write all of your SQL statements and queries out by hand.You can mess around with stuff like that on sqlfiddle.com - you can create, delete, modify, whatever - any sql objects, data, etc... without fear of messing up your development database. Anyway - if this fixed your problem, could you accept it as the answer? =) Thanks.

Poiz · Accepted Answer · 2016-05-28 18:31:46Z

0

Alternatively; why not nest your Query within a Conditional statement and take it from there: like so:

    <?php
        if($username && $password){
            $query  = "INSERT INTO users(username, password) ";
            $query .= "VALUES('" . $username . "', '" . $password . "')";
            $result = mysqli_query($connection,$query);

            if(!$result) {
                die('Query Failed' .mysqli_error($connection));
            }               
        }

edited May 28, 2016 at 18:31

answered May 28, 2016 at 18:23

Poiz

7,5872 gold badges17 silver badges17 bronze badges

4 Comments

Sandeep Over a year ago

that is the brace for if(isset($_POST['submit'])) loop.

Jason Genova Over a year ago

I dont think that was the error. Recall I had an if(isset at the top, so that bracket was for that.

Poiz Over a year ago

@Munt... Sandeep already drew my attention to that... Agreed! That should not have anything to do with the error since it's part of your Code... my apologies ;-)

Poiz Over a year ago

@Munt You may also want to nest the Query within Conditional Logic, checking first if the Username & Passwords exist before attempting to insert into the DB... i just removed my older observation and replaced it with the Conditional....

Collectives™ on Stack Overflow

mySQL database not updating from form

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related