PHP MySQL - Insert into Without Using Column Names But with Autoincrement Field

PHP MYSQL - Insert into without using column names but with autoincrement field

Just use NULL as your first value, the autoincrement field will still work as expected:

INSERT INTO tblname VALUES (NULL, ... 32 Fields ... )

Insert values without mentioning the column name

If this is just for experimental purposes yes you can:

INSERT INTO Persons VALUES (NULL, 'Lastname', 'Firstname', 'Address test', 'city of angels');

But I strongly urge you to not make it a habit of doing this. Always add columns, as eloquently stated by David's answer.

INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Last', 'First', 'Add', 'City');

Fiddle

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.

PHP fails to insert into Mysql (auto increment)

To address the Issue of mysql_query being deprecated, I recommend PDO instead. It's super easy and much safer with prepared staements. This snippet will provide a connection to the DB to make your queries:

$mysql_host = "localhost";
$mysql_db = "my_database";
$mysql_user = "my_user";
$mysql_password = "mypassword";
$db = new PDO('mysql:host='.$mysql_host.';dbname='.$mysql_db.';charset=utf8', $mysql_user, $mysql_password);

Once you have that on your page, you can use $db to interact with your database.

$q = $db->prepare("INSERT INTO `store` (`name`, `description`, `price`, `image`) VALUES (:name, :desc, :amt, :img)");
$q->execute(array(":name"=>$name, ":desc"=>$description, ":amt"=>$amount, ":img"=>$image));

Also, for auto-imcrement values, you can omit that field all together.

PS, there's a stntax error. You're missing a space before the opening parenthesis:
store (

How to insert data to MySQL with auto-incremented column(field)?

In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.

INSERT INTO test.authors (
   instance_id,host_object_id,check_type,is_raw_check,
   current_check_attempt,max_check_attempts,state,state_type,
   start_time,start_time_usec,end_time,end_time_usec,command_object_id,
   command_args,command_line,timeout,early_timeout,execution_time,
   latency,return_code,output,long_output,perfdata
) VALUES (
   '1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
   '2012-01-03 12:50:59','198963','21','',
   '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
   '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
   '','rta=2.860000m=0%;80;100;0'
);

INSERT INTO ... SELECT without detailing all columns

Either you list all of the fields you want in the insert...select, or you use something else externally to build the list for you.

SQL does not have something like SELECT * except somefield FROM, so you'll have to bite the bullet and write out the field names.

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

What must one put in a SQL statement for an auto generated field?

It's good to get into the habit of specifying your fields like this

INSERT INTO users (username,password) VALUES ( '$username', '$hashedPassword')

Or if you want to specify the id field too use NULL

INSERT INTO users (id,username,password) VALUES (NULL, '$username', '$hashedPassword')

By specifying the fields, your code is less likely to break if, for example, you add an extra field to the table.

---

Related Topics