Some interesting findings from web-dev land…
For my own records but may also be of interest to some…
This statement performs 4 conditional left outer joins and then uses CASE to conditionally select the returned columns as a single group of columns.
So a polymorphic one to one relationship is returned in a single query.
123456789101112131415$sql = <<<SQL
SELECT *, ELT(Log.action,’$elt_list’) AS what,
CASE WHEN [...]
Quick reminder for myself of how to do a nested MySQL query with the nested statement in the parent where clause:
12345678910$sql = <<<SQL
SELECT id
FROM invoices as Invoice
WHERE id NOT IN (
SELECT parent_id
FROM invoices
WHERE parent_id > 0
GROUP BY parent_id
) AND billing_start <= ‘$end’
SQL;
Tried to do a schema snapshot of my db today (cake schema generate) to throw in SVN. I’d added an enum column to one of my tables and it threw this error:
1Notice: Schema generation error: invalid column type enum(’flat_fee’,'per_unit’) does not exist in DBO in /vhosts/site.com/accounts/cake/libs/model/schema.php on line 475
Enum must be too old-school and crusty [...]
You can use this in your MySQL WHERE clause to return records that were created within the last 7 days/week:
1created >= DATE_SUB(CURDATE(),INTERVAL 7 day)
You can also use NOW() in the subtraction to give hh:mm:ss resolution. So to return records created exactly (to the second) within the last 24hrs, you could do:
1created >= DATE_SUB(NOW(),INTERVAL 1 day)
Woo hoo! It’s possible to override CakePHP plugin views just like in Rails using the Engines plugin. Documentation on how to do it is a bit thin on the ground though.
Say you created a CakePHP plugin called ‘cms’ and you had a plugin controller called ‘pages’ inside the cms plugin. Just place the views you [...]
To reset the key values in a PHP array you can do this:
1234567891011121314151617181920212223$things = array(’a',’b',’c',’d');
unset($things[0]);
print_r($things);
#outputs
#Array
#(
# [1] => b
# [2] => c
# [3] => d
#)
# Actually do the reset.
$things = array_values($things);
print_r($things);
#outputs
#Array
#(
# [0] => b
# [1] => c
# [2] => d
#)
Ran into an interesting situation yesterday. I wanted to create some dynamic (overloading) object attributes (object member variables) in PHP with private visibility. Forcing encapsulation via getter methods. It seems this is not possible, as dynamic attributes are only ever public (see the PHP documentation).
There is however, as always, a work around! If you [...]
Today I had a log table report query that contained a nested query. It was taking ages (5mins) to complete. I investigated indexes which I had been meaning to look at for a while. Anyway I managed to bring down the query to 0.04s – I now have much love for indexes!!
Basic MySQL syntax:
1CREATE INDEX [...]
Handy, brings you back a time difference in hh:mm:ss format inside an MySQL query:
1TIMEDIFF(date_time_one, date_time_two)