I'm pretty much a newbie when it comes to SQL and advanced web page design. What I want to do is create a cookbook program through a web interface. Right now, all I am interested in is showing one individual recipe, taking data from about 6 different tables, and displaying them all on one page.

I would like advice as to which scripting language to use. I really don't know much about them, other than CGI is server-side and most of the other ways of doing it are client-side. I need to learn jython for an upcoming project, so if it is possible to use it, it would be very helpful to me.

I've found a lot of good SQL tutorials, but I haven't seen any good tutorials for integrating SQL with a web page. If anybody has suggestions, I would be appreciative. I have rudimentary programming skills, but I am a fast learner.

-Biscuits

PHP is probably the most obvious and your best bet.

I'll second that recommendation.

Your options are really C, perl or php.

C requires compiling for every change. It's (obviously) very powerful, but that means you can easily create some serious security issues.

perl is ideal for manipulating anything textual, and easily powerful enough to do the job. But it also suffers from the security issue as C. It's not necessarily easy to learn either - there's so many different ways to write one line of perl that do the same thing, that it's easy to write something that doesn't do quite what you wanted. perl won't complain...

But both of these languages were originally devised for other purposes.

PHP on the other hand was devised explicitly for the purpose of creating dynamic HTML, and as a language has been optimised to do this one task very well. It's syntax is fairly clean and easy to learn, and it's harder to create bone-headed security issues than with C or perl.

Oh, and take a peek at www.hotscripts.com, which has a ton of information, including 87 database related tutorials on PHP.

If someone can help me figure out how to do this SQL query, I would be most appreciative. I have three relevant tables, ingredient_all (IngredientID, IngredientName, IngredientType), ingredient_similar (IngSimID, IngredientID), and recipe_ingredient (RecIngID, RecipeID, IngredientID, Amount, Unit)

Now, I want to find every recipe ingredient that has a similar ingredient and display it on a table, like so:

Recipe Ingredient.......Similar Ingredient
----------------------------------------------------------
.........Butter..........|.........Margerine
Granulated Sugar...|...Powdered Sugar
Granulated Sugar...|......Brown Sugar
.........Vanilla.........|.....Vanilla Extract
....Butterscotch.....|......Butter Extract

If I craft the following select statement:

SELECT ingredient_similar.ingsimid, ingredient_all.ingredientname
FROM ingredient_all, ingredient_similar, recipe_ingredient
WHERE ingredient_all.ingredientid=recipe_ingredient.ingredientid AND ingredient_all.ingredientid=ingredient_similar.ingredientid

then it returns the following data:

...IngredientName........IngSimID (ID for set of similar ingredients)
----------------------------------------------------------
.........Butter..........|..........1
Granulated Sugar..|..........2
........Vanilla..........|..........3
....Butterscotch.....|..........4

This search gives the following results:

SELECT ingredient_all.ingredientname, ingredient_similar.ingsimid
FROM ingredient_all, ingredient_similar
WHERE ingredient_all.ingredientid=ingredient_similar.ingredientid

...IngredientName........IngSimID (ID for set of similar ingredients)
----------------------------------------------------------
.........Butter..........|..........1
......Margerine.......|..........1
Granulated Sugar..|..........2
.Powdered Sugar...|..........2
........Vanilla..........|..........3
...Vanilla Extract...|..........3
....Butterscotch.....|..........4
....Butter Extract...|..........4
.........Cream.........|..........5
....Half and Half.....|..........5
....Brown Sugar.....|..........3

I really don't know how to craft a query to give the data I want... Any advice would be appreciated.

-Biscuits

Haven't tested this, it's SQL from the database in my head :-) I think the thing you're missing is referencing to ingredient_all table twice, first to do the initial lookup against ingredient_similar, and second to reference ingredient_similar's result back to a name.

Give it a go and let me know what happens.

For all the ingredients:
SELECT i.IngredientName, r.IngredientName FROM ingredient_all AS i, ingredient_all AS a, ingredient_similar AS s
WHERE i.IngredientID = s.IngredientID AND a.IngredientID = s.IngSimID

For a single recipe:
SELECT i.IngredientName, r.IngredientName FROM ingredient_all AS i, ingredient_all AS a, ingredient_similar AS s, recipe_ingredient AS r
WHERE i.IngredientID = r.IngredientID AND i.IngredientID = s.IngredientID AND a.IngredientID = s.IngSimID

And BTW, try using shorter table and field names - the long and too-similar names made things fairly confusing. Eg. recipeIng.id rather than recipe_ingredient.recIngID

Thanks for the tips. That was exactly what I was looking for. I didn't know that you could reference a table twice, but it makes sense and seems to do the trick. I have also been planning on renaming all the tables and fields, but I haven't gotten around to it yet. I suppose I probably should before I write too many queries.

Your example code didn't work, but I think I know what the problem is; I think that ingredient_similar needs to be referenced twice as well, something like the following:

SELECT a.ingredientname, b.ingredientname
FROM ingredient_all AS a, ingredient_all AS b,
ingredient_similar AS x, ingredient_similar AS y,
recipe_ingredient AS z
WHERE a.IngredientID=z.IngredientID
AND a.IngredientID=x.IngredientID
AND x.IngSimID=y.IngSimID
AND y.IngredientID=b.IngredientID
AND z.recipeID=1

I'm going to test it out and see what it does.

-Biscuits

I'm getting closer. It shows Butter as being a substitute for Butter, for example. I need to exclude that obviously.... I'll figure it out shortly.

Thanks for the help!

Edit: Got it!

SELECT a.ingredientname AS Original_Ingredient, b.ingredientname AS Possible_Substitute
FROM ingredient_all AS a, ingredient_all AS b,
ingredient_similar AS x, ingredient_similar AS y,
recipe_ingredient AS z
WHERE a.IngredientID=z.IngredientID
AND a.IngredientID=x.IngredientID
AND x.IngSimID=y.IngSimID
AND y.IngredientID=b.IngredientID
AND z.RecipeID=1
AND (y.IngredientID>x.IngredientID OR y.IngredientID<x.IngredientID)
ORDER BY Original_Ingredient ASC
-Biscuits