1

I'm trying to learn PDO and apply to an existing Joomla database table ("users"), just for learning purposes. However I keep getting errors with regard to the "lastResetTime" column.

23000 SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'lastResetTime' cannot be null

When the default system makes a user the default value is set to 0000-00-00 00:00:00, although the settings are NOT NULL.

For some reason I cannot pass this value (0000-00-00 00:00:00) in my queries. This is what I have. Could anyone tell me how I can trigger the default value, despite the facct that I cannot provide NULL, nor 0000-00-00 00:00:00 (which is strictly invalid if I'm correct)

<?php
require 'PDO.php';

$table = $prefix."users";

$pdo = new PDO($dsn,$user,$pass,$opt);

$stmt = $pdo->prepare("INSERT INTO $table (username, email, params, registerDate, lastvisitDate, lastResetTime) VALUES(?, ?, ?, now(),now(), ?)");
$stmt->execute(["lalaa","aaaa","",NULL]);

The table is formated as follows:

 CREATE TABLE `x7j4o_users` (
 `id` int(11) NOT NULL,
 `name` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `username` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `block` tinyint(4) NOT NULL DEFAULT '0',
 `sendEmail` tinyint(4) DEFAULT '0',
 `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `activation` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 `params` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT      'Date of last password reset',
 `resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',
 `otpKey` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Two factor authentication encrypted keys',
 `otep` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'One time emergency passwords',
 `requireReset` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Require user to   reset password on next login'
)     
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE The following doesn't seem to work also

$stmt = $pdo->prepare("INSERT INTO $table (name, username,registerDate,lastvisitDate,lastResetTime) VALUES(?,?,DEFAULT,DEFAULT,DEFAULT)");
$stmt->execute(['aaaa','aaaaaaaa']);

it triggers the error:

22007 SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'registerDate' at row 1

If anyone knows how I can use the default value as set in mysql I would be very greatfull.

Obi Wan Kenobi
  • 15
  • 4
  • 1
    So change your `["lalaa","aaaa","",NULL]` to `["lalaa","aaaa","",""]` - why are you passing NULL when your column doesn't accept NULL values? – Funk Forty Niner Jul 29 '18 at 13:09
  • Thank you for your response. Unfortunately I tried that earlier, but it results in the error: 22007 SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'lastResetTime' at row 1 That is why I don't understand this, because params, is not complaining so to say – Obi Wan Kenobi Jul 29 '18 at 13:14
  • Try removing it from it then. That should auto populate itself. Or use all the value/column numbers of the column in the array. – Funk Forty Niner Jul 29 '18 at 13:15
  • Thanks again, I have tried that too this morning :( it results in the error: 22007 SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastResetTime' at row 1 – Obi Wan Kenobi Jul 29 '18 at 13:17
  • Welcome. This *might* fix your problem. Try changing your `id` column to be auto incremented. Your query is probably trying to use columns that aren't populating correctly and isn't synched correctly. You have more columns in your schema than what's in the query, unless you didn't include everything. – Funk Forty Niner Jul 29 '18 at 13:20
  • I just checked, but it seems it is actually set to AUTO_INCREMENT when I look in phpmyadmin. – Obi Wan Kenobi Jul 29 '18 at 13:25
  • Welcome. So, if you have x-columns to insert into, use the same amount of values in the query and array's execute. The auto incremented id (once altered) doesn't need to be part of it (the query), since it will auto populate from there. That's about all I can add to this. You can also try using a ternary operator to have empty default values if none are given. The example in http://php.net/manual/en/language.operators.comparison.php shows as `$action = (empty($_POST['action'])) ? 'default' : $_POST['action'];` you can replace `empty` with `isset` if using radios/checkboxes. – Funk Forty Niner Jul 29 '18 at 13:29
  • Sure thing. If you find your solution yourself, Stack lets you post your own answer. Let me know either way if someone else finds it also. I'd be happy to upvote it, *cheers*. Good luck :-) – Funk Forty Niner Jul 29 '18 at 13:37
  • I'm sorry but that seems not to be the solution. For any column that is set as "not nul" I added to the query as follows: `$stmt = $pdo->prepare("INSERT INTO $table (name, username, email, password,registerDate, lastvisitDate, activation, params , lastResetTime,resetCount,otpKey,otep,requireReset) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)"); $stmt->execute(['','','','','','','','','','','','','']);` creating the error: 22007 SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'registerDate' at row 1 I'm aware of the PHP features to check if values are set – Obi Wan Kenobi Jul 29 '18 at 13:40
  • Can’t, you can in sql though. https://stackoverflow.com/questions/39808684/mysql-date-field-with-default-curdate-not-datetime#39808770 –  Jul 29 '18 at 15:02

1 Answers1

0

The answer has apparently something to do with PDO

The solution is the following line:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=''"

Always great to waste 1 complete day ;)

In my code it is embeded here:

$opt = [
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES   => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=''"
];

Although this fixed the problem, I'm hesitant to say it is a solid solution. If have read https://codeascraft.com/2013/03/19/the-perils-of-sql_mode/ which thought me the dangers of sql_mode set to null. This seems to be the architecture of the tables I use, which is supplied by Joomla CMS

Obi Wan Kenobi
  • 15
  • 4