MySql: Insert values in an auto_increment field with PHP

0
votes

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!

3
phpmysqlauto-increment
you don't need to have any value for an AI'd column; just remove it - Funk Forty Niner
just remove first field value OR insert ` '' ` in autoincrement field may be it helps. - Gaurang Sondagar
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
@Fred-ii- Gordon's answer doesn't fail to do so. - Florian Humblot
@FMashiro I stand corrected; you're right. I edited. - Funk Forty Niner

3 Answers

5
votes

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.

0
votes

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

0
votes

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');