Solved

php auto archive?

Posted on 2011-03-07
4
399 Views
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.

The three tables I have are INGREDIENTS, RECIPES, RECIPEINGREDIENTS

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?
0
Comment
Question by:jonofat
  • 2
4 Comments
 
LVL 17

Accepted Solution

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

INGREDIENTYEAR
yrid
year
ingridientid
price

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



0
 

Author Comment

by:jonofat
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.

0
 
LVL 4

Assisted Solution

by:gizmola
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:

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

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:  http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

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.
0
 

Author Closing Comment

by:jonofat
ID: 35221706
Helpful but not enough detail perhaps.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mysql left join sentence 7 22
sql sentence 2 13
MySQL left join performance 4 14
Datepicker in PHP 9 21
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now