some rudimentary PHP / MYSQL help please. (Pull a field, modify it, put it back)

[itzpapalotl]

Hi!.

I have what I think should be a fairly simple function I need to perform on a table in a mysql database, and I guess I have no idea what I'm doing.

 

I have a table called "price_translator"

In that table are three colums:

1) serial_number  [iNT(20)] (is also the Key index)

2) vendor_price [VARCHAR(50)] (contains uploaded vendor information)

3) converted_price [DECIMAL(10,2)] (is empty and nees to be filled as follows:)

 

Now.. vendor_price contains data like $1,234.56 with the dollar and the comma.

I need to: (

a) pull the varchar data from vendor_price. (b) strip the dollar sign and the comma, © put it back in the table under my_price as a decimal value.

How would I go about this?

 

Disclaimer: If this has been answered, and you use a link to the answer, can you also include the search terms you used to find the link? That would be helpful for future searches...

 

Thanks all!

 

[aeroswat]

Hi!.

I have what I think should be a fairly simple function I need to perform on a table in a mysql database, and I guess I have no idea what I'm doing.

 

I have a table called "price_translator"

In that table are three colums:

1) serial_number  [iNT(20)] (is also the Key index)

2) vendor_price [VARCHAR(50)] (contains uploaded vendor information)

3) converted_price [DECIMAL(10,2)] (is empty and nees to be filled as follows:)

 

Now.. vendor_price contains data like $1,234.56 with the dollar and the comma.

I need to: (

a) pull the varchar data from vendor_price. (b) strip the dollar sign and the comma, © put it back in the table under my_price as a decimal value.

How would I go about this?

 

Disclaimer: If this has been answered, and you use a link to the answer, can you also include the search terms you used to find the link? That would be helpful for future searches...

 

Thanks all!

$result = mysql_query("SELECT * FROM price_translator WHERE serial_number=(id goes here)");
$row = mysql_fetch_array($result);
$cprice = str_replace(',','',str_replace('$','',$row['vendor_price']));
mysql_query("UPDATE price_translator SET converted_price='" . $cprice . "' WHERE serial_number=(id goes here)");

 

wam bam thankyou ma'am

[MatthewJ]

Or if you want to do them all at once

 

// connection details //
$sql = "SELECT vendor_price as vp, serial_number as sn FROM price_translator";
$res = mysql_query($sql, $conn);
$row = mysql_fetch_assoc($res);

do {

$toreplace = array("$", ",");
$cleaned = str_replace($toreplace, "", $row['vp']);
$ins_sql = "UPDATE price_translator SET converted_price = ".$cleaned." WHERE serial_number = ".$row['sn'];
echo $ins_sql."<br />";
mysql_query($ins_sql, $conn);

} while($row = mysql_fetch_assoc($res));

[aeroswat]

Or if you want to do them all at once

 

// connection details //
$sql = "SELECT vendor_price as vp, serial_number as sn FROM price_translator";
$res = mysql_query($sql, $conn);
$row = mysql_fetch_assoc($res);

do {

$toreplace = array("$", ",");
$cleaned = str_replace($toreplace, "", $row['vp']);
$ins_sql = "UPDATE price_translator SET converted_price = ".$cleaned." WHERE serial_number = ".$row['sn'];
echo $ins_sql."<br />";
mysql_query($ins_sql, $conn);

} while($row = mysql_fetch_assoc($res));

 

Cheater

[itzpapalotl]

Thank you both sooooo much! All I can say. 

[aeroswat]

Thank you both sooooo much! All I can say. 

 

No problemo. Glad to help! Please press the Mark Solved button on this thread so that others do not try to solve it.