Adamhumbug Posted January 15, 2020 Share Posted January 15, 2020 Hi, I am creating a new menu (food) in my system. This consists of a menu, menu_items and menu_connection table. I can insert the menu name just fine and return its id just fine. When inserting the menu items, i need to get each of the menu_item_ids to use in the query that inputs the menu_connection. This is what i have so far: if ($_SERVER['REQUEST_METHOD']=="POST") { ///////////////////// //menu name insert // ///////////////////// $mname = mysqli_real_escape_string($conn, $_POST['newMenuName']); $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu (menu_name) VALUES (?); '); $stmt->bind_param('s',$mname); $stmt->execute(); $menuInsId = $stmt->insert_id; echo $menuInsId; $stmt->close(); ///////////////////// //menu item insert // ///////////////////// $mitname = $_POST['newMenuItem']; $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu_items (menu_item_name) VALUES (?); '); foreach ($_POST['newMenuItem'] as $k => $nmItem) { $mitname = mysqli_real_escape_string($conn, $nmItem); $stmt->bind_param('s',$mitname); $stmt->execute(); $menuItmInsId = $stmt->insert_id; echo $menuItmInsId; } $stmt->close(); /////////////////////////// //menu connection insert // /////////////////////////// $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); foreach ($_POST['newMenuItem'] as $k => $nmItem) { $stmt->bind_param('ii',$menuInsId, $menuItmInsId); $stmt->execute(); $connectionInserId = $stmt->insert_id; echo $connectionInserId; } $stmt->close(); } Currently it is inserting each of the items in the connection table with the same id - i understand why but i dont know how to collect up all of the ids to use later Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted January 15, 2020 Share Posted January 15, 2020 since the ssm_menu_items last insert id is generated inside the 1st foreach loop, you would ether need to insert the row in the ssm_menu_connection table there, OR store the ids in an array and then loop over that array of ids to execute the 3rd query. however, the 'INSERT IGNORE INTO ssm_menu_items...' query won't give you a last insert id if the menu item is already in the ssm_menu_items table. you would need to use an INSERT ... ON DUPLICATE KEY UPDATE query, where the sole purpose of the UPDATE part of the query is to use the MySql LAST_INSERT_ID(x) function to 'get' the id for existing ssm_menu_items. lastly, why are you using mysqli_real_escape_string() calls with prepared queries. The main point of a prepared query is it separates the data from the sql syntax so that nothing in the data will be operated on as sql syntax. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 15, 2020 Author Share Posted January 15, 2020 4 minutes ago, mac_gyver said: since the ssm_menu_items last insert id is generated inside the 1st foreach loop, you would ether need to insert the row in the ssm_menu_connection table there, OR store the ids in an array and then loop over that array of ids to execute the 3rd query. however, the 'INSERT IGNORE INTO ssm_menu_items...' query won't give you a last insert id if the menu item is already in the ssm_menu_items table. you would need to use an INSERT ... ON DUPLICATE KEY UPDATE query, where the sole purpose of the UPDATE part of the query is to use the MySql LAST_INSERT_ID(x) function to 'get' the id for existing ssm_menu_items. lastly, why are you using mysqli_real_escape_string() calls with prepared queries. The main point of a prepared query is it separates the data from the sql syntax so that nothing in the data will be operated on as sql syntax. I am glad that is the case with the real_escape_string - thanks for pointing that out to me. I have created the array but i now need to loop through it in the final query Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 15, 2020 Author Share Posted January 15, 2020 I think this just about does it. if ($_SERVER['REQUEST_METHOD']=="POST") { ///////////////////// //menu name insert // ///////////////////// $mname = $_POST['newMenuName']; $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu (menu_name) VALUES (?); '); $stmt->bind_param('s',$mname); $stmt->execute(); $menuInsId = $stmt->insert_id; echo $menuInsId; $stmt->close(); ///////////////////// //menu item insert // ///////////////////// $menuItemInsertIds = array(); $mitname = $_POST['newMenuItem']; $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu_items (menu_item_name) VALUES (?); '); foreach ($_POST['newMenuItem'] as $k => $nmItem) { $mitname = $nmItem; $stmt->bind_param('s',$mitname); $stmt->execute(); $menuItmInsId = $stmt->insert_id; array_push($menuItemInsertIds, $menuItmInsId); } $stmt->close(); /////////////////////////// //menu connection insert // /////////////////////////// $stmt=$conn->prepare(' INSERT IGNORE INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); foreach ($menuItemInsertIds as $k => $miiids) { $stmt->bind_param('ii',$menuInsId, $miiids); $stmt->execute(); $connectionInserId = $stmt->insert_id; echo $connectionInserId; } $stmt->close(); } Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2020 Share Posted January 15, 2020 IMHO it's easier just to do both inserts in the same loop as you always want one of each. ////////////////////////////////////// // menu item and connection insert // ////////////////////////////////////// $ins_i = $conn->prepare(' INSERT INTO ssm_menu_items (menu_item_name) VALUES (?); '); $ins_c = $conn->prepare(' INSERT INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); $ins_i->bind_param('s',$nmItem); $ins_c->bind_param('ii', $menuInsId, $menuItmInsId); foreach ($_POST['newMenuItem'] as $nmItem) { $ins_i->execute(); $menuItmInsId = $stmt->insert_id; $ins_c->execute(); } 1 Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 15, 2020 Author Share Posted January 15, 2020 3 minutes ago, Barand said: IMHO it's easier just to do both inserts in the same loop as you always want one of each. ////////////////////////////////////// // menu item and connection insert // ////////////////////////////////////// $ins_i = $conn->prepare(' INSERT INTO ssm_menu_items (menu_item_name) VALUES (?); '); $ins_c = $conn->prepare(' INSERT INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); $ins_i->bind_param('s',$nmItem); $ins_c->bind_param('ii', $menuInsId, $menuItmInsId); foreach ($_POST['newMenuItem'] as $nmItem) { $ins_i->execute(); $menuItmInsId = $stmt->insert_id; $ins_c->execute(); } I like this a lot. I didnt know this was possible -> every day is a school day. Thanks again. Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2020 Share Posted January 15, 2020 2 minutes ago, Adamhumbug said: I didnt know this was possible Fingers crossed here - it's untested! Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 15, 2020 Author Share Posted January 15, 2020 3 minutes ago, Barand said: Fingers crossed here - it's untested! haha just for fun: Warning: main(): Couldn't fetch mysqli_stmt in /homepages/29/d742272110/htdocs/actions/submit-new-menu-action.php on line 82 Fatal error: Uncaught mysqli_sql_exception: Column 'menu_item_id' cannot be null in /homepages/29/d742272110/htdocs/actions/submit-new-menu-action.php:83 Stack trace: #0 /homepages/29/d742272110/htdocs/actions/submit-new-menu-action.php(83): mysqli_stmt->execute() #1 {main} thrown in /homepages/29/d742272110/htdocs/actions/submit-new-menu-action.php on line 83 //L69 $ins_i = $conn->prepare(' INSERT INTO ssm_menu_items (menu_item_name) VALUES (?); '); $ins_c = $conn->prepare(' INSERT INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); $ins_i->bind_param('s',$nmItem); $ins_c->bind_param('ii', $menuInsId, $menuItmInsId); foreach ($_POST['newMenuItem'] as $nmItem) { $ins_i->execute(); //L82 $menuItmInsId = $stmt->insert_id; //L83 $ins_c->execute(); } } Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2020 Share Posted January 15, 2020 LINE 82 - "$stmt" should be "$ins_i" (I missed that one) Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 15, 2020 Author Share Posted January 15, 2020 2 minutes ago, Barand said: LINE 82 - "$stmt" should be "$ins_i" (I missed that one) Now that'll do it Thanks again Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2020 Share Posted January 15, 2020 I'm guessing the above code is to be used to process the form in your other post where you are adding new (cloned) items. You data model ... +---------------+ +---------------+ | Menu | | Item | +---------------+ +---------------+ | | | +-----------------+ | +---------<| menu_connection |>------+ +-----------------+ ... implies an item can appear on many menus, yet your form does not allow for the allocation of aleady existing items to a menu, just newly created items. Just curious. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 16, 2020 Author Share Posted January 16, 2020 21 hours ago, Barand said: I'm guessing the above code is to be used to process the form in your other post where you are adding new (cloned) items. You data model ... +---------------+ +---------------+ | Menu | | Item | +---------------+ +---------------+ | | | +-----------------+ | +---------<| menu_connection |>------+ +-----------------+ ... implies an item can appear on many menus, yet your form does not allow for the allocation of aleady existing items to a menu, just newly created items. Just curious. You are of course correct. Currently it does not allow for this but the next step will be to allow the connection of existing items to a new menu. Quote Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2020 Share Posted January 16, 2020 I would have had a page to maintain menu_items table (add/edit/delete) and another page to maintain menus, in which you add/delete menus and select/delete the items you want in them Just my 0.02 worth.. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted January 20, 2020 Author Share Posted January 20, 2020 On 1/16/2020 at 3:22 PM, Barand said: I would have had a page to maintain menu_items table (add/edit/delete) and another page to maintain menus, in which you add/delete menus and select/delete the items you want in them Just my 0.02 worth.. Makes very good sense, i will look into this. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.