04.17.09
Posted in CakePHP, MySQL, PHP, Web Development at 3:15 pm by BrianS
As of cakePHP 1.2.1.8004 you can’t use CURRENT_TIMESTAMP as the default for a column. With MySQL when the default is set to CURRENT_TIMESTAMP the current date+time is inserted for a new row that doesn’t specifically define the value of the column.
From what I can tell, when CakePHP specifies the values for all columns when it creates a new record. A column with a defined default that is not specifically set by the user is manually set by CakePHP (rather than let MySQL handle defaults upon record insertion). But CakePHP doesn’t understand the CURRENT_TIMESTAMP keyword and so treats it as a string and wraps it in quotes. This breaks the resulting INSERT statement and you receive an error:
Incorrect datetime value: ‘CURRENT_TIMESTAMP’
Interestingly, columns that are named “created” and “modified” receive special handling by CakePHP. These columns are treated like auto-update columns by CakePHP and it sets them as expected. With the special handling of these columns in mind it is possible to get around the CURRENT_TIMESTAMP bug by following the recommended settings for created/modified columns, i.e. specifying the field as DATETIME with a default of NULL. CakePHP will automatically update the columns when inserting/updating records.
References:
Permalink
02.06.09
Posted in Web Development at 4:58 pm by BrianS
I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.
This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:
Put a formula that evaluates to empty (=”" ) in the last column of the rows that are empty:
- select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
- type the formula: =”"
- hit ctrl-enter
Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.
Permalink
03.17.08
Posted in Web Development at 10:38 am by bwalker
After making some seemingly insignificant changes to a css file, Dreamweaver CS3/9 suddenly crashed. System restart did not work nor did recreating user config. Before re-installing I chanced upon this blog entry on Google groups. Look for a comment by David Powers regarding a bug wherein if a file is exactly 8,192kb (or a multiple of), the site caching fails and the application crashes. Amazingly he was right. I added a few bytes to the file and problem solved. This has got to be the stupidest thing I’ve heard in a while.
Permalink