Notes on Java, Solaris, PHP, LDAP…

November 17, 2007

MySQL, CakePHP tips

Filed under: Uncategorized — negev @ 5:26 pm
Tags: , ,

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’ )

– CakePHP
— have several DB ‘profiles’ in app/config/database.php
— in model class: var $useDbConfig = ‘my_db_profile’;

CakePHP

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:
use main_db;
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'

or
SET NAMES ‘utf8’;
– the above commands set several variables, list them by

SHOW VARIABLES LIKE 'c%';

Updating CakePHP and weird PHP debug output

Filed under: Uncategorized — negev @ 4:50 pm
Tags: , ,

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>

What dual references can bring

Filed under: Uncategorized — negev @ 4:33 pm
Tags: ,

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`

rather than

`Assets`.`office_code`=Office`.`office_code`

Solution: add var $primaryKey=’office_code’ in Office model class, along with findAll(..) injecting extra condition to make office_code unique

Create a free website or blog at WordPress.com.