Pulling Data From One Table, Modifying The Data, And Inserting It Into Two Others?

[NewcastleFan]

I'm trying to figure out how to modify data I copy from one table into another two.

 

Currently I have it working so my form submits it pulls the data from 'prices' and puts it into 'pricestwo' and 'pricesthree'.

 

However I need to modify the data before hand to make one 10% less on price, and one 20% less on price.

 

<?php
$priceid = $_POST['priceid'] ;
$name = $_POST['productname'] ;
$weight = $_POST['productweight'];
$price = $_POST['productprice'];
if(isset($_POST['updateprices'])) {

for($i=0;$i<$count;$i++){

$sql1= mysqli_query($myConnection, "UPDATE pricestwo SET productname='$name[$i]', productweight='$weight[$i]', productprice='$pricetwo[$i]' WHERE priceid='$priceid[$i]'");

$sql2= mysqli_query($myConnection, "UPDATE pricesthree SET productname='$name[$i]', productweight='$weight[$i]', productprice='$price[$i]' WHERE priceid='$priceid[$i]'");
}
echo "<meta http-equiv=\"refresh\" content=\"0;URL=edit_product_prices.php\">";
}
?>

 

This currently works to copy just the data, I've tried something like:

 

$pricetwo = $price - ($price * 0.15);

 

to modify the data, but there are multiple rows been transferred so this just returns a 0.00 value.

 

Anyone have any ideas on how to do this?

[winningdave]

Are you trying to edit one item at at time? If so you dont need the for loop and Im not sure if you already have the data in the tables to do the update. If not you need to do an insert. Dont forget to escape you POST variables.

 

Not sure if this is how you have your form setup but here's my two cents

 

 

<?php

$priceid = $_POST['priceid'] ;

$name = $_POST['productname'] ;

$weight = $_POST['productweight'];

$price = $_POST['productprice'];

if(isset($_POST['updateprices'])) {

//Get priceone

$priceone = mysqi_query("SELECT productprice FROM prices WHERE priceid='".mysql_real_escape_string($priceid)."'");

 

//If we get a row back then update the other two tables

if(mysql_num_rows($priceone)>0){

 

//Price one

$priceone = mysql_result($priceone,0,'productprice');

$pricetwo = $priceone * 0.9;

$pricethree = $priceone * 0.8;

 

 

//Im not sure if your really trying to update the tables here or if you need to insert - does the data already exist?

$sql1= mysqli_query($myConnection, "UPDATE pricestwo SET productname='".$name."', productweight='".$weight."', productprice='".$pricetwo."' WHERE priceid='".$priceid."'");

 

$sql2= mysqli_query($myConnection, "UPDATE pricesthree SET productname='".$name."', productweight='".$weight."', productprice='".$pricetwo."' WHERE priceid='".$priceid."'");

//Redirects page using PHP

header("Location: edit_product_prices.php");

}

}

?>

[NewcastleFan]

Hey thanks for your reply, sorry I wasn't more clear, the loop is needed as there are multiple rows in the database.

 

I have 3 tabs on the page:

Price One, Price Two, Price Three.

 

I want to make it so you can edit all of the prices on Price one and save it (which is done), I am then making a form button that takes the data from priceone, and removes 10% for pricetwo and 20% for pricethree which is where I am struggling as there are multiple rows to be pulled from the loop to be modified then updated. (Needs to be repeatable to has to be updated)

[winningdave]

The above code will get the priceone, take 10% off to give you price two and then take 20% off price one to give you price three and will update the database. You dont need the loop as you are only updating two rows and already have separate queries. You would only need the loop if you wanted to update say 10 items as once and pricetwo and pricethree of each of these.

 

EDIT:$sql2 should be -

 

$sql2= mysqli_query($myConnection, "UPDATE pricesthree SET productname='".$name."', productweight='".$weight."', productprice='".$pricethree."' WHERE priceid='".$priceid."'");

Edited November 7, 2012 by winningdave

[NewcastleFan]

Thanks for your reply, I'm not sure how this would work as the data is from a loop so $price changes for every row?

 

The data I'm passing to the above code looks like this:

 

 

<form id="form" name="form" method="post" action="">

<? $sqlCommand = "SELECT * FROM prices ORDER BY priceid";

$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());

// Count table rows

$count=mysqli_num_rows($query);

 

while ($row = mysqli_fetch_array($query)) {

 

echo '<input name="priceid[]" type="hidden" id="priceid" value="' . $row['priceid'] . '">';

echo '<input name="productname[]" type="hidden" id="productname" size="20" maxlength="45" value="' . $row['productname'] . '">';

echo '<input name="productweight[]" type="hidden" id="productweight" size="5" maxlength="45" value="' . $row['productweight'] . '">';

echo '<input name="productprice[]" type="hidden" id="productprice" size="5" maxlength="45" value="' . $row['productprice'] . '">';

}

?>

<input type="submit" name="updateprices" id="submit" value="Update All Prices" />

</form>

[winningdave]

Ahh ok so you are trying to update multiple items at once. If your loop is working then all you should have to do $pricetwo = $price[$i] * 0.9; and $pricethree = $price[$i] * 0.8;

[NewcastleFan]

I've tried that method Dave, However the [$i] variable is not created until the for statement, so if I was to put the $pricetwo = $price[$i] * 0.9; in the for statement there would be only 1 price for $pricetwo if I'm correct?

[Barand]

Forget the looping. All you need is a couple of update queries

 

UPDATE pricetwo p2
INNER JOIN priceone p1 USING (priceid)
SET
   p2.price = p1.price * 0.9,
   p2.productname = '$name',
   p2.producweight = $weight
WHERE p1.priceid = $id

 

... and similar for pricethree table.

 

Of course, you need to sanitize those POST variable first.

[NewcastleFan]

Forget the looping. All you need is a couple of update queries

 

UPDATE pricetwo p2
INNER JOIN priceone p1 USING (priceid)
SET
p2.price = p1.price * 0.9,
p2.productname = '$name',
p2.producweight = $weight
WHERE p1.priceid = $id

 

... and similar for pricethree table.

 

Of course, you need to sanitize those POST variable first.

 

Thanks for your reply, I've never used inner join, not 100% sure how it works but I've done the following as per your example however it returns a server error. How are the variables p1.priceid defined?

 

<?php
// Variables defined for update of multiple tables
$priceid = $_POST['priceid'] ;
$name = $_POST['productname'] ;
$weight = $_POST['productweight'];
$price = $_POST['productprice'];

if(isset($_POST['updateprices'])) {

for($i=0;$i<$count;$i++){

$sql1= mysqli_query($myConnection, "UPDATE pricetwo p2
INNER JOIN priceone p1 USING (priceid)
SET
p2.price = p1.price * 0.9,
p2.productname = '$name',
p2.producweight = $weight
WHERE p1.priceid = $priceid";


$sql2= mysqli_query($myConnection, "UPDATE pricesthree SET productname='$name[$i]', productweight='$weight[$i]', productprice='$price[$i]' WHERE priceid='$priceid[$i]'");
}
echo "<meta http-equiv=\"refresh\" content=\"0;URL=edit_product_prices.php#tabone-tab\">";
}
?>

 

Not sure if I'm completely off the mark here, feeling a tad out of my knowledge depth at the moment!

[Barand]

I admit to some confusion. I thought you had several pricetwo records for each priceone record that you wanted updating, and that was the reason for your loops.

 

Having looked at your form it looks to me as if there are several sets of id/name/weight/price being input.

 

So are table priceone, pricetwo and pricethree identical except for the price discounts?

[NewcastleFan]

Yes they will be identical, but editable at the same time (Ability to remove product's and add new ones).

 

So user puts data into price table.

 

Presses button to update pricetwo to the same name,weight, and price with 10% off, and pricethree to the same name, weight, and price with 10% off.

 

If that makes sense? Sorry if I'm not explaining it well.

[Psycho]

Yes they will be identical, but editable at the same time (Ability to remove product's and add new ones).

 

So user puts data into price table.

 

Presses button to update pricetwo to the same name,weight, and price with 10% off, and pricethree to the same name, weight, and price with 10% off.

 

Sorry, if this is a stupid question, but I've read through your posts here and I'm wondering why you need separate tables. In fact, do you need to even store price2 and price3 at all?

 

First, why are you needing to store these in separate tables? Why not store price2 and price3 in the same table in different fields? Then you don't have to duplicate the data across the tables. There might be a valid reason for doing this but, if not, you will save yourself a ton of problems by putting them in the same table.

 

Second, if price2 and prioce3 will always be the same percentage off from price1 then you don't need to store price2 and price3 at all. Just hard-code or have a configuration on what the offset for price2 and price3 should be. Then dynamically change the price based upon your need. Example:

//Config vars for the offsets
$price1 = 1;
$price2 = .9;
$price3 = .8;

//Then in your code have some logic to determine the offset
switch($user_type)
{
   case 'Big Buyer':
    $price_offset = $price3;
    break;
   case 'Frequent Buyer':
    $price_offset = $price2;
    break;
   case 'Normal Buyer':
   Default:
    $price_offset = $price1;
    break;
}

//Then use that offset in any queries to get price
$query = "SELECT name, (price  * $price_offset) as price
	  FROM products";

[NewcastleFan]

Sorry, if this is a stupid question, but I've read through your posts here and I'm wondering why you need separate tables. In fact, do you need to even store price2 and price3 at all?

 

First, why are you needing to store these in separate tables? Why not store price2 and price3 in the same table in different fields? Then you don't have to duplicate the data across the tables. There might be a valid reason for doing this but, if not, you will save yourself a ton of problems by putting them in the same table.

 

Second, if price2 and prioce3 will always be the same percentage off from price1 then you don't need to store price2 and price3 at all. Just hard-code or have a configuration on what the offset for price2 and price3 should be. Then dynamically change the price based upon your need. Example:

//Config vars for the offsets
$price1 = 1;
$price2 = .9;
$price3 = .8;

//Then in your code have some logic to determine the offset
switch($user_type)
{
case 'Big Buyer':
 $price_offset = $price3;
 break;
case 'Frequent Buyer':
 $price_offset = $price2;
 break;
case 'Normal Buyer':
Default:
 $price_offset = $price1;
 break;
}

//Then use that offset in any queries to get price
$query = "SELECT name, (price * $price_offset) as price
	 FROM products";

 

Thanks for your reply, Unfortunately I need them to be stored separate because once the data is passed to the other tables with the % off I need the ability to manually edit the price for special offers etc for certain price bands.

[Barand]

Thanks for your reply, Unfortunately I need them to be stored separate because once the data is passed to the other tables with the % off I need the ability to manually edit the price for special offers etc for certain price bands.

 

And it would be impossible to edit the prices if they were all in one table

[NewcastleFan]

And it would be impossible to edit the prices if they were all in one table

 

As far as I'm aware? I may not be explaining it correctly lets give an example:

 

Product 1 is set to £10.00, I update this across all tables so the data looks like:

 

Tableone

Product 1, £10.00

Product 2, £20.00

 

Tabletwo

Product 1, £9.00

Product 2, £18.00

 

Tablethree

Product 1, £8.50

Product 2, £17.00

 

Now that's what I'm trying to make happen on the update of all the tables.

 

But I currently want to run an offer for users of the tablethree group, I want to reduce product 1 to £6.50 so I go manually update the price in tablethree.

 

Does that make any sense? ha.

 

I've played around with it a bit more now and got to this point:

 

<?php
if(isset($_POST['updateprices'])) {

$sql1= mysqli_query($myConnection, "REPLACE INTO pricestwo (priceid,productname,productweight,productprice) SELECT priceid,productname,productweight,productprice FROM prices");

$sql2= mysqli_query($myConnection, "REPLACE INTO pricesthree (priceid,productname,productweight,productprice) SELECT priceid,productname,productweight,'$productpricetwo' FROM prices");

echo "<meta http-equiv=\"refresh\" content=\"0;URL=edit_product_prices.php#tabone-tab\">";
}
?>

 

 

 

However I still cannot figure out how to reduce the productprice column by a % on update

[NewcastleFan]

I figured it out!

 

Not sure how efficient it is but it works ha If anyone else is struggling with this here is what I used:

 

 

<?php
if(isset($_POST['updateprices'])) { 

$sql1= mysqli_query($myConnection, "REPLACE INTO pricestwo (priceid,productname,productweight,productprice) SELECT priceid,productname,productweight,productprice *0.9 FROM prices");

$sql2= mysqli_query($myConnection, "REPLACE INTO pricesthree (priceid,productname,productweight,productprice) SELECT priceid,productname,productweight,productprice *0.85 FROM prices");

echo "<meta http-equiv=\"refresh\" content=\"0;URL=edit_product_prices.php#tabone-tab\">";
} 
?>

[Barand]

And now your change to 6.50 for product 1 has to be re-done, plus all the other changes you've made

[NewcastleFan]

The prices change weekly for everything anyway so would only be pressing update when they all needed updating anyway, then the special prices would also change weekly after updating.

[Muddy_Funster]

I really hope all this is a "just for fun" project...

[Barand]

Sorry, but I still cannot see the sense in triplicating all the data when only the prices are different.

[NewcastleFan]

I really hope all this is a "just for fun" project...

 

It's for fun yes, I'm using this to teach my self more php.

 

Sorry, but I still cannot see the sense in triplicating all the data when only the prices are different.

 

How would you suggest doing it so I could have 3 price bands and the ability to override the percentage off then? I'm no php expert so I'm trying to learn.

[Barand]

 

First, why are you needing to store these in separate tables? Why not store price2 and price3 in the same table in different fields? Then you don't have to duplicate the data across the tables. There might be a valid reason for doing this but, if not, you will save yourself a ton of problems by putting them in the same table.

 

 

[Psycho]

Sorry, but I still cannot see the sense in triplicating all the data when only the prices are different.

 

Exactly.

 

@NewCastleFan,

 

Why not have multiple fields in one table for the three different prices? You are making this way more difficult than it should be. You can still update all three prices at once or individually.

 

But I currently want to run an offer for users of the tablethree group, I want to reduce product 1 to £6.50 so I go manually update the price in tablethree.

 

And if price3 was simply another field in the main table you can still do the same thing.

 

Let's say the table structure is something like this:

 

Products:

prid_id, prod_name, prod_description, price1, price2, price3

 

You can have a process to set price1 AND automatically set price2 and price3 using the percentage logic using

UPDATE products SET price1 = $price, price2 = $price*.9, price3 = $price*.8
WHERE prod_id = $prodID

 

Then you can also update individual prices just as easily

UPDATE products SET price3 = $special
WHERE prod_id = $prodID