Page 1 of 1

Inserts to multiple tables [SOLVED]

Posted: Thu Aug 18, 2011 8:31 am
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"])) { */

Re: Inserts to multiple tables

Posted: Thu Aug 18, 2011 10:30 am
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.

Re: Inserts to multiple tables

Posted: Thu Aug 18, 2011 2:43 pm
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

Re: Inserts to multiple tables

Posted: Thu Aug 18, 2011 2:55 pm
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.

Re: Inserts to multiple tables

Posted: Thu Aug 18, 2011 2:56 pm
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");
    }
} 

Re: Inserts to multiple tables [SOLVED]

Posted: Fri Aug 19, 2011 2:59 am
by journeyman73
Works perfectly.
Thanks for the help
Kevin