mysql warnings

Question

Hello everyone.

I have the following table:

 CREATE TABLE `events` (
  `evt_id` int(11) NOT NULL AUTO_INCREMENT,
  `evt_name` varchar(50) NOT NULL,
  `evt_description` varchar(100) DEFAULT NULL,
  `evt_startdate` date NOT NULL,
  `evt_enddate` date DEFAULT NULL,
  `evt_starttime` time DEFAULT NULL,
  `evt_endtime` time DEFAULT NULL,
  `evt_amtpersons` int(11) DEFAULT NULL,
  `sts_id` int(11) NOT NULL,
  `adr_id` int(11) DEFAULT NULL,
  `evt_amtPersonsSubs` int(11) DEFAULT NULL,
  `evt_photo` varchar(50) DEFAULT NULL,
  `sys-mut-dt` timestamp NULL DEFAULT NULL,
  `sys-mut-user` varchar(20) DEFAULT NULL,
  `sys-mut-id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`evt_id`),
  KEY `sts_id` (`sts_id`),
  KEY `adr_id` (`adr_id`),
  CONSTRAINT `sts_id` FOREIGN KEY (`sts_id`) REFERENCES `statusses` (`sts_id`) O
N DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

Now I have got two problems:

Here is my query:

INSERT INTO `events`(`evt_name` , `evt_description` , `evt_startdate` , `evt_enddate` , `evt_starttime` , `evt_endtime` , `evt_amtpersons` , `sts_id` , `adr_id` , `evt_amtPersonsSubs` , `evt_photo` , `sys-mut-user` , `sys-mut-id`) VALUES ('asf' , 'asf' , '2009-04-02' , '2009-04-22' , '00:00:00' , '00:00:00' , '3' , '1' , '' , '' , '' , 'test' , '1')

1. When I execute this query through my php programs I get no error. But when I execute the query in a shell directly on the mysql database I get two warnings. How can I get PHP to alert me when there are warnings because if there are warnings mysql doesn't do the insert.

2. About the warnings:

| Warning | 1366 | Incorrect integer value: '' for column 'adr_id' at row 1 | Warning | 1366 | Incorrect integer value: '' for column 'evt_amtPersonsSubs' a t row 1

How can I get rid of these warnings. Tried to make some changes but it didn't work out so far.

Answer 1

You are inserting an empty string. You should remove the '' and put a number in that field

As you said, the column does not have to have a value specified when you insert. The fact is indicated by the "DEFAULT NULL" for that column at table creation. This fact, however, means that if you do not specify the column name in your list of columns while doing INSERT (and therefore you will not specify the corresponding value either), then the tuple can be inserted anyway, and for that column value you will get a NULL automagically by default.

However, in your query you specify that you are going to insert that column value, and the column value you say is '' (an empty string). This is of course not valid, because that column accepts integers (or NULL, because you havent' declared the column NOT NULL), and an empty string is an empty string, not an integer.

The SQL server is generous and accepts the empty string anyway (probably it casts it to zero) but reports you a warning. If you set a strict mode for the server (something I strongly suggest you to do), you will get an error and the insert will fail.

Please note that if you follow my suggestion of setting strict mode, this is server wide, involving all your databases and all your tables (at least with the mysql released one year ago). If you have awfully written software that need a forgiving server, then you cannot use it.

Answer 2

The error message tells you that that the empty string ('') is not a valid value for an integer field - in this case the fields adr_id and evt_amtPersonsSubs. Did you mean to put NULL instead?

In PHP, you can retrieve the error or warning message, for the most recent query only, using the mysql_error() function.

Answer 3

'' is not an integer.... how about using NULL in the query if you actually want a null value?

Answer 4

The warnings tell you that you're trying to insert a string value into an integer column.

In all the places where you have an int column you must not put the value between ' but just put the value as is

[...]'00:00:00' , '00:00:00' , 3 , 1 , [...]

If you don't want to provide a value for a certain column you should define the column with NULL. Then you can even leave your '' for the insert.

BUT

In general it's bad practice to do inserts like that. What if you one day need to add a column to your table? Then you have to go and rewrite your code as well.

Therefore you should do inserts like that:

INSERT INTO tbl_name (col1, col2) VALUES(value1, value2);

This way your code will still work, even if you decide to add columns. Plus the code is easier to read!!

Answer 5

But the field doesn't need to have a value necsercairly