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;
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)
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)
To dump an SQL file of just the schema (structure) of a MySQL database table you can use this command line:
1mysqldump -du username -p databasename tablename > filename.sql
You can drop the -p if you don’t have a password.
Useful if you need to move table structures around ;]
For my own records:
1UPDATE tablename SET tablefield = REPLACE(tablefield,"findstring","replacestring");
This will dump an SQL statement into a file:
1mysql -u username -p database_name -e "SQL_STATEMENT" > file.data
I recently moved over to the 64bit version of Fedora Core 8.
My plan was to compile both Apache and PHP from source, as they require a fair amount of customisations for my needs.
The Apache compile went without problems but using my regular configure directives for PHP (5.2.5) threw this error:
1configure: error: mysql configure failed. Please [...]