CODEWITHSUNDEEP
phpmysqldatabase
user1788736
user1788736

Reputation: 2845

Does MySQL Insert allow not specifying all fields?

I have two mysql insert statements. The one with all the fields specified in insert statement works fine and insert record to testTable.(Even when http_referer is empty the insert statement insert records to table with referer field empty)

First Insert statement with all fields specified:

mysql_query("INSERT INTO testTable VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1','".$_SERVER['HTTP_REFERER']."')");

The problem is with second insert statement that doesn't insert any record to testTable! Could you guys tell me why my second insert statement doesn't insert any record to testTable?

Second insert Statment:

mysql_query("INSERT INTO testTable VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1')");

Create Table:

CREATE TABLE IF NOT EXISTS `testTable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(32) DEFAULT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Title` varchar(32) NOT NULL,
  `Ref` varchar(250) NULL default '',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1784 ;

Upvotes: 4

Views: 11355

Answers (5)

Kermit
Kermit

Reputation: 34054

Yes, by using a column list.

$sql = "INSERT INTO table (`ip`, `date`, `Title`) VALUES ('".$_SERVER['REMOTE_ADDR']."', NOW(), 'Page 1')";

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which.

Upvotes: 6

Bhaskar Bhatt
Bhaskar Bhatt

Reputation: 1467

In first query error not comes because you are specifying all column and fieleds.If any filed is auto increment or by default null you should mention all the column name along with values in insert query accepting null of auto increment field

this are demo with

  1. All field value

    insert into testtable values (1,"127.1.1.0",curdate(),"test 1","default");

  2. Without Default value

    insert into testtable (id,ip,date,title) values (1,"127.1.1.0",curdate(),"test 1");

  3. without auto increment field

    insert into testtable (ip,date,title) values ("127.1.1.0",curdate(),"test 1");

Upvotes: 2

user20232359723568423357842364
user20232359723568423357842364

Reputation: 4634

You can use a column list or SET syntax

Column list:

INSERT INTO table (column1, column2) VALUES ('$value1', '$value2');

SET syntax:

INSERT INTO table SET column1 = '$value1', column2 = '$value2';

Upvotes: 1

Achrome
Achrome

Reputation: 7821

You can choose to specify which columns you want to insert into in an insert statement.

$sql = "INSERT INTO testTable(ID, ip, date, Title)
VALUES('$ID','".$_SERVER['REMOTE_ADDR']."',NOW(),'Page1')";

Additionally, please don't use mysql functions as they are deprecated now. Use MySQLi, or PDO

Upvotes: 5

Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

You have to specify the fields with the second query. If you're not going to insert every column, in the order of the columns, then you have to specify the column names.

INSERT INTO table (column1, column2, columns3) VALUES ('$value1', '$value2', '$value3');

Upvotes: 3

Related Questions