EnglishSvenska

CakePHP Database Logger

I needed to replace the default file log in CakePHP with one based on mysql instead. I shared the project on Bitbucket if someone else would like to do something similar.

https://bitbucket.org/sebnil/cakephp-database-logger/wiki/Home

Why is this better than file log?

  1. Easier to search
  2. Handles large amount of logging much better
  3. Has better performance (at least in some cases)
Tagged with: , ,

Sök och ersätt sträng i MySQL

Jag behövde nyligen söka i en tabell och ersätta kolumnvärden. Exempelvis kan detta användas för att över en hel blogg ersätta ett visst ord eller fras med något annat.

UPDATE table_name SET column_name = REPLACE(column_name,'original_string','replace_string');

Using regular expressions in CakePHP and MySQL

If you want to find something in the database using regular expressions:

$domainNamed = $this->DomainName->find('all', array(
'conditions' => array(
'name REGEXP "^www.i"'
),
'fields' => array(
'name'
),
'contain' => false,
'limit' => 10
));

The code above will find all domain names that start with "www.".

USE INDEX the CakePHP way

Well ok not 100% the cakephp way but better than just using $this->query(...).

I wanted it to look like this:

$this->find('all', array(
'fields' => array('id', 'name'),
'use' => 'index_name'
));

To get this working you need to modify dbo_source. Copy it from cake/libs/model/datasources/dbo_source.php to app/models/datasources/dbo_source.php.

You can download my modified dbo_source.php . I have commented the modified lines with "added". I have only tested this on MySQL. I do not know if it will work on other databases. Also I am not sure if my solution is the best. Please feel free to comment if you have any improvements.

 

To manually modify the dbo_source to support the USE INDEX syntax:

in function read:

/* added */
if (!isset($queryData['use']))
$queryData['use'] = null;
/* /added */

in function generateAssociationQuery:

return $this->buildStatement(
array(
'fields' => array_unique($queryData['fields']),
'table' => $this->fullTableName($model),
'alias' => $model->alias,
'limit' => $queryData['limit'],
'offset' => $queryData['offset'],
'joins' => $queryData['joins'],
'conditions' => $queryData['conditions'],
'order' => $queryData['order'],
'group' => $queryData['group'],
'use' => $queryData['use'] // added
), $model
);

in function buildStatement:

return $this->renderStatement('select', array(
'conditions' => $this->conditions($query['conditions'], true, true, $model),
'fields' => implode(', ', $query['fields']),
'table' => $query['table'],
'alias' => $this->alias . $this->name($query['alias']),
'order' => $this->order($query['order'], 'ASC', $model),
'limit' => $this->limit($query['limit'], $query['offset']),
'joins' => implode(' ', $query['joins']),
'group' => $this->group($query['group'], $model),
'use' => $query['use'] // added
));

in function renderStatement:

case 'select':
/* added */
if (empty($use)) // added
return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
else
return "SELECT {$fields} FROM {$table} {$alias} {$joins} USE INDEX ({$use}) {$conditions} {$group} {$order} {$limit}";
/* /added */
break;

Lock tables in CakePHP

I needed to do some table locking. Just call the method from the controller to lock and unlock your tables. The code below is only tested with MySQL and InnoDb tables. Put it in app_model.php

{code }

<?php

class AppModel extends Model {

    function lockTableRW() {

$this->lockTable('READ');

$this->lockTable('WRITE');

    }

    function lockTable($type='READ') {

$dbo = $this->getDataSource();

$dbo->execute('LOCK TABLES '.$this->table.' '.$type);

    }

    function unlockTables() {

$dbo = $this->getDataSource();

$dbo->execute('UNLOCK TABLES');

    }

}

 

 

Template for CakePHP

I have decided to share a template I made. It is a modification of the default template in CakePHP.

  • Simple menu system
  • Works on most screens. The menu is flexible.
  • Uses CSS gradient for Firefox and Webkit browsers
  • Some text effects
  • Works best in modern browsers

Images:

cakephp template1 cakephp template2

Download the template