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

 

[joel24]

AFAIK, you'll need to use the mysqli class's multi query

[freakunleash]

Any idea how to use it.

i dont have any clue how to use it.....

[jcbones]

You need to decide how you want to tie these together.  I would assume (dangerous) that you want all 3 of those heater images tied to only one product (?).  The same going for the filters?

 

You should be getting the product id back from mysql with mysql_insert_id, then you can insert it with the rest of your queries.  I wouldn't combine the queries into a multi-query (you can, but it would take more knowledge of mysql functions).

 

Let us know, we can get this sorted quickly for you.

[freakunleash]

yes that is what i want..

[jcbones]

<?php
// 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 (
		product ,
		cost ,
		details ,
		)
		VALUES (
		'$name' , '$cost' , '$details')";
$re = mysql_query($sql_title,$con) or trigger_error(mysql_error());
if($re) {
echo "records for product added<br />";	
$productId = mysql_insert_id();
}


$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);
$values = array();
$rf = mysql_query($sql,$con) or trigger_error(mysql_error());
if($rf) {
  echo "records added<br />";
  $rf_id = mysql_insert_id();//if we inserted 3 rows, we should get the last row's id returned.
  $count = count($combined_array);
//so we build values based on the productId, and then take the last insert id in the filters table, 
//and decrement it on each loop, based on your combined array.
//since the id is auto-incremented, it should be in numerical order. Reversing it is not a problem.
  for($i = 0,$n = $rf_id; $i < $count; $i++,$n--) { 
  $values[] = '(' . $productId . ',' . $n . ')';
  }
  $sql = 'INSERT INTO product_filter(id_product,id_filter) VALUES ' . implode(',',$values);
  if(mysql_query($sql)) {
  echo "records tied together<br />";
  } else {
  trigger_error(mysql_error());
  }
} else {
trigger_error(mysql_error());
echo 'Filters failed to insert, no images tied to the product!';
}