Creating Column and Table Aliases

Continuing with the from() method, I'll now touch on aliases. You use aliases on tables and columns when you want to name a column differently from the way it's presented in the table or when you want to assign a different name to the table you are querying. You would use an alias when you have a statement that calls two or more tables that contain columns of the same name such as id. In such cases, you need to distinguish which id column you want to pull data from.

The Zend_Db_Select object allows you to create table aliases and column aliases by using the first and second parameters in the from() method call. Let's start by creating an alias on the artists table, as shown in Listing 5-28. You'll give the table the alias a in the next example and update the columns to use aliases as well. The final statement is the following:

SELECT "a"."id" AS "artist id", "a"."artist_name" AS "name", "a"."genre" FROM "artists" AS "a"

Listing 5-28. Implementing Table and Column Aliases

* Test - Object Oriented Select Statement

public function testoostatementAction() {

//Create DB object require_once "Db/Db_Db.php"; $db = Db_Db::conn();

//Create the statement

//SELECT "a"."id" AS "artist id", "a"."artist_name" AS "name", //"a"."genre" FROM "artists" AS "a" $select = new Zend_Db_Select($db);

//Determine which columns to retrieve. //Determine which table to retrieve data from. $columns = array("artist id" => 'id', "name" => 'artist_name', "genre" => 'genre');

$tableInfo = array("a" => "artists");

$statement = $select->from($tableInfo, $columns);

//Query the Database

//Compare Statement echo $statement->_toString();

//Supress the View

$this->_helper->viewRenderer->setNoRender();

Start by making the changes to the $columns array variable. Instead of using a standard array, you create a key-value array where the key is the alias for the column and the value is the column to retrieve. The next change you make is to the first parameter in the from() method. You update the parameter from a string to a variable: $tableInfo. The variable contains a key-value array where the key represents the alias you plan on using, a, and the value is the name of the table.

If you now execute the object-oriented statement by loading the URL http://localhost/artist/testoostatement, it will retrieve the same number of records that the query in Listing 527 returned, and the desired statement will display on the screen.

Was this article helpful?

0 0

Post a comment