CODEWITHSUNDEEP
phpmysqlinsertdefault-value
Chris Russo
Chris Russo

Reputation: 480

MySQL default values not working as expected

Hi friends from SO!

This code used to be working, but it seems like it stop working when I cloned the database. There's a table called maps, which has some default values, like maximum. As far as I understand, if my INSERT code has no value, then, the dafault value will be used.

This is what I'm doing:

These values might be there, as they might not be there.

$scan->maximo_exploracion   = addslashes($_GET['scan_maximo_exploracion']);
$scan->proceso_analisis = addslashes($_GET['scan_proceso_analisis']);
$scan->email_user           = addslashes($_GET['scan_email_user']);
$scan->email_owner          = addslashes($_GET['scan_email_owner']);

Then:

db_ask($db_resonance_slave, "
INSERT INTO mapas
SET
usuario_id              = '$user->id',
maximo_exploracion      = '$scan->maximo_exploracion',
proceso_analisis        = '$scan->proceso_analisis',
email_user              = '$scan->email_user',
email_owner             = '$scan->email_owner',
max_proc                = '5',
proceso_exploracion     = 'esperando',
direccion               = '$scan->address',
dominio                 = '$scan->dominio',
dominio_id              = '$dominio->id',
server_address          = '$scan->server_address',
server_zone             = '$scan->zone_name',
server_zone_code        = '$scan->zone_code',
server_response_speed   = '$scan->server_response_speed'
");

Now, a few days ago, with the same code, if $scan->maximo_exploracion didn't have any information, the record would be inserted with the default value which is 100.

At this moment, it's appearing blank. Which, of course, is causing problems.

Any ideas?

Thanks! Chris;

The Table:

CREATE TABLE `mapas` (
`id` INT(12) NOT NULL AUTO_INCREMENT,
`usuario_id` VARCHAR(256) NOT NULL,
`maximo_exploracion` VARCHAR(256) NOT NULL DEFAULT '100',
`max_proc` VARCHAR(256) NOT NULL,
`open_proc` VARCHAR(256) NOT NULL,
`hora_lanzado` VARCHAR(256) NOT NULL,
`direccion` VARCHAR(256) NOT NULL,
`dominio` VARCHAR(256) NOT NULL,
`dominio_id` VARCHAR(256) NOT NULL,
`idioma` VARCHAR(256) NOT NULL,
`server_address` VARCHAR(256) NOT NULL,
`server_zone` VARCHAR(256) NOT NULL,
`server_zone_code` VARCHAR(256) NOT NULL,
`server_response_speed` VARCHAR(256) NOT NULL,
`proceso_exploracion` VARCHAR(256) NOT NULL DEFAULT 'esperando',
`proceso_analisis` VARCHAR(256) NOT NULL DEFAULT 'esperando',
`email_owner` VARCHAR(256) NOT NULL DEFAULT 'False',
`email_user` VARCHAR(256) NOT NULL DEFAULT 'True',
`eliminado` INT(12) NOT NULL DEFAULT '0',
`creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)

COMMENT='Almacena mapas, los mapas son ordenes de exploración y análisis para un dominio específico.' COLLATE='latin1_swedish_ci' ENGINE=InnoDB;

Upvotes: 0

Views: 3188

Answers (2)

xQbert
xQbert

Reputation: 35323

Perhaps: http://www.noelherrick.com/blog/mysql-quirk-not-null-defaults

It indicates using an extended insert (where you specify multiple rows in an insert statement), that you can bypass this default value; a warning is provided instead of errros. Yet single insert statements throw an error.

This can be avoided presently by 

Set sql_mode = "strict_all_tables";

Then use the the default keyword, which will insert that column’s default value.

Upvotes: 1

Oli
Oli

Reputation: 2452

As suggested in the comments, you're passing a blank instead of NULL. Try this:

if (isset($_GET['scan_maximo_exploracion']) && $_GET['scan_maximo_exploracion']) {
    $scan->maximo_exploracion   = addslashes($_GET['scan_maximo_exploracion']);
}

Upvotes: 0

Related Questions