MySql: Insert values in an auto_increment field with PHP

Question

What I'm actually trying to do is insert a row with:

INSERT INTO users VALUES (col1, col2, ...)

where col1 is an auto_increment.

The PHP code is:

<?php 
$host = "http://name.altervista.org/";
$user = "name";
$psw = "";
$db = "my_name";

$response = array();
$response["success"] = true;

$connessione = new mysqli($host, $user, $psw, $db);

if($connessione->connect_errno == 0)
{
    $nome = $_POST["nome"];
    $cognome = $_POST["cognome"];
    $username = $_POST["username"];
    $password = $_POST["password"];
    $nTelefono = $_POST["nTelefono"];
    $email = $_POST["email"];

    $sql = "INSERT INTO users 
            VALUES (DEFAULT, '$nome', '$cognome', '$username', '$password', '$nTelefono', '$email')";
    $ris = $connessione->query($sql);

    if($connessione->affected_rows == 1)
    {
        echo(json_encode($response)); 
    }
    else
    {
        $response["success"] = false;
        echo(json_encode($response)); 
    }
}
else
{
    $response["success"] = false;
    echo(json_encode($response)); 
}
?>

I search similar questions here in stackoverflow, and I try to use DEFAULT or NULL, but it doesn't work. And if I put a number instead of the default value that is not already in the table it works, so I really don't understand where the problem is.

Have you any other suggestions?

EDIT: The table structure on the database: click

EDIT 2: I tried to delete the table and create it again, and now it works with the NULL thing. Thanks for the support!

you don't need to have any value for an AI'd column; just remove it — Funk Forty Niner, Jun 20 '17 at 12:00
just remove first field value OR insert ` '' ` in autoincrement field may be it helps. — Gaurang Sondagar, Jun 20 '17 at 12:02
some of the answers given so far, failed to address the plain text password issue. Don't go live with this, till you used a safe hashing method. — Funk Forty Niner, Jun 20 '17 at 12:08
As far as I know, put something like: "INSERT INTO table VALUES (...) without the auto_increment it should work, but as I said it isn't working. Just a few seconds and I will add the table on the question above, maybe the problems is there. — Denko Swagga, Jun 20 '17 at 12:12
@DenkoSwagga You're going to have to ask for (more) help from everyone who gave you answers, If they're not going to help you, then IMHO they shouldn't leave you *"high and dry"* like this. — Funk Forty Niner, Jun 20 '17 at 12:19

Gordon Linoff · Accepted Answer · 2017-06-20T12:14:23.153

5

When you are doing an insert, list all the columns being inserted. You seem to want:

INSERT INTO users (nome, cognome, username, password, nTelefono, email)
    VALUES ('$nome', '$cognome', '$username', '$password', '$nTelefono', '$email');

Next. Never store clear-text passwords in the database. You should be encrypting the value on the client side so the values are never passed over the network.

Next. Learn to use parameterized queries. When you munge query strings with parameter values, your are asking for inexplicable syntax errors and making the code subject to SQL injection attacks.

edited Jun 20 '17 at 12:14

answered Jun 20 '17 at 12:02

Gordon Linoff

1,242,037
58
646
786

I tried that several times, but it doesn't work as well. I knew that not putting the auto_increment values, in theory Sql will provide a values all by himself. But I can't understand why now is not working like it would. For the password thing yeah, the next thing to do is that – Denko Swagga Jun 20 '17 at 12:07

score 0 · Answer 2 · answered Jun 20 '17 at 12:05

0

From the Mysql docs

INSERT INTO users VALUES ('$nome', '$cognome', '$username', '$password', '$nTelefono', '$email')";

The auto_increment fields doesn't need to be set in an INSERT statement

answered Jun 20 '17 at 12:05

wilson

334
1
15

It won't work. If you are not inserting all the columns from sql query then you have to specify the columns in which you are saving the data. So to make it work please specify the column names after table name inside () – Ashish Jun 20 '17 at 12:16
If I don't put a field, I have to specify them above yeah. The problem is that is still not working – Denko Swagga Jun 20 '17 at 12:21

score 0 · Answer 3 · answered Jun 20 '17 at 12:07

In MySQL, if you have an auto_increment column you don't need to put it in the insert statement.

Example: You have the table:

CREATE TABLE test (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
);

Your SQL statement should be:

INSERT INTO test(name) VALUES ('My name');

MySql: Insert values in an auto_increment field with PHP

3 Answers3