Scripting – Page 13

December 6, 2013August 12, 2014Scripting

Setting Current_Timestamp/Datetime to field/column value in MySQL doesn’t work from Php using PDO connection.

Say in one of your table you have a field which you want to set with current time value from your database server. However, setting timestamp/datetime values from php to MySQL using PDO didn’t work for me in case of both NOW(), SYSDATE(), FROM_UNIXTIME(NOW()) The alternate/workaround, I found is to set MySQL default value for the selected column to auto-populate with the current_timestamp of the MySQL server. This worked fine. ALTER TABLE testusers CHANGE creation_time creation_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ; < p>

December 6, 2013August 12, 2014DataBase, Scripting

Configuring UNIQUE constraint in MYSQL to avoid duplicate entries for a given column/field

MySQL Query: ALTER TABLE `MyLabDB`.`testusers` DROP INDEX `username` , ADD UNIQUE `username` ( `username` ) COMMENT ‘Avoid duplicate entries for username’; phpMyAdmin GUI: After setting the Unique Index for the field, whenever a duplicate entry is attempted for insertion, mySQL will result in “#1062” error as shown below: #1062 – Duplicate entry ‘user9’ for key ‘username’ < p>

December 6, 2013August 12, 2014DataBase, Scripting

FIX: MySQL Error: #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 6

MySQL CREATE TRIGGER: CREATE TRIGGER `validate_email_insert` BEFORE INSERT ON `testusers` FOR EACH ROW BEGIN IF NEW.`email` NOT LIKE ‘%_@%_.__%’ THEN SIGNAL SQLSTATE VALUE ‘45000’ SET MESSAGE_TEXT = ‘[table:person] – NEW.`email` column is not valid’; END IF; END; Error SQL query: CREATE TRIGGER `validate_email_insert` BEFORE INSERT ON `testusers` FOR EACH ROW BEGIN IF NEW.`email` NOT LIKE ‘%_@%_.__%’ THEN SIGNAL SQLSTATE VALUE ‘45000’ SET MESSAGE_TEXT = ‘[table:person] – NEW.`email` column is not valid’; MySQL said: #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the […]