RIA CFML Flex Flash

MySQL Find and Replace

Wed, 09 Sep 2009 16:40:00 -0700

I didn't know until now about the replace function in MySQL. Evidently you can do case-sensitive search and replace pretty easily. update table set field = replace(field,'before','after'); I'm not sure about using regex but you can do multiple fields at once. Like this: update tblblogentries set body = replace(body,'ColdFusion','ColdFusion'), title=replace(title,'ColdFusion','ColdFusion') Don't even say it ;p

cfargument type and mysql time

Fri, 31 Jul 2009 10:25:00 -0700

Not having blogged in a while I thought I'd put out something, so here's a little ditty about something I'm looking at right now. How to handle matching ColdFusion types to the time data type used in Mysql. I have a table with columns of type "time" in my MySQL rdbms and I will need to use CF to retrieve and store time type data such as 08:23:00, ie. 8:23am. Note that MySQL stores time military style, ie. 18:23:00 is 6:23pm. So the first thing to think about is passing time values in and out of components through the cfargument tag with the right type choosen. Funny thing is that you can use three different types, 'Any', 'String', 'Date', and even 'Numeric'. How numeric works I don't know, but it does. Personally I've just been using 'Any' as a general rule, until they actually add a 'Time' type, if ever. Using that argument within your query, or cfqueryparam to be more exact, you'd use the cfsqltype of 'cf_sql_time'. Easy enough right? And if your passing around datetime values you can always use createODBCTime function to format it for the MySQL time column.

Moving to CF 8 and MySQL 5

Mon, 15 Oct 2007 17:17:00 -0700

Issues: Moving from CF 7 and MySQL 4 to CF 8 and MySQL 5 1. In MySQL 4 you could pass an empty field ("") to a primary key column that is auto insert, in MySQL 5 you can not. Simply take that out of your insert query completely. 2. MySQL 4 was much more relaxed and forgiving about fields not checked to allow null. If a field did not allow null and you passed in nothing on insert for that field, or did not include that column on insert, it would still work. However, for MySQL 5, if a field is not nullable, you must pass in something on insert. Simply allow null for that field or give that field a default value. I will update this if I find any other issues.

MySQL - delete using

Sun, 07 Oct 2007 17:41:00 -0700

Records in table A reference records in table B. Such as a customer table might have a reference to an orders table where each order record has a customer ID column. How do you delete customer records that do not have any matching orders?


DELETE FROM customers A USING
A LEFT JOIN orders B 
ON A.customerid = B.customerid
WHERE B.customerid IS NULL

Remember that one.