Db/Select.php

.. USING... syntax

This is functionality identical to the existing JOIN methods, however the join condition can be passed as a single column name. This method then completes the ON condition by using the same field for the FROM table and the JOIN table.

$select = $db->select()->from('table1')
                       ->joinUsing('table2', 'column1');

// SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2

These joins are called by the developer simply by adding 'Using' to the method name. E.g. * joinUsing * joinInnerUsing * joinFullUsing * joinRightUsing * joinLeftUsing

The parameter can be a single string or Zend_Db_Expr object, or else an array of strings or Zend_Db_Expr objects.

The first parameter $name can be a simple string, in which case the correlation name is generated automatically. If you want to specify the correlation name, the first parameter must be an associative array in which the key is the correlation name, and the value is the physical table name. For example, array('alias' => 'table'). The correlation name is prepended to all columns fetched for this table.

The second parameter can be a single string or Zend_Db_Expr object, or else an array of strings or Zend_Db_Expr objects.

The first parameter can be null or an empty string, in which case no correlation name is generated or prepended to the columns named in the second parameter.

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. See where() for an example

The $name and $cols parameters follow the same logic as described in the from() method.

A cross join is a cartesian product; there is no join condition.

The $name and $cols parameters follow the same logic as described in the from() method.

A full outer join is like combining a left outer join and a right outer join. All rows from both tables are included, paired with each other on the same row of the result set if they satisfy the join condition, and otherwise paired with NULLs in place of columns from the other table.

The $name and $cols parameters follow the same logic as described in the from() method.

The result set is comprised of all cases where rows from the left table match rows from the right table.

The $name and $cols parameters follow the same logic as described in the from() method.

The $name and $cols parameters follow the same logic as described in the from() method.

A natural join assumes an equi-join across any column(s) that appear with the same name in both tables. Only natural inner joins are supported by this API, even though SQL permits natural outer joins as well.

The $name and $cols parameters follow the same logic as described in the from() method.

Right outer join is the complement of left outer join. All rows from the right operand table are included, matching rows from the left operand table included, and the columns from the left operand table are filled with NULLs if no row exists matching the right table.

The $name and $cols parameters follow the same logic as described in the from() method.

Otherwise identical to orHaving().

Otherwise identical to where().

The first parameter has to be an array of Zend_Db_Select or sql query strings.

$sql1 = $db->select();
$sql2 = "SELECT ...";
$select = $db->select()
     ->union(array($sql1, $sql2))
     ->order("id");

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. Array values are quoted and comma-separated.

// simplest but non-secure
$select->where("id = $id");

// secure (ID is quoted but matched anyway)
$select->where('id = ?', $id);

// alternatively, with named binding
$select->where('id = :id');

Note that it is more correct to use named bindings in your queries for values other than strings. When you use named bindings, don't forget to pass the values when actually making a query:

$db->fetchAll($select, array('id' => 5));