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!

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
– Marcin Orlowski, Commented Aug 30, 2012 at 17:47
This is something wrong with your design. Why you do not use autoincrement for item_number value instead of doing this yourself? — Marcin Orlowski
– Marcin Orlowski, Commented Aug 30, 2012 at 18:33
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
– Bertrand, Commented Sep 6, 2012 at 12:54

Bertrand · Accepted Answer · 2012-09-08 12:50:08Z

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

edited Sep 8, 2012 at 12:50

answered Sep 8, 2012 at 12:36

Bertrand

13.6k5 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daya · Accepted Answer · 2012-09-12 04:38:41Z

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

edited Sep 12, 2012 at 4:38

Daya

1,17010 silver badges22 bronze badges

answered Aug 30, 2012 at 21:09

aporat

5,9425 gold badges34 silver badges54 bronze badges

1 Comment

Marcin Orlowski Over a year ago

'provided that item_id is primary key` needs a note that Zend_Db expects id to be primary id, unless you explicitely tells it so in your model by declaring protected $_primary = 'item_id'; as otherwise it will simply not work

Collectives™ on Stack Overflow

Zend Db / Mysql - Insert with Select

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related