Category Archives: programming

Replace () / . characters in phone numbers with dashes (MySQL database)

If you have a MySQL database containing phone number records in varying formats, you may want to standardize them. New records can easily be handled by checking data entry against your desired format (via JS or whatever method you like). However, existing records must be dealt with. That is where an UPDATE statement combined some handy MySQL functions can assist us: // Replace forward slash with dash UPDATE db_phonenumbers SET phone_field = REPLACE(phone_field, ‘/’, ‘-‘) // Replace close parenthesis with

Data truncated for column ‘status’ at row 1 [SOLVED]

Recently I encountered the following error: Data truncated for column ‘status’ at row 1 This was specifically in a PHP based web hotel/reservations app that uses MySQL for the database backend. When trying to add a new dropdown option value of ‘departed’, the form submission would always fail with the aforementioned error. Thankfully, the solution was easy once I checked the table/field definitions. The column ‘status’ was of type enum and thus needed to have the ‘departed’ value specifically enumerated in

NEW version of Robot Turtles [20% off promo coupon code]

A rather exciting email from the creator of Robot Turtles (the fun board game that teaches kids how to program). I pre-ordered a version and you can even use promo/coupon code TEACHERS at for 20% off! Here is the email in its entirety: {START} First a brief summary (or, as the kids say, TL;DR): A new, enhanced version of Robot Turtles is available for preorder.   Next, a long rambling version of same.   Five short months ago we closed

MySQL: Count the total occupancy rate for a time period (month)

Recently I encountered a somewhat interesting MySQL/PHP task: determine occupancy for a reservation system. This entailed determining how many reservation days fall within a particular month. It was not possible to simply determine each reservation length (datediff from/to) and sum them because some reservations started before the month in question and other reservations lasted until the month was over. Therefore, one solution is to get the reservation lengths then compare the start and end of the reservation to the start