Update multiple tables

[freakunleash]

Need help in updating the tables

I have array of data coming from a form which I'm inserting in my DB. I have 5 tables
product
filter
product_filter
heater
product_heater

Im able to put the data in the "product", "filter" & "heater" tables but i dont know how to put data inside the "product_filter" & "product_heater" table. Any help or direction to any tutorails is appreciated.

My Tables structure:

product
id int(5)
product text
cost text
details text

filter
id int(5)
filter text
imgpath text

product_filter
id int(5)
id_product int(5)
id_filter int(5)

heater
id int(5)
heater text
imgpath text

product_heater
id int(5)
id_product int(5)
id_heater int(5)

// Product data Update
$name = mysql_real_escape_string($_POST['product']);
$cost = mysql_real_escape_string($_POST['cost']);
$details = mysql_real_escape_string($_POST['details']);

$sql_title = "INSERT INTO product (
			id ,
			product ,
			cost ,
			details ,
			)
			VALUES (
			NULL , '$name' , '$cost' , '$details')";
if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records for product added<br />";			


// Filter update
// This is the array which is coming from the form
/*
	Array ( [0] => ehiem 
			[1] => Hagan 
			[2] => Rena 
			[3] => jobo ) 


	Array ( [0] => img1.jpg 
			[1] => img2.jpg 
			[2] => img3.jpg 
			[3] => img4.jpg )
*/

$filtername = mysql_real_escape_string($filtername);
$filterimgpath = mysql_real_escape_string($filterimg);
$combined_array = array_combine($filtername, $filterimgpath);
$values = array();
foreach ($combined_array as $filtername => $filterimgpath)
{
    $values[] = "('$filtername', '$filterimgpath')";
}
$sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product Filter Update table
	// This is where Im stuck. Not able to even think of anything....

	
// heater update
// This is the array which is coming from the form
/*
	Array ( [0] => ehiem 
			[1] => Dolphin 
			[2] => Rena 
			[3] => jobo ) 


	Array ( [0] => img1.jpg 
			[1] => img2.jpg 
			[2] => img3.jpg 
			[3] => img4.jpg )
*/

$heatername = mysql_real_escape_string($heatername);
$heaterimgpath = mysql_real_escape_string($heaterimg);
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
    $values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product heater Update table
	// This is where Im stuck. Not able to even think of anything....

[freakunleash]

Sorry pasted the wrong PHP code.

// Product data Update
$name = mysql_real_escape_string($_POST['product']);
$cost = mysql_real_escape_string($_POST['cost']);
$details = mysql_real_escape_string($_POST['details']);

$sql_title = "INSERT INTO product (
			id ,
			product ,
			cost ,
			details
			)
			VALUES (
			NULL , '$name' , '$cost' , '$details')";
if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records for product added<br />";			


// Filter update
// This is the array which is coming from the form
/*
	Array ( [0] => ehiem 
			[1] => Hagan 
			[2] => Rena 
			[3] => jobo ) 


	Array ( [0] => img1.jpg 
			[1] => img2.jpg 
			[2] => img3.jpg 
			[3] => img4.jpg )
*/

$filtername = $_POST['filtername'];
$filterimgpath = $_POST['filterimg'];
$combined_array = array_combine($filtername, $filterimgpath);
$values = array();
foreach ($combined_array as $filtername => $filterimgpath)
{
    $values[] = "('$filtername', '$filterimgpath')";
}
$sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product Filter Update table
	// This is where Im stuck. Not able to even think of anything....

	
// heater update
// This is the array which is coming from the form
/*
	Array ( [0] => ehiem 
			[1] => Dolphin 
			[2] => Rena 
			[3] => jobo ) 


	Array ( [0] => img1.jpg 
			[1] => img2.jpg 
			[2] => img3.jpg 
			[3] => img4.jpg )
*/

$heatername = $_POST['heatername'];
$heaterimgpath = $_POST['heaterimg'];
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
    $values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product heater Update table
	// This is where Im stuck. Not able to even think of anything....

[pritaeas]

Your link tables require the id's of the items you just inserted. You can find these by using mysql_last_insert_id.

[freakunleash]

hi,

I have tried to use "mysql_inster_id()" but I'm not getting the correct result. for 1st insert query I'm getting the correct result but for next query im not getting the correct answer.
I think it is because the query is inserting the array data it is capturing the first insert query & it is ignoring rest of the insert statements. I might be wrong.

[diafol]

AFAIK, mysql_insert_id() gets the last autoincrement field to be added to the table. Have you tried echoing out the query string at the end and commmenting out the query itself? Copy the query string from the screen and place it into phpmyadmin and see what happens.

[pritaeas]

All your id fields are set to auto_increment ?

[freakunleash]

Yes all fields are auto increment.

If i do a single insert query than im getting correct last insert ID, but if im getting both the input as an array than im getting the incorrect ID.

let me try to explain it again. if I try to insert the below array inside the heater table, it insert all the 4 fields in the table but I'm getting the last insert ID as 1. Maybe im echoing out the ID at wrong place.

Array ( [0] => ehiem
[1] => Dolphin
[2] => Rena
[3] => jobo )
 
 
Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/
 
$heatername = $_POST['heatername'];
$heaterimgpath = $_POST['heaterimg'];
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
$values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "records added<br />";

[pritaeas]

Oh, correct. If you want this to work, you'll have to do them one by one. I overlooked you're inserting more than a single row at a time.

[freakunleash]

Any idea how to do this.

[pritaeas]

It depends on your flow, on how your products, heaters and filters have to be connected to each other.

Basically, within your heater loop, you should also insert into your product_heater table. The same goes for filter. Instead of building an array and exploding it to insert them all at once, process your heaters and filters one by one by looping through your array.

[freakunleash]

I want to have
many-to-many releation between product & heater
many-to-many releation between product & Filter
Thats why I have created "product_heater" & "product_filter" tables but im not sure how to update them.

If you check the query again, im not both the heater & filter with same query. I'm updating them with seperate queries.

for Heater table im getting heatername & heaterimg as an array from the form
for Filter table im getting filtername & filterimg as an array from the form

[pritaeas]

insert product
get product insert_id

for each heater {
    insert heater
    get heater insert_id
    insert product_heater
}

for each filter {
    insert filter
    get filter insert_id
    insert product_filter
}