Object-Oriented SELECT Statements - Zend

The Zend_Db_Select class allows developers to create SELECT statements using the standard clauses ranging from a simple SELECT clause to a GROUPING clause; it automatically escapes the user-entered input and removes the overhead of filtering any possible SQL injection attack. You can then use the SELECT statement to query the database.

Lets go through each clause, working with a couple of examples that range from simple statements to the more complex JOIN statements you typically see.

You need a SQL statement to reference before you start an object-oriented statement. You start by creating a simple SELECT statement:

SELECT * FROM `artists`

The statement queries the artists table in the database and retrieves all columns and records stored in the table (it is indicated by the * wildcard symbol in the columns list). Translating the statement into an object-oriented call using the Zend_Db_Select class, you create the initial SELECT portion of the script, as shown in Listing. Yes, simply calling the Zend_Db_Select class creates a statement, but this statement is not that smart; it wonft do anything at this point.

Listing Simple Object-Oriented Statement: testoostatementAction

At this point, if you wrote out the POSQL created in Listing, the result would be a string such as the following:"SELECT"Copy the code shown in Listing to the ArtistController.php file.

Querying Records from Tables Using from()

You need to give the SQL statement youfre using a bit more power and intelligence. The statement needs to know from which table you will fetch data. Using the same Zend_Db_Select object, youfll use ‘the from() method to identify a table and the __toString() method to convert the SELECT statement youre currently building to a POSQL string. Doing this enables you to compare the object-oriented statement with the intended POSQL.

Listing builds on previous listing by using the from() method to distinguish which table the SQL statement should retrieve data from.

The from() method accepts three parameters:

  • The initial parameter is the name of the table you want to reference as a string.
  • The second parameter is a list of columns. To retrieve all the columns, use the * wildcard symbol; if you want to specify specific columns, use an array. The default value is *.
  • The third parameter (optional) is the schema name you want to reference.

Listing Using Object-Oriented from(): Updated testoostatementAction()

 /**   *  Test - Object Oriented Select Statement   */    public function testoostatementAction() {  //Create DB object   require_once "Db/Db.php";   $db = Db_Db::conn();  //Create the statement   //Select * FROM `artists`;   $select = new Zend_Db_Select($db);   $statement = $select->from('artists');  //Compare Statement   echo $statement->__toString();  //Supress the View   $this->_helper->viewRenderer->setNoRender(); } 

The table is identified by using the from() method and passing in the artists string value. At this point, the full POSQL is the following:

SELECT `artists`.* FROM `artists`

You can use the __toString() method to view the generated statement. Load the URL http://localhost/artist/testoostatement and view the statement.

Querying Specific Columns

Sometimes you don’t need all the table columns, so the POSQL must change from using the wildcard symbol to identifying which columns you want to fetch. Updating the example statement previously mentioned, you’ll pull three columns for each record, the ID of the artist, the name of artist, and the genre the artist belongs to. The new statement looks like this:

SELECT `artists`.`id`, `artists`.`artist_name`, `artists`.`genre` FROM `artists`

Using the from() methodfs second parameter, pass in an array. You can create an array containing string elements representing the individual column names.

Listing Identifying Specific Columns to Fetch

 /**   * 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  `artists`.`id`, `artists`.`artist_name`, `artists`.`genre`  //FROM  `artists`   $select = new  Zend_Db_Select($db);  //Determine which columns to retrieve.   $columns =  array('id', 'artist_name', 'genre');   $statement =  $select->from('artists', $columns);  //Compare Statement   echo $statement->__toString();  //Supress the View   $this->_helper->viewRenderer->setNoRender(); }

For ease of use, create a $columns array variable that contains a list of all the columns you want to fetch from the table and pass it into the from() methodfs second parameter. Each element in the array represents a column name in the table you are fetching records from.

Executing Object-Oriented Statements

The statement now contains enough intelligence to determine which columns to pull from the table. Its time to execute the statement and fetch data.

There are two ways to execute the query youfve built. One way is to use the database method query(), which accepts a string or Zend_Db_Select object, as shown in Listing. The other way is by calling query() directly from the Zend_Db_Select object. Both methods are followed by a call to the desired fetch method shown in Table. You can then use an iteration function to iterate through the result set.

Listing Executing Object-Oriented Statement

/** 
  *  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 `artists`.`id`, `artists`.`artist_name`,  `artists`.`genre`
//FROM `artists`
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
  $columns = array('id', 'artist_name', 'genre');
  $statement = $select->from('artists', $columns);
//Query the Database
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

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. 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 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
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//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 it will retrieve the same number of records that the query in Listing returned, and the desired statement will display on the screen.

Narrowing Down the Search with a WHERE Clause

You can narrow down your searches with a WHERE clause just like any standard POSQL statement. A WHERE clause allows you to fetch all the records from a table with a given condition. In some cases, you want to retrieve all the records in a table that match a given string, are higher than a specified value, or do not meet a condition.

Continuing with the statement previously created, let’s expand it and return only the artist information for the artist Groove Armada. The new statement looks like this:

 SELECT  `a`.`id`, `a`.`artist_name` AS `name`, `a`.`genre` 
FROM `artists` AS  `a` 
WHERE (artist_name='Groove Armada')

Translating the preceding statement into an object-oriented call requires you to use the where() method in the Zend_Db_Select object. The where() method accepts two parameters:

  • The initial string parameter represents the complete condition the record must match for it to be included into the result set. Placeholders can also be used in the condition.
  • The second optional parameter is a string representing the binding value.

Listing adds the new method where() to the code shown in the next listing. Because you want the result set to contain only the artist named Groove Armada, you pass in the value artist_name=?. The artist_name is the column you are identifying to match records to, and? is the placeholder that will be replaced by the escaped value used in the second parameter (in this case, Groove Armada).

Listing Using the Where Clause

/**
  *  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`, `a`.`artist_name` AS `name`, `a`.`genre`
//FROM `artists` AS `a` WHERE (artist_name='Groove Armada')
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
//Determine which table to retrieve data from.
  $columns = array("id" => 'id',"name" => 'artist_name',"genre" => 'genre');
  $tableInfo = array("a" => 'artists');
  $statement = $select->from($tableInfo, $columns)
  ->where("artist_name=?", 'Groove  Armada');
//Query the Database
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

Load the URL http://localhost/artist/testoostatement to see the resulting query statement.

Now let’s update the POSQL statement and search for all records with the artist name Groove Armada that belongs to the electronic genre. You’ll add a new condition to the SELECT statement. You want to be absolutely sure that you are retrieving the artist Groove Armada because there might be other Groove Armadas in different genres.

Adding the new condition to the POSQL statement requires the AND clause. The new POSQL statement looks like this:

SELECT `a`.`id`, `a`.`artist_name` AS `name`, `a`.`genre`
 FROM `artists` AS `a`
 WHERE (artist_name='Groove Armada') 
AND (genre='electronic')

Using the object-oriented approach, you create the new statement using another where() method call. Using another where() method call is the same as appending an AND clause to the statement. You create the new WHERE clause the same way as in the previous example. You pass in the condition as a string in the first parameter, as shown in Listing.

Listing Using an Additional where()

/**
  *  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`, `a`.`artist_name` AS `name`, `a`.`genre`
  //FROM `artists` AS `a`
  //WHERE (artist_name='Groove Armada') AND (genre='electronic')
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
//Determine which table to retrieve data from.
  $column = array("id" => 'id',"name" => 'artist_name',"genre" => 'genre');
  $tableInfo = array("a" => 'artists');
  $statement = $select->from($tableInfo, $column)
  ->where("artist_name=?", 'Groove Armada')
  ->where('genre=?', 'electronic');
//Query the Database
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

Of course, if you’re a fan of electronic music you might be telling yourself that an electronic genre is much too broad, and Groove Armada might be placed under the house genre instead. To satisfy this requirement, you need a way to translate this new condition onto a statement. You can’t use the AND clause because an artist can belong to only one genre at a time. You use the OR search clause in the statement to check both the electronic and house genres. The new POSQL statement is the following:

SELECT  `a`.`id`, `a`.`artist_name` AS `name`, `a`.`genre`
 FROM `artists` AS `a` 
WHERE  (artist_name='Groove Armada')
 AND (genre='electronic') OR  (genre='house')

Instead of using another where() method, you use the orWhere() method, which accepts the same number and type of parameters as the standard where() method. The final testoostaetmentAction() looks like Listing.

Listing Using orWhere()

/**
  *  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`, `a`.`artist_name` AS `name`, `a`.`genre`
  //FROM `artists` AS `a`
  //WHERE (artist_name='Groove Armada')
  //AND (genre='electronic') OR (genre='house')
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
  //Determine which table to retrieve data from.
  $columns = array("id" => 'id',"name" => 'artist_name',"genre" => 'genre');
  $tableInfo = array("a" => 'artists');
  $statement =  $select->from($tableInfo, $columns)
  ->where("artist_name=?", 'Groove Armada')
  ->where('genre=?',  'electronic')
  ->orWhere('genre=?',  'house');
//Query the  Database
  $results =  $db->query($statement);
  $rows =  $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

Querying Two or More Tables using JOIN

Demonstrating the benefits of using the Zend_Db_Select statement requires you to expand on simply retrieving a specific artist. You now want to retrieve all the fans for a specific artist. In the application, users have lists of artists they listen to; if users contain an artist, they are considered to be a fan of that artist.

Constructing the POSQL statement requires two tables: artists and accounts_artists. The new POSQL statement is the following:

SELECT `a`.`id` AS `artist id`,  `a`.`artist_name` AS `name`, `a`.`genre`,
`aa`.`account_id` AS `user_id`,  `aa`.`created_date` AS `date_became_fan` FROM
`artists` AS `a` INNER JOIN  `accounts_artists` AS `aa` ON aa.artist_id = a.id

The Zend_Db_Select object contains six types of JOIN statements that can be executed successfully if the RDBMS supports it. Zend_Db_Select supports inner joins, left joins, right joins, full joins, natural joins, and cross joins. The statement uses the INNER JOIN SQL call, so you use the join() method. A full list of available join() methods can be seen in Table.

Table Join Methods

Table Join Methods

Taking the inner join used in the preceding statement, use the join() method call:

  • The first parameter accepts a mixed type. It can be a key-value array where the key is the alias you want to use for the table, and the value is the name of the table you want to use.
  • The second parameter is the condition on which you want to join the tables.
  • The third parameter contains the columns you want to fetch from the table. This parameter also accepts a key-value array where the key is the alias to the column and the value is the column name.

Let’s now implement a join() method and transform the query to an object-oriented statement. Open the ArtistController.php file and create a new action: testoofansAction.

The new action created in the ArtistController.php file is shown in Listing and uses much of the code created in the previous examples. Again, you create a $columns variable that contains the column you want to retrieve as well as specify the $tableInfo variable that contains the alias and the table you want to fetch data from.

Listing testoofansAction using join()

/**
  *  Test - Get All Fans
  *
  */
  public function testoofansAction(){
//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`,aa`.`account_id` AS `user_id`,
//`aa`.`created_date` AS `date_became_fan`
//FROM `artists` AS `a`
//INNER JOIN `accounts_artists` AS `aa` ON aa.artist_id = a.id 
   $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
  //Determine which table to retrieve data from.
  $columns = array("artist id" => 'a.id',"name" => 'a.artist_name',"genre" => 'a.genre');
  $tableInfo = array("a" => 'artists');
  $statement = $select->from($tableInfo, $columns)
  ->join(array("aa" => 'accounts_artists'),
  'aa.artist_id  = a.id',
  array("user_id"  => 'aa.account_id',"date_became_fan" =>
  'aa.created_date'));
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

After the columns and the table are set, you can use the join() method. You supply it with an array containing the alias you want to give the accounts_artists table: aa. You also supply the join condition, which specifies the column that will associate the records to each other in the artists and accounts_artists tables. In this case, you are using the artists.id and the accounts_artists.artist_id columns. Finally, you supply the third parameter with an array containing the columns and its aliases you want to use.

Load the URL http://localhost/artist/testoofans to see the resulting POSQL that is created.

Limiting and Ordering the Result Set

You returned all artists along with their fans, but it didnft return many rows because the system is not full of user data at the moment. What would happen if there were millions of accounts and associated artists? You need a way to limit the number of results returned.

Many RDBMSs contain the LIMIT clause, which allows you to pass two parameters: a starting index and the number of rows to retrieve. The Zend_Db_Select object also contains a way to limit the amount of rows returned from the database.

Youll now transform the object-oriented statement into the following statement, which only appends a LIMIT to the number of rows you return:

SELECT `a`.`id` AS `artist id`, `a`.`artist_name` AS `name`, `a`.`genre`,
`aa`.`account_id` AS `user_id`,  `aa`.`created_date` AS `date_became_fan` FROM
`artists` AS `a` INNER JOIN  `accounts_artists` AS `aa` ON aa.artist_id = a.id
LIMIT 10

The preceding statement returns only ten records from the list. By using the limit() method along with a parameter of 10, you can achieve the same results. Listing contains the updated $statement, which uses a limit() method.

Ordering Result Sets

You now want to return the freshest information displayed for the users. What you now want is the ability to display all the fans (based on the date when they became fans).

To accomplish this, you need to use the ordering functionality available in the RDBMS. The ORDER BY clause allows you to order the records based on a column. You can sort the data by descending or ascending order. If the column type is an integer, descending order sorts the column from greatest to least; if the column is noninteger, the information will be returned alphabetically reversed.

Using this concept you now use the order() method call as well as demonstrate the use of limit()

Listing testoofansAction Using order()

/**
  *  Test - Get All Fans
  *
  */
  public function testoofansAction(){
//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`, 
`aa`.`account_id` AS `user_id`,
//`aa`.`created_date` AS `date_became_fan
//FROM `artists` AS `a`
//INNER JOIN `accounts_artists` AS `aa` ON aa.artist_id = a.id
//ORDER BY `date_became_fan` DESC LIMIT 10
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
//Determine which table to retrieve data from.
  $columns = array("artist id" => 'a.id',"name" => 'a.artist_name',"genre" => 'a.genre');
  $tableInfo = array("a" =>'artists');
  $statement = $select->from($tableInfo, $columns)
  ->join(array("aa"  => 'accounts_artists'),
  'aa.artist_id = a.id',
  array("user_id" => 'aa.account_id',"date_became_fan" =>
  'aa.created_date'))
  ->order("date_became_fan DESC")
  ->limit(10);;
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

The order() method accepts a mixed type parameter. It will accept a single string value containing the columns you want to order, immediately followed by the way in which you want to order the content: DESC or ASC. You can also use an array that provides each element within the array in the same format. If you do not present the type of ordering, the Zend_Db_Select object uses DESC by default.

Database Expressions

The Zend_Db_Select object also allows you to use database expressions such as NOW(), SUB(), ADD(), and DATE_FORMAT(), among others. Using database expressions is easy and can be done within the from() method call.

Create a testoocountAction() action within the ArtistController.php file and copy the code shown in Listing. This POSQL query is generated:

SELECT COUNT(id) AS `total_fans` FROM `accounts_artists` AS `aa`

Listing Implementing the count() Method

/**
  *  Test – Database expression.
  *
  */
  public function testoocountAction(){
//Create Db object
  require_once "Db/Db_Db.php";
  $db = Db_Db::conn();
//Create the statement
// SELECT COUNT(id) AS `total_fans` FROM `accounts_artists` AS  `aa`
  $select = new Zend_Db_Select($db);
//Determine which columns to retrieve.
//Determine which table to retrieve data from.
  $columns = array("total_fans" =>'COUNT(id)');
  $tableInfo = array("aa" => 'accounts_artists');
  $statement = $select->from($tableInfo, $columns);
  $results = $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

The example uses the $columns parameters in the from() method to simply pass in a database expression directly into the array. In this case, you’re taking the count of all results returned with the given condition.

Another popular query statement is to fetch all distinct records in a table. The DISTINCT() call allows you to fetch all unique records. For example, if you want to query the Artists table and fetch all distinct genres currently in the system, you can create the following SQL statement and execute it:

SELECT DISTINCT `a`.`genre` FROM `artists` AS `a`

Let’s go ahead and create the object-oriented equivalent of this statement.

Listing Implementing the distinct() Method

  Test - Return distinct genres
  public function testoogenrelistAction(){
//Create Db object
  require_once "Db/Db_Db.php";
  $db = Db_Db::conn();
//Create the statement
//SELECT DISTINCT `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("genre" =>'a.genre');
  $tableInfo =  array("a" => 'artists');
  $statement =  $select->from($tableInfo, $columns)
  ->distinct();
  $results =  $db->query($statement);
  $rows = $results->fetchAll();
//Compare Statement
  echo $statement->__toString();
//Supress the View
  $this->_helper->viewRenderer->setNoRender();
}

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Zend Topics