Zend Framework: Select statement and where clause examples

17 Apr

When it comes to retrieving data, nothing can be done unless using select statement. Sql is as easy as simple English, however we technical people don’t know much about languages and when think as functions, classes and objects. We love to use functions, modules, classes and objects. In sql

SELECT * FROM table_name

WHERE condition

Statement can easily be understood, however, how if we do the same things using functions and objects etc. It would be great, no doubt.

You’d be glad to work in Zend Framework if you think as I think. I feel comfortable using functions and objects. Another important thing is, you don’t need to learn sql if you don’t want, but only need to learn functions that do everything under the hood.

Zend Framework team has done a fabulous job, providing methods for interacting with database. Though all the database related things are impossible to cover in single post, I’d discuss the usage of select statement here.

I always encourage creation of separate php file for each of your database table in your models directory and create a custom class extending from Zend_Db_Table or Zend_Db_Table_Abstract.

The code can be written as

<?php

class Users extends Zend_Db_Table

{

protected $_name = ‘tbl_users’;

}

I assume that you have a table named “tbl_users”.

After extending your class from Zend_Db_Table, you get access to all the methods defined in Zend_Db_Table as well as Zend_Db_Table_Abstract, because Zend_Db_Table extends Zend_Db_Table_Abstract. The method available are in those two table are insert, update, delete etc.

Although you can use these methods provided by ZF in your controller by creating object of your own class. The code might be

$users = new Users();

$users->insert(array());

Keep in mind that here I have given an empty array, you can give associative array to insert specific values in specific columns of the tbl_users.

Although built-in function exist, however sometime you need to define your own custom methods.

Like

function getUserDataById($id) {

}

This function return data based on the id given. The code it may contain

Public function getUserDataById($id) {

$select = $this->_db->select()

->from($this->_name)

->where(‘id = ?’, $id);

$result = $this->getAdapter()->fetchAll($select);

return $result;

}

This is what I was intending to tell you. The from and where clauses. Its all seems simple. You don’t need to care how ZF build query for you.

The first line say, select and then from method is called, getting the name of the table. The where clause tells that id should be set to what we have sent as parameter.

Its very simple, isn’t it?

Wow, a question comes to mind how this query looks like in sql. Well, I want to say that ZF will create the following query for you.

SELECT * FROM tbl_users

WHERE id = 3

Yes if you have sent 3 as parameter. You can sent any number and the query will retrieve data based on that id.

Let’s make this query a bit complex, actually simple.

How if you want to select specific column only?

It will easy to do than say using ZF.

$select = $this->_db->select()

->from($this->_name,array(‘col2′,’col3’))

->where(‘id = ?’, $id);

I have made only one change, an additional array is sent as parameter to the from method.

Here col2 and col3 are the name of the column in the table tbl_users.

Another question can also come straight to mind.

How to And two condition? Like

WHERE id = 3 and name = ‘a’

It again very simple, write something like this.

$select = $this->_db->select()

->from($this->_name,array(‘col2′,’col3’))

->where(‘id = ?’, $id)

->where(‘name = ?’ , ‘a’);

Keep in mind that you can place “=” with any valid sql operator.

This will make an “AND” query.

What if you want to make “OR” query?

Its simple is that.

$select = $this->_db->select()

->from($this->_name,array(‘col2′,’col3’))

->where(‘id = ?’, $id)

->orwhere(‘name = ?’ , ‘a’);

I replaced where with orwhere, as simple as this.

Hopefully I’ll discuss some more magic of Zend_Db in coming days.

Cheers.

2 Responses to “Zend Framework: Select statement and where clause examples”

  1. Yaroslav Vorozhko April 19, 2009 at 9:23 am #

    Hi Faheem,

    First of all nice article for beginners, but i have several questions.

    Why do you use $this->_db->select() instead of $this->select()?
    Why do you use $this->_db instead of $this->getAdapter ? Is there a difference between $this->_db and $this->getAdapter?

  2. Andres August 10, 2010 at 7:55 pm #

    How should i do to get ????

    SELECT * FROM tbl_users WHERE id = 3 AND city = 5 AND (name = ‘a’ OR age > 15)

    thanks for the help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: