Calculate before update query

[Rob1]

Hello,

Please be soft on me, I try to learn… ;D
But this is an obstacle I can not get over alone and google doesn’t help/
I’m trying to get my price calculated en used 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]Update multiple rows in mysql

<?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); ?>

<?php while($rows=mysql_fetch_array($result)){ ?> <?php } ?> id model price_adv price discount <? $id[]=$rows['id']; ?><? echo $rows['id']; ?>

<?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();
?>[/php]

Thanks,

Rob

[ErnieAlex]

Well, I think the code is simple. But, not sure of what you are asking. I think you want to do it in this
area of your code:

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

You are displaying the model, price_adv, price and discount. If you are asking how to replace the “price” with the calculated price, do it this way:

<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>
<?PHP $newprice=($rows['price_adv']/100) * (100-$rows['discount']); ?>
<td align="center" bgcolor="silver"><input READONLY name="price[]" type="text" id="price" value="<? echo $newprice; ?>"  size="8"></td>
<td align="center"><input name="discount[]" type="text" id="discount" value="<? echo $rows['discount']; ?>" size="8"></td>
</tr>

Something like that should work for you. I just added the calculation for you. Note, you can change the formula to this to be simpler:
Instead of: (price_adv / 100) * (100 – discount)
Use: price_adv - (price_adv * discount) which is price minus discount… Less calculations done!

Hope that helps you out…

[Rob1]

Great !!
Now it’s guitar and bear time. After a day of 16 hours of trying to get this working…
But first thing tomorrow I’m goin to try !!!
Thanks and I will let you know, super!

[ErnieAlex]

Hmmmm, CURIOUS! I get the guitar time… But the “BEAR” time??? LMAO!!!

[Rob1]

I’ve tried it but for presentation it is correct…
But: … (Sorry)
This page is to edit the prices, the “Administrator” just want to check, correct and fill in the ‘price_adv’ and the ‘discount’ fields.

In the table I have three fields:
price_adv (advice price)
price (customer price)
discount (I use a percentage like 4,6, 10 ….)

So what I need is that the form after changing a ‘price_adv’ or a ‘discount’ field(s) the update query uses these two fields to update also the ‘price’.

The price field is used in a lot of pages, it is almost impossible to correct them.

I think it must be solved in the

[php]
if ($Submit){
for($i=0;$i<$count;$i++)
{
[/php]

I also have looked to java-scripts functions but that is out of my league, I understand sometimes what they do but how to implement them into my code?

So has anybody got a clue ?? :’(

[Rob1]

: I Have the answer:

Must I share it?!?!!?
I think the internet is a big knowledgebase so here it is.

Still have to use " price_adv - (price_adv * discount) " !!

Option 1: udjust the query:
[php]$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]’ "[/php]
Option two: assign it to a new variable
[php]$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]’ "[/php]

So thanks, I’m gone find me a new PHP MySQL adventior. 8)
And maby : I’l be back …

[Rob1]

Bear time !!! Friends and guitars, basses etc… lots off fun. !!!

Yep that was yesterday evening around 21:00 hour
So after playing the guitar and drinking some bear, now I’m playing the keyboard. :-\

Good morning.

Now it is 8:15 (Amsterdam time ;-)) Gone find me some milk ?!?!? Thirsty