Update entire Mysql DataBase with PhP

[Erick]

Hello to everyone,

I'm new here - Great forum.

I would like to update an entire database at once.
I use Mysql database and PhP scripts.

I explain : I have to update prices in one column of the DB by puting +10%

This is what I have but it takes the first price, put +10% and update all prices with this same value, and the idea is I update all prices that are all different, so I suppose to get all different result !

I first select * from the DB where kind=$kind
then I update :

$Price_Ex_1 = $Price_Ex *10/100;
$Price_Ex_2 = $Price_Ex + $Price_Ex_1;

$q = "update DB set Price_Ex = '$Price_Ex_2'
where Kind = $kind

For sure this won't give the expected result !!
Can somebody please help me ?
In advance many thanks.
Erick.

[oberoc]

Ok, here is a couple of places that you can look. I know that php doesn't have types, but you want to make sure that you aren't truncating the value of $Price_Ex. Another place you want to look is your mysql database, make sure that you have the correct type on the mysql attribute. Finally, if that doesn't work, post a snippet of your code, and I will take a look

$Price_Ex_1 = $Price_Ex *10/100; $Price_Ex_2 = $Price_Ex + $Price_Ex_1; $q = "update DB set Price_Ex = '$Price_Ex_2' where Kind = $kind For sure this won't give the expected result !! Can somebody please help me ? In advance many thanks. Erick.

[Erick]

HI,
Thanks for your answer.
about the type for $Price_Ex, I guess it's OK (double), as the echo of my data is OK.

$q = "select * from DB_Product
where

Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q)))
die("query failed.");

$res = mysql_db_query($DB, $q);

while($row = mysql_fetch_object($res)) {

$Product_ID=$row->Product_ID;
$Kind=$row->Kind;
$Price_Ex=$row->Price_Ex;
$Price_Ex_1 = $Price_Ex *10/100;
$Price_Ex_2 = $Price_Ex + $Price_Ex_1;
}

$q = "update DB_Product set Price_Ex='$Price_Ex_2'
where Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q)))
die("query failed.");

$res = mysql_db_query($DB, $q);

if(!$dbconn){
die("Connection failed.");
}else
{
if(($res = mysql_DB_query($DB, $q))){
confirm();
}else{
inputerror();
}
}

This works if I call one Product_ID and echo it in a field name Price_Ex, but not to update all prices. When i run this script it puts all prices the same.

[tip]

Include the update sql statement in the while loop. At the moment it only updates the last $kind in the table.

[billah_norm]

$q = "select * from DB_Product where Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q)))
die("query failed.");

$res = mysql_db_query($DB, $q);

while($row = mysql_fetch_object($res)) {

$Product_ID=$row->Product_ID;
$Kind=$row->Kind;
$Price_Ex=$row->Price_Ex;
$Price_Ex_1 = $Price_Ex *10/100;
$Price_Ex_2 = $Price_Ex + $Price_Ex_1;
}

$q = "update DB_Product set Price_Ex=$Price_Ex_2
where Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q)))
die("query failed.");

$res = mysql_db_query($DB, $q);

if(!$dbconn){
die("Connection failed.");
}else
{
if(($res = mysql_DB_query($DB, $q))){
confirm();
}else{
inputerror();
}
}

///////////////////////////////////////////////////////////////////////////
as your $Price_Ex_2 is double type remove qoutes from it
if your $kind is not string type then its ok that you gave but if it is string type make sure that it is qouted

thank you

[landonmkelsey]

$q = "select * from DB_Product where Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q))) die("query failed.");

$res = mysql_db_query($DB, $q);

while($row = mysql_fetch_object($res)) {

$Product_ID=$row->Product_ID; $Kind=$row->Kind; $Price_Ex=$row->Price_Ex; $Price_Ex_1 = $Price_Ex *10/100; $Price_Ex_2 = $Price_Ex + $Price_Ex_1; }

$q = "update DB_Product set Price_Ex=$Price_Ex_2 where Kind = $Kind ";

if(!($res = mysql_DB_query($DB, $q))) die("query failed.");

$res = mysql_db_query($DB, $q);

if(!$dbconn){ die("Connection failed."); }else { if(($res = mysql_DB_query($DB, $q))){ confirm(); }else{ inputerror(); } }

/////////////////////////////////////////////////////////////////////////// as your $Price_Ex_2 is double type remove qoutes from it if your $kind is not string type then its ok that you gave but if it is string type make sure that it is qouted

thank you

I have 5 PHP programs that work as web sites allowing
adding rows, deleting row and updating single rows
window XP Prof SP3
(1) pilot's logbook access MySQL
(2) pilot's logbook access Oracle
(3) pilot's logbook access sql server 2008 express
Fedora 12
(1) pilot's logbook access MySQL
(2) pilot's logbook access Oracle

I can email

Voltaire: If God did not exist, it would be necessary for us to invent Him
GOD: If Voltaire did not exist, it would be necessary for us to invent him

[vaultdweller123]

all you need is this query...

<?php $sql = mysql_query( "UPDATE price SET prod_price = (prod_price * .10)" ); ?>

[almostbob]

all you need is this query...

should be

<?php $sql = mysql_query( "UPDATE price SET prod_price = (prod_price * 1.10)" ); ?>

+ 10% = 1.1 otherwise yeah