Calculate before update query

[Rob__]

Hello,

Please be soft on me, I try to learn… :queasy:
But this is an obstacle I can not get over alone and google doesn’t help,
Just trying it for days, lookin for and editing tutorials, but... :mad:

I’m trying to get my price calculated en use it in an update query.

I use:
price_adv (advice price)
price (customer price)
discount (I use a percentage like 4,6, 10 ….)

Normally I would use something like (price_adv / 100) * (100 – discount)
WHERE and how do I implement this is this code ?!?!?

<?php

ini_set('display_errors', 1);
error_reporting(E_ALL | E_STRICT);

	// Connect DBase
				include ("../inc/connect_Shop.inc.php");


$sql="SELECT * FROM Prod WHERE manufacturer_id='4' ORDER BY id ASC";

$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);

?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>id</strong></td>
<td align="center"><strong>model</strong></td>
<td align="center"><strong>price_adv</strong></td>
<td align="center"><strong>price</strong></td>
<td align="center"><strong>discount</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){

?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="model[]" type="text" id="model" value="<? echo $rows['model']; ?>" size="25"></td>
<td align="center"><input name="price_adv[]" type="text" id="price_adv" value="<? echo $rows['price_adv']; ?>" size="8"></td>
<td align="center" bgcolor="silver"><input READONLY name="price[]" type="text" id="price" value="<? echo $rows['price']; ?>"  size="8"></td>
<td align="center"><input name="discount[]" type="text" id="discount" value="<? echo $rows['discount']; ?>" size="8"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="Submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php


// Check if button name "Submit" is active, do this
if ($Submit){
for($i=0;$i<$count;$i++) 
		{


	// $sql1="UPDATE Prod SET model= '$model[$i]', price_adv='$price_adv[$i]' ,price=($price_adv[$i] / 100) * (100 - $discount[$i]) , discount='$discount[$i]' WHERE id='$id[$i]' ";
	 $sql1="UPDATE Prod SET model= '$model[$i]', price_adv='$price_adv[$i]' , price='[$price[$i]' , discount='$discount[$i]' WHERE id='$id[$i]' ";

	$result1=mysql_query($sql1);
	}
}

if($result1){
header("location:price.php");
}
mysql_close();
?>

Thanks Rob

[m_tt]

The query you have commented out looks fine just bad syntax for price. You have to break out of the string to do the calculation e.g.

$sql1="UPDATE Prod SET model= '$model[$i]', price_adv='$price_adv[$i]' ,price=" . (($price_adv[$i] / 100) * (100 - $discount[$i])) . " , discount='$discount[$i]' WHERE id='$id[$i]' "

Or you could just assign it to a new variable

$new_price = (($price_adv[$i] / 100) * (100 - $discount[$i]));

$sql1="UPDATE Prod SET model= '$model[$i]', price_adv='$price_adv[$i]' ,price=$new_price , discount='$discount[$i]' WHERE id='$id[$i]' "

[Rob__]

Thanks M@tt,

🙂🙂🙂

That is what I was looking for.

Your option one is where I was standing, but it didn’t work because of some quotes en dots.
Your notation is correct ! It works !

Your option two is more elegant, I’m gone use that.

Cheers,

Rob