Changing table name in Zend_db_select

Question

SELECT SUM(Table.ColumnThatChanges) AS 'Count', Table.B as 'Param'
FROM Table
WHERE Table.C = 4
    AND Table.B = 'Something'
    AND Table.Date BETWEEN '2014-01-01 00:00:00' AND '2014-06-30 00:00:00'

I have this SQL Query that I generate in php. I want to call this query quite a few times with a slight change; I am changing ColumnThatChanges every request.

What would be the better/fastest way to do this? Use str_replace? Call the entire string everytime? Use some type of class that generates my SQL statement?

I'm currently generating it this way with Zend:

$somequery = $this->db->select();
$somequery->from(Table, array('SUM(Table.ColumnThatChanges) AS Count', 'Table.B as Param'))
->where('C = ?', $Variable)
->where('B = ?', $Variable2)
->where('Date > ?', $start_date)
->where('Date < ?', $end_date);

I haven't had much success changing the ColumnThatChanges part of the query, maybe I'm doing it wrong?

baldrs · Accepted Answer · 2014-06-23T16:11:44.517

2

If you can use PDO extension, there are prepared statements for this

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

In Zend Framework it can be achieved using Zend_Db_Statement

UPD If you want to change columns, as it stated here, you can use columns method or second argument to from method;

$select = $db->select()
         ->from(array('p' => 'products'), 'product_id')
         ->columns('product_name');

Then if you want to change anything, all you need is to preserve your $select object.

UPD-2 Bill Karwin has provided a better answer for a last version of the question. Please look at it.

edited Jun 23 '14 at 16:11

answered Jun 23 '14 at 15:25

baldrs

2,132
25
34

I forgot to specify I'm using Zend. The code I'm currently working on uses Zend_Db_Select. Would that be an issue? – Sefam Jun 23 '14 at 15:28
http://framework.zend.com/manual/1.12/en/zend.db.select.html Slightly different. But I'm not sure it allows me to change part of a statement once I've already stated it, I've tried without much success. I'm stuck with no choice but to recall the whole thing, which irritates me slightly. – Sefam Jun 23 '14 at 15:34
@Sefam bumped link in the answer – baldrs Jun 23 '14 at 15:36
@Sefam you wanna to change a column, not column value? Then `str_replace` is your best choice. – baldrs Jun 23 '14 at 15:37
I've updated my question, sorry for the confusion. I'll make sure I include everything next time. – Sefam Jun 23 '14 at 15:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56136/discussion-between-baldrs-and-sefam). – baldrs Jun 23 '14 at 15:46

score 1 · Answer 2 · answered Jun 23 '14 at 15:27

1

If you're using PDO, you can bind parameters. So loop through your entries, and execute with the different data. I'm not sure if this is clear or not... ... oh wait, baldrs has an example. However, you can also pass an array to execute:

$stuff = array(':name' => 'one', ':value' => 1);
$stmt->execute($stuff);

answered Jun 23 '14 at 15:27

mable

144
7

I forgot to specify I'm using Zend. The code I'm currently working on uses Zend_Db_Select. Would that be an issue? – Sefam Jun 23 '14 at 15:28
Ah, well that's similar to this question. [link](http://stackoverflow.com/questions/1604388/how-to-use-bind-variables-with-zend-db-table-update-in-the-where-clause) – mable Jun 23 '14 at 15:32
1

You can't bind a column name as a parameter. Bounds parameters can be used only where you would normally use a string or numeric constant in an SQL expression. – Bill Karwin Jun 23 '14 at 15:41

score 1 · Answer 3 · answered Jun 23 '14 at 16:06

I wrote Zend_Db_Select for Zend Framework 1.0. There is no method to replace columns in the select-list, only to append or insert columns.

It's not hard to create a custom class to add this capability, though.

class My_Db_Select extends Zend_Db_Select
{
  public function clearColumns() {
    $this->_parts[self::COLUMNS] = array();
  }
}

Test out clearing the columns and adding different columns:

$sel = new My_Db_Select($db);
$sel->from("foo", array());

$sel->columns(array("SUM(foo.red)", "id"), "foo");
echo $sel . "\n";

$sel->clearColumns();

$sel->columns(array("SUM(foo.blue)", "id"), "foo");
echo $sel . "\n";

Output:

SELECT SUM(foo.red), `foo`.`id` FROM `foo`
SELECT SUM(foo.blue), `foo`.`id` FROM `foo`

Changing table name in Zend_db_select

3 Answers3