Use insert ids from an array in another query

[Adamhumbug]

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

[mac_gyver]

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.

[Adamhumbug]

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

[Adamhumbug]

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();
}

 

[Barand]

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();
    }

 

[Adamhumbug]

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.

[Barand]

2 minutes ago, Adamhumbug said:

I didnt know this was possible

Fingers crossed here - it's untested!

[Adamhumbug]

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();
    
}
}

 

[Barand]

LINE 82 - "$stmt" should be "$ins_i" (I missed that one)

[Adamhumbug]

2 minutes ago, Barand said:

LINE 82 - "$stmt" should be "$ins_i" (I missed that one)

Now that'll do it

 

Thanks again

[Barand]

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.

[Adamhumbug]

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.

[Barand]

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..

[Adamhumbug]

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.