Blank record issue in cakephp - Mysql

Question

In my application in-spite of the strict validation on client and server side both, somehow it's happen that record is going blank from user registration page. All data is blank accept the default value in my table (eg. Gender - Male). This happens approximately 1 in 100 registration.

Mysql - I have structure like not null for every required field. Still it's inserting blank.

Cakephp side - I am checking data array is blank or not before saving it.

I will really appreciate if anyone help me out.

Thanks in advance.

Can you post your validation rules?

dhofstet
– dhofstet

2011-07-14 14:13:41 +00:00
Commented Jul 14, 2011 at 14:13 — dhofstet
– dhofstet, Commented Jul 14, 2011 at 14:13

Johan · Accepted Answer · 2013-03-15 16:57:25Z

2

It's blank because in every database (except Oracle) an empty string '' is not null. Therefore the database will accept that.

If you want to prevent this from happening you need to write a trigger like so:

DELIMITER $$

CREATE TRIGGER bi_table1_each BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
  //this will cause an error and prevent the insertion of an empty gender field.
  //If gender has a default value, the database will insert the default value instead.
  IF new.gender = '' THEN SET new.gender = null;
END $$

DELIMITER ;

Alternatively you can make gender a enum field

ALTER table1 
CHANGE COLUMN gender gender ENUM ('M','F');

Links:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.5/en/triggers.html

edited Mar 15, 2013 at 16:57

answered Jul 14, 2011 at 11:21

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Blank record issue in cakephp - Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related