PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Wed Jun 26, 2019 8:06 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
PostPosted: Sun May 15, 2016 5:06 pm 
Offline
Forum Newbie

Joined: Sun May 15, 2016 4:59 pm
Posts: 5
Please help out an old FortranIV and ASM programmer.

I have a table in a mySql database that contains products. I want to retrieve a field which contains the cost of an item, multiply by a constant and then store the result into the retail price field for that product. I then want to go through the entire table for all products.

Thanks,
Klaus Cook
Houston, Texas


Top
 Profile  
 
PostPosted: Sun May 15, 2016 5:33 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
Actually that may not be a good idea. What is the constant? Are you doing this to fix data in the table because it is wrong, or to update the retail price? Is there a chance that you'll have to do this query again with a different constant? Why and how often?


Top
 Profile  
 
PostPosted: Sun May 15, 2016 6:34 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
Something like:
Syntax: [ Download ] [ Hide ]
$markup = 1.5;
$sql = "UPDATE products SET retail_price=cost*$markup WHERE ...";
$rows_updated = $pdo->exec($sql);

_________________
(#10850)


Top
 Profile  
 
PostPosted: Sun May 15, 2016 6:48 pm 
Offline
Forum Newbie

Joined: Sun May 15, 2016 4:59 pm
Posts: 5
I'm open to any suggestions. What I have is an Open shopping cart package on my website. I am trying to use it for sales of silver and gold coins. The pricing for these items is very volatile, spot prices for silver and gold change frequently. I have one field in a table which gives me the price of the item in troy ounces of silver, i.e. 1, 1.25, etc. I have the spot price that I want to plug into the routine (manually through edit). I want to be able to retrieve the price of a product in troy ounces, multiply by the constant, and store the result into the record for that product. I want to loop through the entire table for all products.

Thanks for taking the time...


Top
 Profile  
 
PostPosted: Sun May 15, 2016 7:12 pm 
Offline
Forum Newbie

Joined: Sun May 15, 2016 4:59 pm
Posts: 5
Looking at the above code example, if I work this line of code into my routine (after connecting and opening the database), it will update the entire product line in this table?

Syntax: [ Download ] [ Hide ]
$sql = "UPDATE ccCubeCart_inventory SET price=cost_price*$spot_price";


.....and I was looking how to define a loop to go through the table. Isn't progress great!


Top
 Profile  
 
PostPosted: Sun May 15, 2016 8:13 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
I'm a little unclear on why you're updating, but apparently (a) you want to use the spot price to update the price of the items and (b) the spot price changes frequently. So wouldn't that mean you have to do this update frequently as well? To me, that's a warning sign.

In your position I would store this spot price somewhere separately. Database, PHP code, the exact location isn't so important - though the database may be easier to work with. Then you use that when you query for the items. As in whatever SELECT query. You'll have the base price (cost_price) there, and in the PHP you calculate the actual current price (cost_price * $spot_price).

But it looks like you're using this CubeCart framework? I'm not sure how easy it would be to work with that. I don't like it personally but the best solution for you may be to go with the UPDATE route...


Top
 Profile  
 
PostPosted: Mon May 16, 2016 7:27 am 
Offline
Forum Newbie

Joined: Sun May 15, 2016 4:59 pm
Posts: 5
Originally I wanted to do that, but apparently there is more than one place where the values are retrieved from the data base. The first one I found was in the checkout process. Even though the shopping cart showed the correct values, they are reloaded when transferring the data to PayPal. Rather than trying to hunt all this different occasions I settled on a utility that I could run maybe once a day, or after a significant movement of the spot price. I think this will work for me. Thank you so much for the advice.

Klaus Cook
Houston, Texas


Top
 Profile  
 
PostPosted: Mon May 16, 2016 3:44 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
Yes the UPDATE statement above (without a WHERE) will update the price values for the entire table. You could limit it to specific rows with a WHERE condition. Updating the price data after a change in the spot price seems like reasonable solution.

_________________
(#10850)


Top
 Profile  
 
PostPosted: Mon May 16, 2016 4:18 pm 
Offline
Forum Newbie

Joined: Sun May 15, 2016 4:59 pm
Posts: 5
Don't want to make a pest of myself, but have one more problem:

I have all the preliminary stuff working, i.e. connect to the server and to the database. The retrieval of the spot price from the data base works, however, I can't get the "UPDATE" portion to work. It returns "0" records updated. Here is the code that I'm using:

Syntax: [ Download ] [ Hide ]
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT value FROM ccCubeCart_currency WHERE code = 'XAG' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
//        echo "Value: " . $row["value"].  "<br>";
                $spot_price = $row["value"];
    }
} else {
    echo "0 results silver spot price";
}
//////////////////// AT THIS POINT WE HAVE THE SILVER SPOT PRICE IN $spot_price //////////////////////////////////////////
// NOW UPDATE THE RETAIL PRICE OF EACH ITEM IN THE "INVENTORY" TABLE //
$sql = "UPDATE ccCubeCart_inventory SET price=cost_price*$spot_price";
$results = $conn->query($sql);
if ($results->num_rows > 0) {
    while($row = $results->fetch_assoc()) {
        echo "Value: " . $row["value"].  "<br>";
    }
} else {
    echo "0 results calculating retail";
}

$conn->close();


Top
 Profile  
 
PostPosted: Mon May 16, 2016 6:00 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
1. An UPDATE query doesn't return any results. There's nothing to fetch.
2. As such, you need to be using ->affected_rows instead of ->num_rows for it.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group