Search code examples
mysqlzend-frameworkzend-db

Zend Db / Mysql - Insert with Select

I am trying to find the best way to do this, better if I could use Zend_db_table. Basically I am inserting a row and one of the values comes from the same DB, this value changes constantly so I need to be sure the data inserted is valid. I can't query first for the value and then append it to the insert query because between the two queries the data could change and I end up inserting the wrong value. I wonder if LOCKING the table is the way to go or if Zend has a shortcut.

I'm using Mysql.

[EDITED]

For example: This table has a field called item_number, and for each new row I take the last item_number+1 (from the same item_family) and insert with it. It is a manual increment. 

 TABLE ITEMS
 | item_id |  item_family | item_number |   name    |  
 |   15    |       1      |      10     |   Pan     |
 |   16    |       2      |      1      |   Dress   |  
 |   17    |       1      |      11     |   Spoon   |

In this example you see that the next row from item_family 1 has its item_number = 11 because the previous row from the same item_family was 10.

Thanks!

Solution

  • My solution (using Zend) was to LOCK the table, than query the item_number, append the result to the insert query, insert and UNLOCK the table. Here is how to LOCK and UNLOCK:

    $sql = "LOCK TABLE items WRITE";
$this->getAdapter()->query($sql);

//run select to get last item_number
//append result to insert array
//insert

$sql = "UNLOCK TABLES";
$this->getAdapter()->query($sql);

    Another way is to write the query so the value would be selected durint the insert. Here is an example:

    $sql = INSERT INTO items (item_id, item_family, item_name, item_number) 
              VALUES (item_id, item_family, item_name, (SELECT item_number FROM... )+1);
$this->getAdapter()->query($sql);

    More info about this kind of query in MySQL Web