Update multiple tables with ZF2 update class

Question

I'm trying to use the zend framework update class: http://framework.zend.com/manual/2.2/en/modules/zend.db.sql.html#zend-db-sql-update

to create something like the statement found here:
http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

I've tried passing an array to ->table but it fails on the string conversion.

// UPDATE `Array`
$update->table(['table1', 'table2'])

I've tried creating an escaped string but it ends up double escaped when converted to sql.

// UPDATE ``table1`,`table2``
$update->table("`table1`,`table2`")

I've tried cheating and used implode to use the glue the tables together

// UPDATE `table1``,``table2`
$update->table(implode("`,`", ['table1','table2']))

Does anyone have a clean answer to this issue?

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

After further research, I don't think it can be done. The reason I say that is because the query you've proposed may be too resource intensive. After looking for an alternate option, I've come across Update one MySQL table with values from another. However, that I'm aware of, there's no join option on an Update object. So at least at this stage, I don't believe it can be done.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 30 '14 at 14:52

Matthew Setter

2,397
1
19
18

Still waiting for the answer! – Kunal Dethe Sep 01 '14 at 15:08
I tried running my query manually and left the secondary table out and used it in the WHERE and SET statement. I got an error back from the server. – metric152 Sep 02 '14 at 18:43
I updated my sql statement to use the format in the answer you posted. Bummer to find out I can't use the zend class for the moment. Thanks for the help. – metric152 Sep 07 '14 at 17:50
Happy to do what I can. All the best. – Matthew Setter Sep 09 '14 at 09:25

score 0 · Answer 2 · answered Aug 31 '14 at 08:43

0

Are you trying to write sql like: "update tableOne, tableTwo set ...." !! I do not think it is possible in sql. As this kind of syntex not supported by mySql so do zend. here is zend table method-

public function table($table)
@param  string|TableIdentifier $table

parameter is simply string or TableIdentifier. So you have to write two sql in zend

$update->table('table1');
$update->set(array('foo' => 'bar', 'baz' => 'bax'));
.......
........
$update->table('table2');
$update->set(array('foo1' => 'bar', 'baz1' => 'bax'));

Hope it will help you.

answered Aug 31 '14 at 08:43

Atiqur

3,802
1
25
26

The linked example at the top of the question shows the same sql statement on the mysql documentation. The query does work but I'm trying to use the Update class. I'm currently writing it by hand using database->query. I tried digging around the code and saw the function you're talking about. It only accepts one table. I was hoping there was a way around this. – metric152 Sep 01 '14 at 03:57

Update multiple tables with ZF2 update class

2 Answers2