Jump to content
#StayAtHome ×
Adamhumbug

Use insert ids from an array in another query

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

  • Great Answer 1

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
2 minutes ago, Adamhumbug said:

I didnt know this was possible

Fingers crossed here - it's untested!

Share this post


Link to post
Share on other sites
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();
    
}
}

 

Share this post


Link to post
Share on other sites
2 minutes ago, Barand said:

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

Now that'll do it

 

Thanks again

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.