Loop accessing a database table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CaseyC1
Forum Newbie
Posts: 5
Joined: Sun May 15, 2016 4:59 pm

Loop accessing a database table

Post by CaseyC1 »

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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Loop accessing a database table

Post by requinix »

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?
User avatar
Christopher
Site Administrator
Posts: 13595
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Loop accessing a database table

Post by Christopher »

Something like:

Code: Select all

$markup = 1.5;
$sql = "UPDATE products SET retail_price=cost*$markup WHERE ...";
$rows_updated = $pdo->exec($sql);
(#10850)
CaseyC1
Forum Newbie
Posts: 5
Joined: Sun May 15, 2016 4:59 pm

Re: Loop accessing a database table

Post by CaseyC1 »

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...
CaseyC1
Forum Newbie
Posts: 5
Joined: Sun May 15, 2016 4:59 pm

Re: Loop accessing a database table

Post by CaseyC1 »

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?

Code: Select all

$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!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Loop accessing a database table

Post by requinix »

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...
CaseyC1
Forum Newbie
Posts: 5
Joined: Sun May 15, 2016 4:59 pm

Re: Loop accessing a database table

Post by CaseyC1 »

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
User avatar
Christopher
Site Administrator
Posts: 13595
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Loop accessing a database table

Post by Christopher »

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)
CaseyC1
Forum Newbie
Posts: 5
Joined: Sun May 15, 2016 4:59 pm

Re: Loop accessing a database table

Post by CaseyC1 »

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:

Code: Select all

// 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();
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Loop accessing a database table

Post by requinix »

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.
Post Reply