Zend Db / Mysql - Insert with Select

Question

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!

Answer 1

Provided that item_id is the primary key of your table, and it set as auto increment field, the Zend_Db_Table::insert() function will return the primary key of the row just you inserted.

For example:

$table = new Items();

$data = array(
    'item_family' => '1',
    'item_number' => '10',
    'name'        => 'Pan'
     );

$itemId = $table->insert($data); 

You can also call directly the mysql last inserted id function:

$itemId = $this->getAdapter()->lastInsertId('items');

Answer 2

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