[zend] [db] fetchAll with several variables

Question

I am trying to use fetchAll in a query with two variables. I can not understand the syntax. I can manage with only one variable:

$sql = "SELECT * FROM mytable WHERE field1 = ?";
$this->_db->fetchAll($sql,$value1);  # that works

However, I am having some problems when a query has multiple variables

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$this->_db->fetchAll($sql,$value1,$value2); # doesn't work
$this->_db->fetchAll($sql,array("field1"=>$value1,"field2"=>$value2)); # doesn't work either

Why do I want to use? instead of putting the variables directly in the query, I found out that using? allows you to compile a query using the db mechanism and improve performance.

Answer 1

There are two types of parameters, named parameters and positional parameters. You mix the two types and it will not work.

. :. , . , ( ) . :

$sql = "SELECT * FROM mytable WHERE field1 = :param1 AND field2 = :param2";
$this->_db->fetchAll($sql,array("param1"=>$value1,"param2"=>$value2));

? . () , . :

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$this->_db->fetchAll($sql,array($value1,$value2));

SQL , PDO , SQL, , . Zend_Db PDO, Zend_Db .

Answer 2

, , .

Zend_Db_Select Zend_Db. Zend_Db. Zend_Db_Select.

, Zend_Db: $this → _ db

# this will get the Zend_Db_Select object
$select = $this->_db->select();

# now you build up your query with Zend_Db_Select functions
$select->from('mytable');
$select->where('field1 = ?', $field1);
$select->where('field2 = ?', $field2);
[...]

# echo to see the SQL (helps in debugging)
# SELECT * FROM mytable WHERE field1 = ? AND field2 = ? [...]
echo '<p>My SQL: ' . $select . '</p>';

# Execute the SQL / Fetch results
$results = $select->query()->fetchAll();

, Zend Framework , JOINS, UNIONS, GROUP BY, LIMIT, HAVING ..

, , :

# SELECT p.* FROM products AS p
$select->from('p' => 'products');

, :

# SELECT model FROM products
$select->from(products, array(model));

, SQL :

SELECT 'products'.model FROM 'products'

.

, , - AND OR WHERE.

# WHERE a = $a
$select->where('a = ?', $a);

# WHERE a = $a AND b = $b
$select->where('a = ?', $a);
$select->where('b = ?', $b);

# WHERE a = $a OR b = $b
$select->where('a = ?', $a);
$select->orWhere('b = ?', $b);

# WHERE a = $a AND b = $b
$select->orWhere('a = ?', $a);
$select->where('b = ?', $b);

, "where" , , . , .

"where" "", "" . "where" "", "".

, WHERE , .

, WHERE select.

, ! !

Answer 3

:

$sql = "SELECT * FROM mytable WHERE field1 = ? AND field2 = ?";
$statement = $this->_db->query($sql,array("field1"=>$value1,"field2"=>$value2));
$data = $statement->fetchAll();

$this → _ db Db.

Answer 4

Here's what the Zend code for this code looks like.

$ sql = "SELECT * FROM mytable WHERE field1 =: param1 AND field2 =: param2"; $ This is → _ db-> fetchAll ($ SQL, array ("param1" => $ value1, "param2" => $ value2));

$where = $this->_db->select()
->from('mytable')
->where('field1 = ?',$value1)
->where('field2 = ?',$value2);

$rowSet = $this->_db->fetchAll($where);

This works great for me.