Zend Db / Mysql - Insert with Select

1
votes

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!

2
mysqlzend-frameworkzend-db
Can you explain a bit more? This sounds a bit odd - you got values that change constantly, but you cannot read them because it then change again? So what for does it change in the first place if you cannot use it in sane way? - Marcin Orlowski
This is something wrong with your design. Why you do not use autoincrement for item_number value instead of doing this yourself? - Marcin Orlowski
This is how the DB was first designed and I am trying to use it as it is. The item_number increments based on item_family, this is why I cant autoincrement it. - Bertrand

2 Answers

2
votes

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

1
votes

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');