Request for Comments re: USDA Nutrition Database

Which of the information were you looking to integrate? And how (visually)?
There's a lot of info there, and I'm not sure how much would be really usable on a recipe site.

Yes there is a lot of info, but the main point would be to have the ability to print off nutrition facts about each recipe (ie, this recipe contains 22 grams of fat, 550 Calories, 60 mg of sodium, etc.)

Secondarily, the database has a fairly exaustive list of ingredients. It might help consistency on a large recipe database if the ingredients were validated against the list instead of just freely typed. But that may be too much of a pain to work with. That's why I'm looking for some kind of feedback. If we were going to do this, what would be the best way to go about it?

--Stephen

I would vote against forcing validation. When using eastern recipes, for instance, a lot of ingredients would be "not found." (I just tried a few) Also, when I do a simple search for salt, I get a million options where salt was included in other ...foodstuffs (for lack of a better word).
So what would happen to the nutrition information if some of the ingredients aren't found?

Maybe it should be an add-on module to the recipe module, cause it looks like it might add quite some overhead... something that people can choose to enable/disable (but maybe bundled with the recipe module).

This has come up before, and I'd like to see the feature.

Units might be a problem though - you'd need to be able to translate, say, 1 cup flour into its equivalent weight.

I would like to see this incorperated as an option as well :) My wife and I were talking about this just last week actually. Here are some sites that appear to autogenerate information based off this database:

Ogles Foods

All Recipes

Good House Keeping

Food Network

This would be a nice optional feature. Once a flexible picture solution is found (our site has a set "wide angle" picture at the top of every recipe on the site and we absolutely want to keep pictures at the top of every recipe and viewable in Views for the recipe browsing) we will be switching to the Recipe module so this would be a nice perk to make the move. Nearly no reason not to now that there is a 4.7 "patch".

Units would be the least of our problems if this was going to become a reality. There are simple mathematical conversions available for that, and I'm pretty sure those are all in the USDA database already. But as moonray pointed out, the database doesn't have every single ingredient known to mankind in it, and simple ingredients such as salt can become nedlessly complex when there are several types of salt to choose from in the database.

Does anyone know how these other web sites have overcome these problems? Perhaps there is an easier way to do this that I haven't thought of.

--Stephen

If we make ingredients nodes, we can associate lots of extra information with each one. Nutrition information may be "not found", but some admin users could then edit ingredients to add this information.

Using SR20, I've loaded the nutrient information into my own database, and thought you guys might find this useful.

My table definitions:
CREATE TABLE FOOD_DES (
`NDB_No` CHAR(5) NOT NULL,
`FdGrp_Cd` CHAR(4) NOT NULL,
`Long_Desc` CHAR(200) NOT NULL,
`Shrt_Desc` CHAR(60) NOT NULL,
`ComName` CHAR(100) NULL,
`ManufacName` CHAR(50) NULL,
`Survey` CHAR NULL,
`Ref_desc` CHAR(60) NULL,
`Refuse` INT NULL,
`SciName` CHAR(60) NULL,
`N_Factor` DOUBLE NULL,
`Pro_Factor` DOUBLE NULL,
`Fat_Factor` DOUBLE NULL,
`CHO_Factor` DOUBLE NULL
);

CREATE TABLE NUT_DATA (
`NDB_No` CHAR(5) NOT NULL,
`Nutr_No` CHAR(3) NOT NULL,
`Nutr_Val` DOUBLE NOT NULL,
`Num_Data_Pts` DOUBLE NOT NULL,
`Std_Error` DOUBLE NULL,
`Src_Cd` CHAR(2) NOT NULL,
`Deriv_Cd` CHAR(4) NULL,
`Ref_NDB_No` CHAR(5) NULL,
`Add_Nutr_Mark` CHAR NULL,
`Num_Studies` INT NULL,
`Min` DOUBLE NULL,
`Max` DOUBLE NULL,
`DF` INT NULL,
`Low_EB` DOUBLE NULL,
`Up_EB` DOUBLE NULL,
`Stat_cmt` CHAR(10) NULL,
`CC` CHAR NULL
);

CREATE TABLE FD_GROUP (
`FdGrp_Cd` CHAR(4) NOT NULL,
`FdGrp_Desc` CHAR(60) NOT NULL
);

CREATE TABLE NUTR_DEF (
`Nutr_No` CHAR(3) NOT NULL,
`Units` CHAR(7) NOT NULL,
`Tagname` CHAR(20) NULL,
`NutrDesc` CHAR(60) NOT NULL,
`Num_Dec` CHAR NOT NULL,
`SR_Order` INT NOT NULL
);

CREATE TABLE SRC_CD (
`Src_Cd` CHAR(2) NOT NULL,
`SrcCd_Desc` CHAR(60) NOT NULL
);

CREATE TABLE DERIV_CD (
`Deriv_Cd` CHAR(4) NOT NULL,
`Deriv_Desc` CHAR(120) NOT NULL
);

CREATE TABLE WEIGHT (
`NDB_No` CHAR(5) NOT NULL,
`Seq` CHAR(2) NOT NULL,
`Amount` DOUBLE NOT NULL,
`Msre_Desc` CHAR(80) NOT NULL,
`Gm_Wgt` DOUBLE NOT NULL,
`Num_Data_Pts` INT NULL,
`Std_Dev` DOUBLE NULL
);

CREATE TABLE FOOTNOTE (
`NDB_No` CHAR(5) NOT NULL,
`Footnt_No` CHAR(4) NOT NULL,
`Footnt_Typ` CHAR NOT NULL,
`Nutr_No` CHAR(3) NULL,
`Footnt_Txt` CHAR(200) NOT NULL
);

CREATE TABLE DATA_SRC (
`DataSrc_ID` CHAR(6) NOT NULL,
`Authors` CHAR(255) NULL,
`Title` CHAR(255) NOT NULL,
`Year` CHAR(4) NULL,
`Journal` CHAR(135) NULL,
`Vol_City` CHAR(10) NULL,
`Issue_State` CHAR(5) NULL,
`Start_Page` CHAR(5) NULL,
`End_Page` CHAR(5) NULL
);

CREATE TABLE DATSRCLN (
`NDB_No` CHAR(5) NOT NULL,
`Nutr_No` CHAR(3) NOT NULL,
`DataSrc_ID` CHAR(6) NOT NULL
);

CREATE TABLE FIELDINFO (`TABLENAME` VARCHAR(255), `FIELDNAME` VARCHAR(255), `DESCRIPTION` VARCHAR(255)
);

INSERT INTO `FIELDINFO` VALUES('FOOD_DES','NDB_No','5-digit Nutrient Databank number that uniquely identifies a food item');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','FdGrp_Cd','4-digit code indicating food group to which a food item belongs');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Long_Desc','200-character description of food item');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Shrt_Desc','60-character abbreviated description of food item. Generated from the 200-character description using abbreviations in appendix A. If short description was longer than 60 characters, additional abbreviations were made.');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','ComName','Other names commonly used to describe a food, including local or regional names for various foods, for example, “soda” or “pop” for “carbonated beverages”');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','ManufacName','Indicates the company that manufactured the product, when appropriate');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Survey','Indicates if the food item is used in the USDA Food and Nutrient Database for Dietary Studies (FNDDS) and has a complete nutrient profile for a specified set of nutrients');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Ref_desc','Description of inedible parts of a food item (refuse), such as seeds or bone');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Refuse','Percentage of refuse');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','SciName','Scientific name of the food item. Given for the least');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','N_Factor','Factor for converting nitrogen to protein (see p. 7)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Pro_Factor','Factor for calculating calories from protein (see p. 8)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','Fat_Factor','Factor for calculating calories from fat (see p. 8)');
INSERT INTO `FIELDINFO` VALUES('FOOD_DES','CHO_Factor','Factor for calculating calories from carbohydrate (see p. 8)');

INSERT INTO `FIELDINFO` VALUES('NUT_DATA','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Nutr_Val','Amount in 100 grams, edible portion †');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Num_Data_Pts','Number of data points (previously called Sample_Ct)');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Std_Error','Standard error of the mean. Null if could not be calculated');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Src_Cd','Code indicating type of data');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Deriv_Cd','Data Derivation Code giving specific information on how the value was determined');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Ref_NDB_No','NDB number of the item used to impute a missing value. Populated only for items added or updated starting with SR14');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Add_Nutr_Mark','Indicates a vitamin or mineral added for fortification or enrichment. This field is populated for ready-to-eat breakfast cereals and many brand name hot cereals in food group 8.');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Num_Studies','Number of studies');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Min','Minimum value');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Max','Maximum value');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','DF','Degrees of Freedom');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Low_EB','Lower 95% error bound');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Up_EB','Upper 95% error bound');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','Stat_cmt','Statistical comments. See definitions below.');
INSERT INTO `FIELDINFO` VALUES('NUT_DATA','CC','Confidence Code indicating data quality, based on evaluation of sample plan, sample handling, analytical method, analytical quality control, and number of samples analyzed. Not included in this release, but is planned for future releases.');

INSERT INTO `FIELDINFO` VALUES('FD_GROUP','FdGrp_Cd','4-digit code identifying a food group. Only the first 2 digits are currently assigned. In the future, the last 2 digits may be used. Codes may not be consecutive.');
INSERT INTO `FIELDINFO` VALUES('FD_GROUP','FdGrp_Desc','Name of food group');

INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Units','Units of measure (mg, g, µg, and so on.)');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Tagname','International Network of Food Data Systems (INFOODS) Tagnames.† A unique abbreviation for a nutrient/food component developed by INFOODS to aid in the interchange of data');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','NutrDesc','Name of nutrient/food component');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','Num_Dec','Number of decimal places that a nutrient value is rounded to');
INSERT INTO `FIELDINFO` VALUES('NUTR_DEF','SR_Order','Used to sort nutrient records in the same order as various reports produced from SR');

INSERT INTO `FIELDINFO` VALUES('SRC_CD','Src_Cd','2-digit code');
INSERT INTO `FIELDINFO` VALUES('SRC_CD','SrcCd_Desc','Description of source code that identifies the type of nutrient data');

INSERT INTO `FIELDINFO` VALUES('DERIV_CD','Deriv_Cd','Derivation Code');
INSERT INTO `FIELDINFO` VALUES('DERIV_CD','Deriv_Desc','Description of derivation code giving specific information on how the value was determined');

INSERT INTO `FIELDINFO` VALUES('WEIGHT','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Seq','Sequence number');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Amount','Unit modifier (for example, 1 in “1 cup”)');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Msre_Desc','Description (for example, cup, diced, and 1-inch pieces)');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Gm_Wgt','Gram weight');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Num_Data_Pts','Number of data points');
INSERT INTO `FIELDINFO` VALUES('WEIGHT','Std_Dev','Standard deviation');

INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_No','Sequence number. If a given footnote applies to more than one nutrient number, the same footnote number is used. As a result, this file cannot be indexed.');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_Typ','Type of footnote. D = footnote adding information to the food description; M = footnote adding information to measure description; N = footnote providing additional information on a nutrient value. If the Footnt_typ = N, the Nutr_No will also be filled in');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Nutr_No','Unique 3-digit identifier code for a nutrient to which footnote applies');
INSERT INTO `FIELDINFO` VALUES('FOOTNOTE','Footnt_Txt','Footnote text');

INSERT INTO `FIELDINFO` VALUES('DATA_SRC','DataSrc_ID','Unique number identifying the reference/source');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Authors','List of authors for a journal article or name of sponsoring organization for other documents');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Title','Title of article or name of document, such as a report from a company or trade association');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Year','Year article or document was published');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Journal','Name of the journal in which the article was published');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Vol_City','Volume number for journal articles or books; city where sponsoring organization is located');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Issue_State','Issue number for journal article; State where the sponsoring organization is located');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','Start_Page','Starting page number of article/document');
INSERT INTO `FIELDINFO` VALUES('DATA_SRC','End_Page','Ending page number of article/document');

INSERT INTO `FIELDINFO` VALUES('DATSRCLN','NDB_No','5-digit Nutrient Databank number');
INSERT INTO `FIELDINFO` VALUES('DATSRCLN','Nutr_No','Unique 3-digit identifier code for a nutrient');
INSERT INTO `FIELDINFO` VALUES('DATSRCLN','DataSrc_ID','Unique ID identifying the reference/source');

And the code to populate the database: (sorry - it's a bit messy, but I'm new to PHP!)

subscribe

I think we might need primary key for the tables

These are the primary keys that I've identified...

Some have none:
FOOTNOTE - none. The documentation states that no unique key exists.
DATSRCLN - none?

Some are obvious:
DATA_SRC.DataSrc_ID
DERIV_CD.Deriv_Cd
FD_GROUP.FdGrp_Cd
FOOD_DES.NDB_No
NUTR_DEF.Nutr_No
SRC_CD.Src_Cd

Some have composite keys:
NUT_DATA - NDB_No and Nutr_No
WEIGHT - NDB_No and Seq

As far as I can tell, there are a lot of "character" fields that should probably be integer. I don't know how problematic it'd be later if we were to convert them.

Would probably be a pain, yeah...

now the other piece of this is how to deal with updates that come in periodically from USDA?

I took a different approach to this that would make it easier to incorporate the updates. The updates themselves come in an SQL format that you can run against an existing database and it will automatically insert and remove appropriate fields. I've actually written an entire module that at this point does nothing but load/and or delete the USDA information. I'm writing it with the intention of incorporating it into the recipe module, but I wanted it to be generic enough that you could use it for a standalone database.

I took the most straightforward approach I could think of for both of these scenarios: I loaded the data directly into a MySQL database, then did an export with an --add-drop-table option. For the standalone database configuration, I left it as-is. For the integrated database portion, I prefixed each table name with the module name, "nutrition_". The admin page has three options for using the nutrition module:

Do not use USDA data
Use USDA data in Drupal table (recommended)
Use USDA data in separate database

I haven't written the separate database portion, but the intention is that the database doesn't even have to reside on the local machine. This would allow having a "vanilla" USDA nutrition database anywhere that you could use. Once your option is selected, the database is loaded line by line with the following functions:

This way, updates to the database can be done by a simple replacement of a canonical SQL file derived directly from the original USDA database, or, I could trivially adapt the load function to create an "upgrade" function to upload a "diff" SQL file.

Hi all,
I've done a fair amount of work with the USDA SR19 database and have made some progress on a module that can tie into Recipe. As has been mentioned several times here, the biggest hurdle is making sense of the data. For instance, using a simple autocomplete search on "salt" gives you 643 possible USDA SR19 entries.

Slogging through the data, I found that the "long descriptions" are fairly uniform and in fact can be relatively easily parsed as a CSV. There were about 30 exceptions (about .5% based on 7293 entries) and most of these were what I consider to be mistakes in the data (e.g. "fast foods" category broken out as "Fast foods", "Fast Foods" and "Fast food"). I wrote a parser to break the CSV descriptions down into a tree and then created a couple of tables mapping the USDA nutrition number to the appropriate path through the tree. What that gives me is the ability expand and collapse the 7293 entries all the way up into the 24 top level groupings. This works well for most groups, however several of them are still very cumbersome to work with. For instance, the USDA group "Beverages" has 50 or 60 second level groups (as opposed to, say, "Baby Foods" which has 5).

Working with what I have right now, I put together a jQuery driven module for basically just displaying the USDA data and I'd love to hear some comments on how to improve it. The page is at http://www.eatandbefit.com/nutrition. What you'll see is a table with 24 cells, each containing one of the 24 USDA basic nutrition groups. The next line will be a (possibly ellipsis shortened) list of the first drill down into that level. Items that are gray in color are branch nodes; they have other descriptors beneath them. Items in blue are actual leaf nodes representing a line in the USDA nutrition database. These would be what you click on to add an ingredient to your recipe. The autocomplete input is there but non-functional.

Things I see immediately that I think need changing or don't know what to do with:

• The "path" through the tree should be a different color. For instance under Baby Foods: Child formula, MEAD JOHNSON, PORTAGEN, with iron, prepared with powder, the first four levels should be a different color other than gray to show where in the tree you are.
• The autocomplete needs to work. At this point I'm thinking the autocomplete should always reside at the level of the current tree depth so whatever you search for, the link to get there is assumed: using the above example, at the "Child formula, MEAD JOHNSON, PORTAGEN" level, typing "with ..." into the autocomplete should give you 2 choices in the popup, "with iron, prepared from powder" and "with iron, powder, not reconstituted"
• An autocomplete at the top of each of the 24 major nutrition groups? Is this necessary?
• Presentation of realllly long sub groups. For instance, what to do with the second level of Beverages? A nice left/right scrolly thing would be cool, but practical? Worth trying to program? How should that be presented? Should there be something similar to the existing "... (more)"?
• Should blocks of data be made "re-collapsible"? That's a lot of work I think.
• Encapsulated in a form: right now, it's not a form. The whole thing needs to be presented so when you hit a child node, you can actually capture the data of that child node as input.
• Best way to use this in the Recipe module?
• Autocompletion animation for upper level blocks, to let you know when clicking on a keyword that it's actually searching.

Please post any thoughts on this you may have.

Nothing? Anyone? Beuller?

OK, this is "done" as far as I'm concerned now. I'm taking comments from people I know and integrating them as they arrive. I've made some of the changes above, other ones I'm either still working on or aren't important enough to hold up further integration.

The "path" through the tree should be a different color. For instance under Baby Foods: Child formula, MEAD JOHNSON, PORTAGEN, with iron, prepared with powder, the first four levels should be a different color other than gray to show where in the tree you are.
This is done. "Parent" nodes are green, "child" nodes; those that correspond to a line item food product in the USDA database are blue, and the path through the tree is in dark green. There is only one dark green entry per level obviously, and clicking on a blue node submits the form with that as your selection.

The autocomplete needs to work.
Not worrying about this for now. It will work as described above. The skeleton is there, just haven't written the code yet. The hard part was the "multi-part" autocomplete callback. Since you have to distinguish the top of your search path depending on where in the tree you are, you have to be able to pass the code of your parent level as an argument to the autocomplete callback. That is done.

An autocomplete at the top of each of the 24 major nutrition groups? Is this necessary?
No it's not.

Presentation of realllly long sub groups. For instance, what to do with the second level of Beverages? A nice left/right scrolly thing would be cool, but practical? Worth trying to program? How should that be presented? Should there be something similar to the existing "... (more)"?
Not worrying about this now.

Should blocks of data be made "re-collapsible"? That's a lot of work I think.
Yes they should, no it's not, and I'm not worrying about it yet.

Encapsulated in a form
Done. Uses drupal_message to print out the received values for now.

Best way to use this in the Recipe module?
Here's where I was hoping I'd get some feedback. You all have failed me.

http://www.eatandbefit.com/nutrition

I tried running the above program & 3 data files do not get uploaded. They are DATSRCLN, FOOD_DES, NUT_DATA. I understand that they are huge data files. The error I get is Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 25720267 bytes) at line: $file = file_get_contents('C:/Recipe Database/sr20/'.$filename.'.txt');
Any help would be greatly appreciated. Is this due to RAM of my machine. I have a 2 GB RAM machine.

Thanks

sub

Actually it is probably due to the PHP memory_limit setting in your php.ini file. See the section regarding "Resource Limits" the PHP core directives page: http://php.net/manual/en/ini.core.php, then try increasing your memory allocation incrementally to resolve this problem.

Thanks. It worked. I need a suggestion. I am new to using USDA database. I am devleoping my trainers website which requires a search engine gfor searching various foods users enter. The results should have info on Calories, protien a,fat and carbs and the unit. Basicaly he needs a very simple info to be shown to the user. The question is should I load all those tables in USDA or can I just use ABBREV table which has the precalculated info on th ebasic info I need.Basically when do we use ABBREV table , by using it Am I mssing any vital info. Since I am the only person looking after the website, I need to think in terms of maintanence perspectiv etoo.

Thank you

This is tricky. I'm implementing this in my Nutrition module which is pretty close to "alpha" right now. The problem is that going directly against the USDA database gives the user WAY too many choices. For instance, if you search for "salt" when what you're looking for is "salt, table", you'll actually return 643 results to your user.

The solution I'm testing is creating a USDA "filter". Basically it's a series of checkboxes with all of the major, top level USDA food groups (about 24). I then modify the autocomplete URL for the recipe ingredients textfield based on the food groups checked. The autocomplete always searches the recipe custom ingredients, but it will also optionally search the USDA database underneath the food groups you have checked. So in the example case, you would select the "Spices and Herbs" food group from the USDA DB, and start typing "salt". You'll only get one hit - "salt, table" which is exactly the one you want.

Now the question becomes what to do with the ABBREV table. In theory you could just use the ABBREV table. In fact, that's probably the best way to do it because most everything you're interested in, and everything on the standard US nutrition label is in the ABBREV table, however in order to get the same filtering capabilities I mentioned above, you'll need a number of supporting tables.

Regarding maintenance, what to do about custom ingredients? In my opinion, they should go into the ABBREV table, but I haven't gotten that far yet so I haven't quite sussed out the best way to do it. In the next couple of weeks I hope to have a clean alpha to be able to release. There are a couple of sticky issues I have yet to work out - the biggest one required a modification to core autocomplete.js which I'm really not happy about and will be looking at ways to get around.

I'll keep you posted.

Hi Gys,

I am very interested in build some kind os nutrient/recipe app. I am deciding between drupal or build using a different framework (maybe web2py, django, etc). So, I guess you guys already wrote some cool stuff. Is there anything I can do for helping out?

best,

Flavio.

Hi soupy,

I've used your Database source code and the php - thank you.
I found an error in the php, it can be fixed by replacing

$fields = $line;
with

$fields = trim($line);
otherwise a trailing CR will be in the data, preventing your replacement-by-0 method.

One more: `Vol_City` CHAR(10) NULL, should be CHAR(16).

The default database type with my mysql was InnoDB, and with innodb it took 5 minutes to load only 10000 records into the nut_data Database :-( it has 500000 records.
So I changed it to type=Myisam and added DELAYED to the INSERT statement - after that it took only seconds to run completely.

Hope this may help somebody.

Now I'm thinking about some php applications to replace my msacces queries.
If anybody cound provide some php-code for usind the database -to start with- I'd appreciate it.

cheers, Ingo

here comes my slightly altered mysql create code:

hey gajillion - how's your nutrition thingie going?

i'm developing a site now with the recipe module and it would be incredible to have a sort of "master list" of ingredients that ties into USDA info.

gajillion: how soon until you put your nutrition module here on drupal.org? I'm interested in seeing the code. Thanks!

This is pretty old, however I do think this is a great idea. There are a couple of suggestions/caveats I'd like to speak on however.

The USDA is not the only nutrition db out there. In Canada there is the Canadian Nutrient File (http://www.hc-sc.gc.ca/fn-an/nutrition/fiche-nutri-data/cnf_downloads-te...) which has a much different database structure. In order to make anything useful here, we'd have to have the ability to map information to (what I imagine as) a standard schema.

My other thought is that this would almost require a complete overhaul of the Recipe module. I think it's a great idea to extract the ingredients from the nutritional database and use that for the ingredients (as opposed to taxonomy), this would standardize input. I'm already finding with testing of my multi-user recipe site that things like 'potato' vs 'potatoe' are being inputted by users, because of this, there is a split in what should be a uniform ingredient. Even small things like "shredded carrots" vs "shredded carrot" are separated because of the current approach.

I've seen many comments already on the recipe module which point to sites like allrecipes.com etc. From what I know of these sites, you aren't inputting recipes in real time. All the recipes you submit are verified by a moderator who would take the recipes (which are inputted via a textbox) and input each ingredient one by one like the complex option of the recipe module. If the nutritional value is not auto-generated for the moderators, they could easily use a piece of software or a site like http://www.fitwatch.com/database/analyzer.php to achieve this. So for now, I think the comparison to these types of sites is a moot point.

Personally, I think for this module to be used in a multi-user environment and to be more useful and user friendly, it would require a complete re-write from the ground up. I'm prepared to put time in as I'm an experienced Drupal developer. However, I am not a UI developer nor am I a nutritionist. I would require help in both of these areas. If anyone is willing to start this with me, please contact me at kevenages [at] gmail dot com

We're looking at using some of the cck3 features and some fuzzy taxonomy, simply because its clear that this module DOES need a complete re-write...

UI / Nutrition seems to be the theme of the summer (fall now), at least on my end.

I'll be in touch.

subscribe

subscribe