Inserts to multiple tables [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
journeyman73
Forum Newbie
Posts: 6
Joined: Thu Aug 18, 2011 8:18 am

Inserts to multiple tables [SOLVED]

Post by journeyman73 »

Hi,
I am trying to use a form where I create a new content page and give it category, (there can be multiple categories) which is held in a relationship table between pages and categories.
Everything works fine but i cannot get the page id of the new page to populate into the relationship table when the form is submitted.
how can this be done?

I have included the code for the insert as it currently is.
there is no value being returned for contentpage_ID in the second insert
Cheers
Kevin

Code: Select all

/* =====  ADD Page  ===== */
/* [ START ] Add new Page */
if(isset($_POST["action"]) && $_POST["action"] == "AddPage") {
	$addPageError = array();
	
		/* Set Default Values for form fields for insert */
		$_POST["contentpage_Name"] = isset($_POST["contentpage_Name"]) ? $_POST["contentpage_Name"] : "NULL";
		$_POST["contentpage_Content"] = isset($_POST["contentpage_Content"]) ? $_POST["contentpage_Content"] : "NULL";
		$_POST["contentpage_inMenu"] = isset($_POST["contentpage_inMenu"]) ? $_POST["contentpage_inMenu"] : "0";
		$_POST["contentpage_OnWeb"] = isset($_POST["contentpage_OnWeb"]) ? $_POST["contentpage_OnWeb"] : "0";
		$_POST["contentpage_Description"] = isset($_POST["contentpage_Description"]) ? $_POST["contentpage_Description"] : "NULL";
		$_POST["contentpage_Url"] = isset($_POST["contentpage_Url"]) ? $_POST["contentpage_Url"] : "NULL";
		$_POST["contentpage_Keywords"] = isset($_POST["contentpage_Keywords"]) ? $_POST["contentpage_Keywords"] : "NULL";
		
		$query_rsCW = sprintf("INSERT INTO tbl_contentpage (contentpage_Name, contentpage_Content, contentpage_OnWeb, contentpage_inMenu, contentpage_Description, contentpage_Url, contentpage_Keywords) VALUES ('%s','%s','%d','%d','%s','%s','%s')",$_POST["contentpage_Name"] ,$_POST["contentpage_Content"],$_POST["contentpage_OnWeb"],$_POST["contentpage_inMenu"],$_POST["contentpage_Description"],$_POST["contentpage_Url"],$_POST["contentpage_Keywords"]);
		$rsCW = $cartweaver->db->executeQuery($query_rsCW, "rsCW");

		/* Add Category */
		if(isset($_POST["Contentpage_Category_ID"])){		
			for($i=0; $i < count($_POST["Contentpage_Category_ID"]); $i++) {
		$query_updtCats = sprintf("INSERT INTO tbl_contentcat_rel (contentcat_rel_Content_ID, contentcat_rel_Cat_ID) VALUES (%d, %d)",[color=#FF0000]$_POST["contentpage_ID"][/color],$_POST["Contentpage_Category_ID"][$i]);
		$updtCats = $cartweaver->db->executeQuery($query_updtCats, "updtCats");
	}}	
		
		header("Location: PageActive.php?status=1"); 
		exit();
}/* END if(isset($_POST["AddNews"])) { */
Last edited by journeyman73 on Fri Aug 19, 2011 2:58 am, edited 1 time in total.
genix2011
Forum Commoner
Posts: 74
Joined: Tue Aug 02, 2011 4:00 pm

Re: Inserts to multiple tables

Post by genix2011 »

Hi,

you can get the ID of the last inserted row with mysql_insert_id().

and this is completly wrong:

Code: Select all

sprintf("INSERT INTO tbl_contentcat_rel (contentcat_rel_Content_ID, contentcat_rel_Cat_ID) VALUES (%d, %d)",<span style="color: #FF0000">$_POST["contentpage_ID"]</span>,$_POST["Contentpage_Category_ID"][$i]);
why is there html code inside your php code?

should be something like that:

Code: Select all

sprintf("INSERT INTO tbl_contentcat_rel (contentcat_rel_Content_ID, contentcat_rel_Cat_ID) VALUES (%d, %d)",$_POST["contentpage_ID"],$_POST["Contentpage_Category_ID"][$i]);
Greets.
journeyman73
Forum Newbie
Posts: 6
Joined: Thu Aug 18, 2011 8:18 am

Re: Inserts to multiple tables

Post by journeyman73 »

no Idea why it has, It has been ripped out of cartweaver e commerce package originally
So using mysql_insert_id() would pick it up even though it all happens at the same time?
Cheers
Kev
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Inserts to multiple tables

Post by oscardog »

It will pick up the ID of the last inserted row, so it depends where you call it and how many inserts there are.
genix2011
Forum Commoner
Posts: 74
Joined: Tue Aug 02, 2011 4:00 pm

Re: Inserts to multiple tables

Post by genix2011 »

Hi,

what do you mean at once? php is going through the code sequentially.
Do the following steps:
- Insert your data into a table
- Get Primary ID from mysql_insert_id()
- Insert your data into foreign table with foreign ID, which you got one step above

using that with your code:

Code: Select all

/* Add Category */
if(isset($_POST["Contentpage_Category_ID"])){          
    for($i=0; $i < count($_POST["Contentpage_Category_ID"]); $i++) {
        $foreign_id = mysql_insert_id(); // Step 2
        $query_updtCats = sprintf("INSERT INTO tbl_contentcat_rel (contentcat_rel_Content_ID, contentcat_rel_Cat_ID) VALUES (%d, %d)",$foreign_id,$_POST["Contentpage_Category_ID"][$i]); // Step 3
                $updtCats = $cartweaver->db->executeQuery($query_updtCats, "updtCats");
    }
} 
journeyman73
Forum Newbie
Posts: 6
Joined: Thu Aug 18, 2011 8:18 am

Re: Inserts to multiple tables [SOLVED]

Post by journeyman73 »

Works perfectly.
Thanks for the help
Kevin
Post Reply