Tag Archives: PHP

Zend_Db syntax cheat sheet

A small cheat sheet with simple examples for the Zend_Db syntax in Zend Framework. A smooth way to write sql that will work on many different databases

Connect to database

$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '127.0.0.1',
    'username' => 'myUser',
    'password' => 'myPassword',
    'dbname'   => 'myDatabase'
));

SELECT statement
eg.

SELECT fname, course, grade FROM students 
   WHERE student_id = {$student_id} ORDER BY grade DESC

is written in Zend_Db syntax like this:

$db->select()
  ->from('students', array('fname', 'course', 'grade'))
  ->where('student_id = ?', $student_id)
  ->order('grade DESC');

If you want to fetch all columns (*), just remove the array(‘name’,…) in the from function

INSERT statement
eg.

INSERT INTO students(id, email, passwrd, fname, address, active) 
   VALUES (id + 1, '{$email}', '{$passwrd}', '{$fname}', '{$address}', 1);

Zend_Db syntax:

$student = array( 'id' => new Zend_Db_Expr('id + 1'),
                  'email' => $email,
		          'passwrd' => $passwrd,
		          'fname' => $fname,
		          'address' => $address,
		          'active' => '1');

$db->insert('students', $student);

UPDATE statement
eq.

UPDATE students
   SET passwrd = '{$passwrd}',
       fname = '{$fname}',
       address = '{$address}',
       active = 1
WHERE id = '{$student_id}'

Zend_Db syntax:

$student = array('passwrd' => $passwrd,
                 'fname' => $fname,
                 'address' => $address,
                 'active' => '1');

$db->update('students', $student, 'id = ' . $student_id);

DELETE statement
eq.

DELETE FROM students WHERE id = '{$student_id}'

Zend_Db syntax:

$db->delete('students', 'id = ' . $student_id);

Tested in Zend Framework 1.10.8 on OSX 10.7.4

Setup SQLite in Zend Framework

Like many things in Zend Framework this is very simple. I will here show how I usually do.

First we set the required parameters in application.ini:

[production]
...
;Setup database (SQLITE)
resources.db.adapter = "PDO_SQLITE"
resources.db.params.dbname = APPLICATION_PATH "/../db/basic.db"
...

This tells Zend that we will be using a SQLite database (PDO_SQLITE) and then also the path to the SQLite db file (basic.db)

For easy access we will now add the database handle to the Zend registry:
Bootstrap.php

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap {
 ...
    protected function _initDbAdapter() {
        $this->bootstrap('db');
        $db = $this->getResource('db');
        Zend_Registry::set('db' , $db);
    }
 ...
}

Then whenever we need to use the database we collect if from the Registry

// Get SQLite db handle from the registry
$db = Zend_Registry::get('db');

// Use the database
$resultset = $db->fetchAll("SELECT id, name FROM projects");

Done!

Tested on Mac OSX 10.6.8 and Zend Framework 1.10.8

Create a logger in Zend Framework using application.ini

I am here going to show how I usually setup a logger in my Zend applications. First we add the logger settings into the application.ini:

[production]
...
;Setup logger (file)
resources.log.stream.writerName = "Stream"
resources.log.stream.writerParams.stream = APPLICATION_PATH "/logs/basic.log"
resources.log.stream.writerParams.mode = "a"
resources.log.stream.filterName = "Priority"
resources.log.stream.filterParams.priority = 7
...
  • WriterName is the type of writer you want. I choose “Stream” here for logging to file
  • WriterParams is the parameters which are sent to the writer. Since i use stream i set the path to the logfile and set the “mode” to “a” (append to file)
  • FilterName is the name of the filter type. I use “Priority” so I can use loglevels
  • FilterParams is set to 7 (Debug) so that all loglevels are logged

Now we will make the logger easily accessible by putting a handle in the Zend register like this.
Bootstrap.php

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap {
...
    protected function _initLogger() {
        $this->bootstrap("log");
        $logger = $this->getResource("log");
        Zend_Registry::set("logger", $logger);
    }
...
}

Now, to use the logger in your application just do like this:


//Get the logger handle from the register
$logger = Zend_Registry::get('logger');

//Use the logger
$logger->log("message", 3);//Second argument is the loglevel (0-7)

Valid loglevels (straight from the Zend code:Log.php):

EMERG   = 0;  // Emergency: system is unusable
ALERT   = 1;  // Alert: action must be taken immediately
CRIT    = 2;  // Critical: critical conditions
ERR     = 3;  // Error: error conditions
WARN    = 4;  // Warning: warning conditions
NOTICE  = 5;  // Notice: normal but significant condition
INFO    = 6;  // Informational: informational messages
DEBUG   = 7;  // Debug: debug messages

Tested on Mac OSX 10.6.8 and Zend Framework 1.10.8