1

I have a DB where I have some tables, most of them irrelevant for this question:

Content_handler:

+-------------+-------------------+---------------------------+
| app_data_id | app_menu_entry_id | content_item_container_id |
+-------------+-------------------+---------------------------+
| 0           | 0                 | NULL                      |
| 0           | 1                 | bm9zb3Ryb3MtMC0w          |
+-------------+-------------------+---------------------------+

App_menu_entry:

+-------------------+----------+---------+--------------+
| app_menu_entry_id | position | name    | icon_id      |
+-------------------+----------+---------+--------------+
| 0                 | 0        | Nombre  | asd          |
| 1                 | 1        | Precios | icontest.png |
+-------------------+----------+---------+--------------+

Content_item_container:

+---------------------------+--------------+
| content_item_container_id | content_name |
+---------------------------+--------------+
| bm9zb3Ryb3MtMC0w          | Nosotros     |
+---------------------------+--------------+

content_handler.app_menu_entry_id REFERENCES TO app_menu_entry.app_menu_entry_id

content_handler.content_item_container_id REFERENCES TO content_item_container.content_item_container_id

Now, when I want to update app_menu_entry I DELETE all records from this table (with cascade, so also deletes content from content_handler) and then I insert data with PHP from an array so I use a foreach with prepared statements like this:

$menuQuery = $connection->prepare("INSERT INTO app_menu_entry(app_menu_entry_id,position,name,icon_id) VALUES(?,?,?,?)");
$handlerQuery = $connection->prepare("INSERT INTO content_handler(app_data_id, app_menu_entry_id, content_item_container_id) VALUES(?,?,?)");
$id = 0;

if (!$menuQuery || !$handlerQuery) {
    ErrorHandler::setError(ErrorHandler::DB_QUERY_ERROR, true);
    throw new \Exception('Error al realizar la consulta');
}

foreach ($menu['menu_items'] as $menuItem) {
    $name = $menuItem['name'];
    $position = $menuItem['position'];
    $icon = $menuItem['icon_id'];
    $contentId = ($menuItem['content'] != null) ? base64_encode($menuItem['content'] . '-' . $userId . '-' . $appId) : null;

    $menuQuery->bind_param('ssss', $id, $position, $name, $icon);
    $menuQuery->execute();

    $handlerQuery->bind_param('sss', $appId, $id, $contentId);
    $handlerQuery->execute();

    $id++;
}

The problem is that this loop insert data in app_menu_entry but not in content_handler. This can be solved adding sleep(1000); between execute() function and that make me think how can I prevent using sleep() and how this execute function works? Does it uses a thread, makes a async queue...?

Machavity
  • 30,841
  • 27
  • 92
  • 100
legomolina
  • 1,043
  • 2
  • 13
  • 33
  • 2
    I guess you need transaction http://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli – Alex Jun 29 '16 at 18:15
  • 1
    ->execute() should block and not return until the execution has completed. that being said, you're simply assuming the queries are succeeding. unless you enabled exceptions, the db calls will simply return boolean false on failure, which you never check for it. if there's some kind of sync problem, you'll never know. – Marc B Jun 29 '16 at 18:45
  • The second execute sentence works fine ( I tried it outside the loop with static values to test errors but nothing found) – legomolina Jun 29 '16 at 18:48
  • Yep, $id increments inside the loop. – legomolina Jun 29 '16 at 19:17
  • At the risk of asking another dumb question, where does @appId come from? (Since it is involved in the assignment of $contentId, which is used to reference another table.) – Uueerdo Jun 29 '16 at 19:20
  • $appId = '0'; always. – legomolina Jun 29 '16 at 19:23
  • Is `app_menu_entry.app_menu_entry_id` an auto-increment field? – Uueerdo Jun 29 '16 at 19:25
  • Nop, the field is a normal varchar(255) – legomolina Jun 29 '16 at 19:26
  • just look at the source code for this if you need to know the internals – Drew Jun 29 '16 at 21:44

0 Answers0