Re: Default value of INT NOT NULL fields
as far as I understood you, cakephp inserts NULL instead of '0' right?
Correct
Set your database to default '0' as it should work.
It doesn't as MySQL refuses to accept NULL for a NOT NULL field and if
NULL is allowed then it supersedes the default 0 value.
- If it does not add function beforeSave() { /* ... */ } to your
YourModelWithLotsOfInts
- Within that check if a variable is a.) or empty or NULL or
whatever your want to test again
- ...As well if it is your desired INT - check var $_schema for that.
- You can do that in a loop against the data array.
- If the constrains are right just change those values before saving.
This was what I expected the answer to be, but what are the
performance and coding differences between doing this in the app
(looping through a large multi-dimensional array) and adding the db
trigger which only acts upon the individual SQL calls which are passed
to it?
From my limited research on db triggers, they do seem to offer better
performance and they are certainly working for me .. but I am happy to
be told otherwise by someone with more knowledge :)
Regards,
Paul.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Default value of INT NOT NULL fields
My suggestion,
as far as I understood you, cakephp inserts NULL instead of '0' right?
Set your database to default '0' as it should work.
- If it does not add function beforeSave() { /* ... */ } to your
YourModelWithLotsOfInts
- Within that check if a variable is a.) or empty or NULL or
whatever your want to test again
- ...As well if it is your desired INT - check var $_schema for that.
- You can do that in a loop against the data array.
- If the constrains are right just change those values before saving.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: Default value of INT NOT NULL fields
You could use a db trigger. Assuming you are using MySQL: Never heard of these and has taken me 4 hours to get the right syntax and wrestle it into phpmyadmin. For anyone interested this is what I added (make sure to change the delimiter field to // before/after executing) DROP TRIGGER IF EXISTS `dnuk_app`.`asvr_insert`// CREATE TRIGGER `dnuk_app`.`asvr_insert` BEFORE INSERT ON `dnuk_app`.`annual_statistics_volunteer_roles` FOR EACH ROW BEGIN IF NEW.age1 IS NULL THEN SET NEW.age1 = 0; END IF; IF NEW.age2 IS NULL THEN SET NEW.age2 = 0; END IF; IF NEW.age3 IS NULL THEN SET NEW.age3 = 0; END IF; IF NEW.age4 IS NULL THEN SET NEW.age4 = 0; END IF; END // After I managed to add this and stop dancing around the room I was shocked to see it didn't help as MySQL was doing table checks before the trigger was being ran which meant my NOT NULL fields were stopping the insert. Once I allowed NULL values in the offending fields, MySQL let the query through so the trigger could do it's job. Still not sure if this is the most practical way to solve this issue, but allows me to use Cake's default behaviour and allow my database to worry about data integrity. Thanks, Paul. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Default value of INT NOT NULL fields
I am trying to submit a form with 100's of numerical INT fields. On average only around 50% of them will be completed and should default to 0 if left blank. However if no value is entered Cake tries to enter NULL values for these fields and I get nasty SQL errors. This issue has been raised in the past: https://trac.cakephp.org/ticket/5141 http://groups.google.com/group/cake-php/browse_thread/thread/a0b1a35c1909d5db http://groups.google.com/group/cake-php/browse_thread/thread/47b6b4a5715d32cb I have looked at the ticket raised and I bow down to superior knowledge that Cake treats such fields in the proper manner, but this still doesn't help me resolve my issue. Can someone inform me how I should configure my form and/or table so that the user can skip any fields that do not have a numerical value yet when inserted into the database these values will default to 0? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Default value of INT NOT NULL fields
Here is my data array ... as you can see a lot of fields that a user would have to enter zero's into ... is there anyway within the form helper to default int fields to zero if they have no value when displayed? Array ( [AnnualStatistic] = Array ( [id] = 210 [vol_age_1] = 2 [vol_age_2] = 4 [vol_age_3] = 4 [vol_age_4] = 4 [households] = 0 [bednight_avg_hours] = 0.00 [bednights] = 0 [referral1_male_age1] = 0 [referral1_male_age2] = 0 [referral1_male_age3] = 0 [referral1_female_age1] = 0 [referral1_female_age2] = 0 [referral1_female_age3] = 0 [referral2_male_age1] = 0 [referral2_male_age2] = 0 [referral2_male_age3] = 0 [referral2_female_age1] = 0 [referral2_female_age2] = 0 [referral2_female_age3] = 0 [referral3_male_age1] = 0 [referral3_male_age2] = 0 [referral3_male_age3] = 0 [referral3_female_age1] = 0 [referral3_female_age2] = 0 [referral3_female_age3] = 0 [disability1] = 0 [disability2] = 0 [disability3] = 0 [care_leaver1] = 0 [care_leaver2] = 0 [care_leaver3] = 0 ) [AnnualStatisticVolunteerRole] = Array ( [820] = Array ( [role_id] = 820 [age1] = 1 [age2] = [age3] = [age4] = ) [1459] = Array ( [role_id] = 1459 [age1] = 2 [age2] = [age3] = 3 [age4] = 4 ) [726] = Array ( [role_id] = 726 [age1] = [age2] = [age3] = [age4] = ) [728] = Array ( [role_id] = 728 [age1] = [age2] = [age3] = [age4] = ) [724] = Array ( [role_id] = 724 [age1] = [age2] = [age3] = [age4] = ) [723] = Array ( [role_id] = 723 [age1] = [age2] = [age3] = [age4] = ) [730] = Array ( [role_id] = 730 [age1] = [age2] = [age3] = [age4] = ) [725] = Array ( [role_id] = 725 [age1] = [age2] = [age3] = [age4] = ) [720] = Array ( [role_id] = 720 [age1] = [age2] = [age3] = [age4] = ) [721] = Array ( [role_id] = 721 [age1] = [age2] = [age3] = [age4] = ) [798] = Array ( [role_id] = 798 [age1] = [age2] = [age3] = [age4] = ) [1458] = Array ( [role_id] = 1458 [age1] = [age2] = [age3] = [age4] = ) [963] = Array ( [role_id] = 963 [age1] = [age2] = [age3] = [age4] = ) [1460] = Array ( [role_id] = 1460 [age1] = [age2] = [age3] = [age4] = ) [727] = Array ( [role_id] = 727 [age1] = [age2] = [age3] = [age4] = ) [1461] = Array ( [role_id] = 1461 [age1] = [age2] = [age3] = [age4] = ) [796] = Array ( [role_id] = 796
Re: Default value of INT NOT NULL fields
could you post debug($this-data) from the controller after the form is submitted? Usually when I need defaults, I set them at DB-level, never bothered to have Cake set defaults for me. WebbedIT pisze: Here is my data array ... as you can see a lot of fields that a user would have to enter zero's into ... is there anyway within the form helper to default int fields to zero if they have no value when displayed? Array ( [AnnualStatistic] = Array ( [id] = 210 [vol_age_1] = 2 [vol_age_2] = 4 [vol_age_3] = 4 [vol_age_4] = 4 [households] = 0 [bednight_avg_hours] = 0.00 [bednights] = 0 [referral1_male_age1] = 0 [referral1_male_age2] = 0 [referral1_male_age3] = 0 [referral1_female_age1] = 0 [referral1_female_age2] = 0 [referral1_female_age3] = 0 [referral2_male_age1] = 0 [referral2_male_age2] = 0 [referral2_male_age3] = 0 [referral2_female_age1] = 0 [referral2_female_age2] = 0 [referral2_female_age3] = 0 [referral3_male_age1] = 0 [referral3_male_age2] = 0 [referral3_male_age3] = 0 [referral3_female_age1] = 0 [referral3_female_age2] = 0 [referral3_female_age3] = 0 [disability1] = 0 [disability2] = 0 [disability3] = 0 [care_leaver1] = 0 [care_leaver2] = 0 [care_leaver3] = 0 ) [AnnualStatisticVolunteerRole] = Array ( [820] = Array ( [role_id] = 820 [age1] = 1 [age2] = [age3] = [age4] = ) [1459] = Array ( [role_id] = 1459 [age1] = 2 [age2] = [age3] = 3 [age4] = 4 ) [726] = Array ( [role_id] = 726 [age1] = [age2] = [age3] = [age4] = ) [728] = Array ( [role_id] = 728 [age1] = [age2] = [age3] = [age4] = ) [724] = Array ( [role_id] = 724 [age1] = [age2] = [age3] = [age4] = ) [723] = Array ( [role_id] = 723 [age1] = [age2] = [age3] = [age4] = ) [730] = Array ( [role_id] = 730 [age1] = [age2] = [age3] = [age4] = ) [725] = Array ( [role_id] = 725 [age1] = [age2] = [age3] = [age4] = ) [720] = Array ( [role_id] = 720 [age1] = [age2] = [age3] = [age4] = ) [721] = Array ( [role_id] = 721 [age1] = [age2] = [age3] = [age4] = ) [798] = Array ( [role_id] = 798 [age1] = [age2] = [age3] = [age4] = ) [1458] = Array ( [role_id] = 1458 [age1] = [age2] = [age3] = [age4] = ) [963] = Array ( [role_id] = 963 [age1] = [age2] = [age3] = [age4] = ) [1460] = Array ( [role_id] = 1460 [age1] = [age2] = [age3] = [age4] = ) [727] = Array ( [role_id] = 727 [age1] = [age2] = [age3]
Re: Default value of INT NOT NULL fields
The above is the result of a debug($this-data) after submission to the controller. Obviously more fields would be completed in typical usage, but still there would be a lot left blank. I tried adding a default option at dbase level but Cake still passes NULL as a value which trips the NOT NULL setting in the MySQL table. If you allow NULL then that's what get's entered but as I am doing a lot of numerical calculations with this data I really need them to default to 0. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Default value of INT NOT NULL fields
You could use a db trigger. Assuming you are using MySQL: http://en.wikipedia.org/wiki/Database_trigger#Triggers_in_MySQL you should be able to extend the example to convert NULL to 0. //mathias 2009/5/9 WebbedIT p...@webbedit.co.uk: The above is the result of a debug($this-data) after submission to the controller. Obviously more fields would be completed in typical usage, but still there would be a lot left blank. I tried adding a default option at dbase level but Cake still passes NULL as a value which trips the NOT NULL setting in the MySQL table. If you allow NULL then that's what get's entered but as I am doing a lot of numerical calculations with this data I really need them to default to 0. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
