Many to Many insert

[dee19802000]

Hey I have some code that inserts in to 3 tables,

 

1. quotes

2. quotes_det

3. quotes_link

 

The code uses jquery to insert 2 form elements, product code and quantity.  These elements are added with this code

var count = 0;
$(function(){
$('#add_field').click(function(){
	count += 1;
	$('#container').append(
			'<table id="tableid' + count +'"><tr><td align="left" valign="middle">Product Code:</td>' 
			+ '<td align="left"><input id="field_' + count + '" name="fields[]' + '" type="text" class="formInputField" size="32" maxlength="255" /></td>' 
			+ '<td align="left" valign="middle">Quantity:</td>' 
			+ '<td align="left"><input id="field_' + count + '" name="fields[]' + '" type="text" class="formInputFieldQty" size="32" maxlength="255" /></td></tr></table>' );
	$('#flashBox').highlightFade({
	speed:1000
});

});
});

 

and below is my php code that inserts into the tables

<?php
//If form was submitted
if (isset($_POST['btnSubmit'])) {

//create instance of database class
$db = new mysqldb();
$db->select_db();

//Insert static values into users table
$sql_quote = sprintf("INSERT INTO quotes (m_id, m_comment) VALUES ('%s','%s')",
					mysql_real_escape_string($_GET['m_id']),
					mysql_real_escape_string($_POST['m_comment']) );  
$result_quote = $db->query($sql_quote);

$sql_quotes_static = sprintf("INSERT INTO quotes_det (item_code, quantity) VALUES ('%s','%s')",
					mysql_real_escape_string($_POST['item_code']),
					mysql_real_escape_string($_POST['quantity']) );  
$result_quotes_static = $db->query($sql_quotes_static);

//Check if user has actually added additional fields to prevent a php error
if ($_POST['fields']) {

	//get last inserted userid
	$inserted_quote_id = $db->last_insert_id();

	//Loop through added fields
	foreach ( $_POST['fields'] as $key=>$value)  {

		//Insert into websites table
		$sql_add_f = sprintf("INSERT INTO quotes_det (item_code) VALUES ('%s')",
					    	   mysql_real_escape_string($value) );  
		$result_add_f = $db->query($sql_add_f);
		$inserted_add_f_id = $db->last_insert_id();


		//Insert into users_websites_link table
		$sql_link_quotes = sprintf("INSERT INTO quotes_link (q_id, qd_id) VALUES ('%s','%s')",
					    	   mysql_real_escape_string($inserted_quote_id),
							   mysql_real_escape_string($inserted_add_f_id) );  
		$result_link_quotes = $db->query($sql_link_quotes);

	}


} else {

	//No additional fields added by user

}
echo "<h1>Thank you!  We will be in contact soon!</h1>";

//disconnect mysql connection
$db->kill();
}
?>

 

what its doing is inserting my array into the same column.  I want it to insert into each column (item_code, quantity) .

 

Does anybody know how i can go about doing this?

 

Dee

[dee19802000]

not to worry, i managed to get it working.

<?php
//If form was submitted
if (isset($_POST['btnSubmit'])) {

//create instance of database class
$db = new mysqldb();
$db->select_db();

//Insert static values into users table
$sql_quote = sprintf("INSERT INTO quotes (m_id, m_comment) VALUES ('%s','%s')",
					mysql_real_escape_string($_GET['m_id']),
					mysql_real_escape_string($_POST['m_comment']) );  
$result_quote = $db->query($sql_quote);

$sql_quotes_static = sprintf("INSERT INTO quotes_det (item_code, quantity) VALUES ('%s','%s')",
					mysql_real_escape_string($_POST['item_code']),
					mysql_real_escape_string($_POST['quantity']) );  
$result_quotes_static = $db->query($sql_quotes_static);

//Check if user has actually added additional fields to prevent a php error
if (isset($_POST['fields'])) {


	//get last inserted userid
	$inserted_quote_id = $db->last_insert_id();

	//Loop through added fields
	for ( $i=0;$i<count($_POST['fields']);$i++) {
	$product_code = $_POST['fields'][$i];
	$product_qty = $_POST['fieldsQ'][$i];

		//Insert into quotes table
		$sql_add_f = sprintf("INSERT INTO quotes_det (item_code, quantity) VALUES ('%s','%s')",
					    	   mysql_real_escape_string($product_code),
							   mysql_real_escape_string($product_qty) );  
		$result_add_f = $db->query($sql_add_f);
		$inserted_add_f_id = $db->last_insert_id();


		//Insert into quotes link table
		$sql_link_quotes = sprintf("INSERT INTO quotes_link (q_id, qd_id) VALUES ('%s','%s')",
					    	   mysql_real_escape_string($inserted_quote_id),
							   mysql_real_escape_string($inserted_add_f_id) );  
		$result_link_quotes = $db->query($sql_link_quotes);

	}


} else {

	//No additional fields added by user

}
echo "<h1>Thank you!  We will be in contact soon!</h1>";

//disconnect mysql connection
$db->kill();
}
?>

 

 

cheers