PHP MySQL - Insert New Record into Table with Auto-Increment on Primary Key

PHP mySQL - Insert new record into table with auto-increment on primary key

Use the DEFAULT keyword:

$query = "INSERT INTO myTable VALUES (DEFAULT,'Fname', 'Lname', 'Website')";

Also, you can specify the columns, (which is better practice):

$query = "INSERT INTO myTable
          (fname, lname, website)
          VALUES
          ('fname', 'lname', 'website')";

Reference:

• http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html

MySql: Insert values in an auto_increment field with PHP

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.

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

Insert auto increment primary key to existing table

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

How retrieve in PHP the generated PRIMARY KEY from an AUTO_INCREMENT column for each row with an INSERT of multiple rows in MySQL?

There is no way to retrieve more than one generated auto-inc variable. Calling LAST_INSERT_ID() returns you the first value generated by the last INSERT.

To get all the values, insert one row at a time, and call LAST_INSERT_ID() after each one. This results in more overhead, but it's the only workaround.

This is especially an issue if you're doing INSERT...SELECT or LOAD DATA INFILE, generating many new id values in a batch.

See also MySQL LAST_INSERT_ID() used with multiple records INSERT statement

INSERT INTO and primary keys, autoincrement fields, and default values

MySQL provides a convenient way to answer this exact question:

mysql> SELECT LAST_INSERT_ID()

The mysql_* functions come with a wrapper for that: check mysql_insert_id

The mysql driver for PDO gives the same:

$pdo->exec('insert into ...');
$lastId = $pdo->lastInsertId();

Codeigniter insert where MySql table has auto increment id?

Can you show your table structure?

test Table:

id (AUTO INC 10)

member_id (VARCHAR 10)

name (VARCHAR 10)

email (VARCHAR 15)

$fielddata['member_id'] = 'abcdefghi12345';
$fielddata['name'] = 'John Smith';
$fielddata['email'] = 'jsmith@gmail.com';

$this->db->insert('test', $fielddata);

Create table name 'test' and test if this is working..

---

Related Topics