DATE_SUB can be very handy if you’re trying to return rows from a database table relative to a date range. As the name suggests, it SUBtracts a time value from a DATE. It expects a date, as well as an INTERVAL argument consisting of a unit (such as DAY, WEEK, or YEAR) and quantity, like […]
I wrote recently about using JOIN when needing to incorporate a lookup in a MySQL delete command. What if one needs to do the same when inserting into a table instead? This time it’s SELECT to the rescue. Imagine we have a simple bookmarking function that stores a user id and article id in a […]
Deleting from a table is simple enough. If, for example, we wanted to remove all the comments from a single user and we already know that user’s id we could run this command (employing the EasyPDO library in PHP): <?php $db->ExecuteSQL(“DELETE FROM comments WHERE user_id=?”,”i”,$id); ?> Now imagine we only have the user’s email and […]
When two models have a HABTM relationship (resisting the urge to make a joke) there is necessarily a shared association table. But what if we want to store more information in that table? While, for example, an association table between Events and Waiters (with Events HABTM Waiters) would be very useful, it would be even […]
Qualifying a query in CakePHP is as simple as adding conditions to the conveniently-named “conditions” array, like so: // return array of all musicians $all = $this->Musician->find(‘all’); // return array of all available reed players $only_available_reeds = $this->Musician->find(‘all’, array( ‘conditions’ => array( ‘category’ => ‘reeds’, ‘status’ => ‘available’ ) )); If the array contains multiple […]