28 Feb 07
Mutliple databases and PHP frameworks
– Code Igniter (OpenWorld)
— in a controller: $this->load->model( ‘Officemodel’, ”, $this->config->item(‘my_db_profile’) );
— in application/config/autoload.php -> add a new config file for the other DB, e.g. application/config/database_2nd.php:
$autoload[‘config’]= array( whatever-was-there, ‘database_2nd’ )
— have several DB ‘profiles’ in app/config/database.php
— in model class: var $useDbConfig = ‘my_db_profile’;
MySQL 4.1.13-standard: do not have DB user and DB name same, otherwise it used to cause problems.
— however, same DB user and database name worked on another instance of MySQL 4.1.13-standard
MySQL -copying a value from one DB or table to another:
UPDATE cities SET name=(SELECT name FROM old_main_db.cities WHERE id=337) WHERE id=337;
CakePHP multi-DB application:
Have following set in the model(s):
var $useDbConfig = ‘test’;
Unicode in MySQL:
SET NAMES 'charset_name' COLLATE 'collation_name'
SET NAMES ‘utf8’;
– the above commands set several variables, list them by
SHOW VARIABLES LIKE 'c%';
23 Feb 07
Updating a closely tied library (CakePHP) in an existing application
– comparing plain original CakePHP and existing app: diff -r cake ~/my-app/trunk/cake
– copying new CakePHP over it
– applying any of the custom changes
— if we had plain CakePHP in CVS/SVN, then we could automate it – except where custom changes were in conflict with Cake update
Strange debug outputs from PHP
When debugging an SQL query in PHP, I’ve displayed it using ‘echo’ or ‘print_r’. Most of the query displayed fine but conditions which used lower-than, grater-than – for these browser hide a part of them and displayed only OR (‘2006-11-25′<‘2007-02-28’) instead of (‘2006-11-25′<Experience.created AND Experience.created<‘2007-02-28’) – because browsers interpret <… as HTML tag opener. However, it displays <= and >= correctly!
Solution: Always show debug printouts within <pre>…</pre>
22 Feb 07
Symptoms of the problem: Weird – inconsistent DB results in CakePHP.
Root of evil: non-standard conventions. I have a DB table Office that I refer to via 2 columns:
- id – unique within the table
- office_id – same for current and historical records of an office. I differentiate current and historical offices by another column – disposition, which is ‘active’ or ‘historical’. You can also have ‘pending’ and ‘lameduck’ if the office was edited but the change was not confirmed yet.
The actual problem: My other tables referred to Office via office_code and filtered them by disposition=’active’. However, I didn’t tell CakePHP about it. Therefore CakePHP generated queries like
LEFT JOIN `officies` AS `office` ON `Assets`.`office_code`=`Office`.`id`
Solution: add var $primaryKey=’office_code’ in Office model class, along with findAll(..) injecting extra condition to make office_code unique