php auto archive?

Posted on 2011-03-07
Last Modified: 2012-05-11
Hi all. Currently I have an ingredients table and a recipe table. First you would start by adding your ingredients ie: add ingredient name, price and type ie: ml, grams, etc.

Then after adding ingredients I will add a recipe which enters into the recipe table. I have a search function which allows you to search for ingredients for the recipe you want to add. So, at the moment if I search for mushrooms and I then click on mushrooms, there is a field which allows me to enter how many I need. I type in 4 and it calculates the price of 1 mushroom x the entered amount eg:4. Then I click on add ingredient and it adds that into another table.


When adding a recipe, just the name of the recipe, year of the recipe and number of portions it makes are entered into the RECIPES table. The individual ingredients, quantities and price of the ingredients are added into the RECIPEINGREDIENTS table. This all works great, the calculations are correct etc. and I am very happy with it. However, I need some suggestions. When this year is over, I need to put in new ingredient prices for 2012 which I want to automatically update the recipe prices so I don’t have to manually go and change all the recipe prices. But, this would then overwrite the current recipe prices for 2011. So, somehow I need to give the user the ability to click an “archive” button or something that will move the 2011 recipes to another table and then when they edit the ingredient prices for 2012 it will automatically adjust the recipe prices but they must still be able see how much it cost them to make the dish in 2011. Any suggestions? Maybe there is an easier way?
Question by:jonofat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 17

Accepted Solution

Shinesh Premrajan earned 250 total points
ID: 35066945
Add a new field in the INGREDIENT Table as yrid


Hence for every ingredient there will be yrid, for ex: mushrooms with the ingridientid as 9
mushroom will have yr id 12, in the table INGREDIENTYEAR, the row 12 will contain the data as

12, 2010 ,9, 20.00
18, 2011 ,9, 25.00

so you can add more years in the INGREDIENTYEAR table. only thing is there must be a unique key for
based on fields year and  ingridientid.

Hope this helps


Author Comment

ID: 35068136
This is a good idea, but I don't know if it will solve my entire problem? Would the best thing then be to duplicate all the entries in the ingredients table and only the year changes? Then do a multiple update on the price to whatever you want it to be? Is that possible? For example, my ingredients table looks like:

IngredientID |  IngredientName  |  IngredientPrice  |  IngredientType  | IngredientYear
-------------- |  ------------------- |  ------------------  |  ------------------  | -----------------
      1                     Olive Oil               0.04726                   ml                      2010
      2                     Feta                     0.05271                   g                       2010

Is it possible to have a button and when I click on it, it would run a query that will add these items again but just change the year depending on the user input? So, if they user typed in 2011 and clicked submit, it would change the table to:

IngredientID |  IngredientName  |  IngredientPrice  |  IngredientType  | IngredientYear
-------------- |  ------------------- |  ------------------  |  ------------------  | -----------------
      1                     Olive Oil               0.04726                   ml                      2010
      2                     Feta                     0.05271                   g                       2010
      1                     Olive Oil               0.04726                   ml                      2011
      2                     Feta                     0.05271                   g                       2011

Although, it probably won't work because IngredientID is a primary key set to auto increment. Damn.


Assisted Solution

gizmola earned 250 total points
ID: 35074051
Basically what you have is a normalized relational design, and what you've realized is that Ingredient price is not a core attribute of an ingredient.  It really should be normalized out, and further more, what I would suggest is that the price table contain a From/To Date range indicating how long the price is valid for.  It just begs the question of... do ingredients really have a set price for an entire year that only changes when there's a new year?  I'm dubious.

This gives you a way of updating pricing information whenever you want to, as well as going back in time and looking at price changes, if that's something of interest to you.  I'd suggest:

IngredientPriceID  - Int PK
IngredientID -FK to ingredient table.
FromDate - Date
ToDate - Date

Add a unique index on IngredientID, FromDate,ToDate

One application approach trick is to set your current active ToDate to always be a date far in the future.  I call this using a "perpetuity" date.  For a mysql Date column the largest allowable date is '9999-12-31', which makes a great perpetuity date.  When you need to enter a new ingredient price, you basically close out that date, by updateing the ToDate to be the day prior to your new date, and then add the new row.  

When you query this you will typically be doing a query related to CURDATE().  This article talks a bit about that:

When you join in your query from ingredient to ingredientPrice, you'll just include a WHERE CURDATE() BETWEEN FromDate AND ToDate and you'll always get the current price for that ingredient for the day, but you'll also be able to craft different queries that get you pricing for prior periods.

For even more flexibility, you might want to have a currency type to indicate the currency of your related Ingredient price as well.

Author Closing Comment

ID: 35221706
Helpful but not enough detail perhaps.

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a loading gif while php runs server side 15 67
Php logic to add to date card 9 39
PHP function parameters defined 14 42
Dump data from mysql to xls php 10 24
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question