To compile a product listing, specifically a price, how can I use two tables to build a total price overall for product?

Question

I am trying to produce a shopping site where I am selling items that fluctuate in price daily (precious metals). I have a table (products) that will contain a multiplier (something like "1.1") for each product. I basically don't want to have to go into my tables and change the prices of hundreds of items every day. My idea is to create another table where I will simply change the price field with the daily value each day. How can I make the final product price total the multiplier from one table multiplied by the entered daily price from another table. Or, is there a better way than using two tables? Here's the coding so far using just one table with a defined price :

if (isset($_GET['id'])) {
    //Connect To Mysql Database
    include"storescripts/connect_to_mysql.php";
    $id = preg_replace('#[^0-9]#i','',$_GET['id']);
    //Use This VAR To Check To See If This ID Exists, If Yes Then Get Product
    //Details, If No Then Exit Script and Give Message Why
    $sql = mysql_query("SELECT * FROM products WHERE id='$id' LIMIT 1");
    $productCount = mysql_num_rows($sql);
    if ($productCount > 0) {
        //Get All The Product Details
        while ($row = mysql_fetch_array($sql)) {
            $product_name = $row["product_name"];
            $price = $row["price"];
            $details = $row["details"];
            $category = $row["category"];
            $subcategory = $row["subcategory"];
            $date_added = strftime("%b %d, %Y",strtotime($row["date_added"]));
        }

    } else {
        echo "That Item Does Not Exist";
        exit();
    }

} else {
    echo "Data To Render This Page Is Missing";
    exit();
} 

mysql_close();

Answer 1

So, how about a response that isn't the usual mysql_* related pedantry?

In the following schema I've broken the materials table away from the listed price so that they can be stored according to date. You might find this useful for records and/or invoicing.

TABLE products
  prod_id     INT PK
  prod_name   VARCHAR
  prod_price  DECIMAL
  mat_id      INT FK
  ...

TABLE materials
  mat_id    INT PK
  mat_name  VARCHAR
  ...

TABLE material_pricing
  mat_id            INT FK PK
  mat_price_date    DATE PK
  mat_price_factor  DECIMAL

SELECT
  p.prod_name,
  p.prod_price * pr.mat_price_factor AS 'cur_price'
FROM products p INNER JOIN materials m
  ON p.mat_id = m.mat_id
  INNER JOIN material_pricing pr
  ON m.mat_id = pr.mat_id
WHERE mat_price_date = TODAY()

I'm trying to think of a way to change the query to grab the last defined material_pricing entry for the relevant material, but I'm having a tough time lining up the data for a subquery...

edit: this should do the trick

SELECT
  p.prod_name,
  p.prod_price * pr.mat_price_factor AS 'cur_price'
FROM products p INNER JOIN materials m
  ON p.mat_id = m.mat_id
  INNER JOIN (
    SELECT p1.*
    FROM material_pricing p1 INNER JOIN (
      SELECT mat_id, MAX(mat_price_date) 'mat_price_date'
      FROM material_pricing
      WHERE mat_price_date <= $target_date
      GROUP BY mat_id
    ) p2
    ON p1.mat_id = p2.mat_id
      AND p1.mat_price_date = p2.mat_price_date
  ) pr
  ON p.mat_id = pr.mat_id

Where $target_date in the innermost subquery would be replaced by either today's date, the TODAY() mySQL function, or the date of the invoice being displayed.