Looping insert - then update

[AikenDrum]

Hi there,

 

I have two table - Products and LineItems. I insert 3 values into LineItems - the quoteID, productID and Quantity - I then need to have the Cost field and imageName field in the LineItems table populated from the Products table where the productID in the newly created row in LineItems matches the productID form the Product table.

 

Here is my current code:

 

$i = 0;
$countLI = count($postID);

while ($i < $countLI) {

$qryLi = "INSERT INTO qLi(qid, prodID, Qty) VALUES('$qid', '$postID[$i]', '$postQty[$i]')";

$resultLi = @mysql_query($qryLi);
        if( ! $resultLi ) {
            die( "Could not execute query '$qryLi'. (Reason: " . mysql_error() . "). " );
    }
  	$i++;
}

 

id someone could help me here, I would be so thankful - it's 5:20 in the morning here, been up all night and only now realized what I need.

[AikenDrum]

Actually - I used the following and it works - if anyone can see what may be wrong with it - please let me know

 

$i = 0;
$countLI = count($postID);

while ($i < $countLI) {

$qryLi = "INSERT INTO qLi(qid, prodID, Qty) VALUES('$qid', '$postID[$i]','$postQty[$i]') ";
$resultLi = @mysql_query($qryLi);
        if( ! $resultLi ) {
            die( "Could not execute query '$qryLi'. (Reason: " . mysql_error() . "). " );
    }
    
    
$qryLi2 = "Update qLi p, onlineprods pp SET p.Cost = pp.Cost WHERE p.prodID = pp.id";
$resultLi2 = @mysql_query($qryLi2);
        if( ! $resultLi ) {
            die( "Could not execute query '$qryLi'. (Reason: " . mysql_error() . "). " );
    }

echo "<br>".$qryLi;

  	$i++;
}

[AikenDrum]

Hi again,

 

I updated it and this does what I want :

 

$i = 0;
$countLI = count($postID);

while ($i < $countLI) {

$qryLi = "INSERT INTO qLi(qid, prodID, Qty) VALUES('$qid', '$postID[$i]','$postQty[$i]') ";
$resultLi = @mysql_query($qryLi);
        if( ! $resultLi ) {
            die( "Could not execute query '$qryLi'. (Reason: " . mysql_error() . "). " );
    }
    
   $liid     = mysql_insert_id();
    
//    echo $liid;
    
$qryLi2 = "Update qLi p, onlineprods pp SET p.Cost = pp.Cost, p.Product = pp.Product, p.ImgName = pp.ImgName WHERE p.prodID = pp.id AND p.id = '$liid'";

//	echo $qryLi2;

$resultLi2 = @mysql_query($qryLi2);
        if( ! $resultLi ) {
            die( "Could not execute query '$qryLi'. (Reason: " . mysql_error() . "). " );
    }

echo "<br>".$qryLi;

  	$i++;
}

 

quite chuffed actually that it works - probably not optimal but it works

 

Cheers

 

AikenD