Query question

[wemustdesign]

I am creating a cocktail database and need help on a query if anyonr can help. The database has seperate tables for measurements and amounts

 

measurements are stored as 2|4|3

which translate to oz|dash|squeeze when the measurements table is queried.

 

The problem I am having is that I am not sure how to do simultaneous queries. I am wanting to add a conversion to show how much the amounts will be in ml. This translation would only apply to oz as a dash and squeeze would stay the same.

 

You can see what I mean viewing the page below. How do I carry out a query to only show the ml conversion for oz and not for a dash etc.

 

http://www.cocktail-recipe-guide.com/index.php?section=view_cocktail&cocktail=Adonis

 

Here is the current queries

while ($i2<$ingredients_count){
$query2="SELECT * FROM ingredients WHERE id = '$ingredients_explode[$i2]'";
$result2=mysql_query($query2);

$query3="SELECT * FROM measurement WHERE id = '$measurements_explode[$i2]'";
$result3=mysql_query($query3);

while($row2 = mysql_fetch_assoc($result2)){
$ingredients_n=$row2[name];
$ingredient_r = str_replace(" ", "_", "$ingredients_n"); 

while($row3 = mysql_fetch_assoc($result3)){
$measurement_n=$row3[name];

if ($amounts_explode[$i2] >1 && $measurement_n == "Part"){
$measurement_n = "Parts";
}
//convert to ml
$ml_convert = $amounts_explode[$i2]*$ml;
echo"
$amounts_explode[$i2] $measurement_n <a href=index.php?section=view_ingredient&ingredient=$ingredient_r>$ingredients_n</a>  ($ml_convert ml)<br />";
$i2++;
}}}

[monkeytooth]

What are you tempting to do exactly? If your trying to create a listing like the link you provided then I can't see why you would need to pull multiple things out of the database. As the link shows information pertaining to a single drink. Which if that's the case, best way to go about things in a matter of opinion at least is restructure your database so each drink is its own row and each row contains columns for the measurements, ingredients etc..

 

Well no scratch that I would suggest that over all. Cause if your trying to list drinks with similar ingredients etc then its still easier to pull info out like that restructuring accordingly.

 

Then rebuilding, and I am sure thats not the answer you wanna hear. but hell this is only my opinion someone may have a better answer. Personally I would go that route I am now actually on one of the sites I am working on. Its easier in opinion to stop what I am doing and rebuild then continuously tempt to hack around things to make it work. Easier in the end result of it all.

[kickstart]

Hi

 

I would agree with restructuring.

 

A table of cocktails, and then a table of ingredients. Ingredient table having one row per ingredient per cocktail, and a column for what the ingredient is and columns for the amounts.

 

A simple join would then give you the ingredients for a particular cocktail (and if you want to convert from oz to ml you could do that easily in the select or in php after the select).

 

All the best

 

Keith

[wemustdesign]

Yes this is what I have done. I have a table for cocktails with a new cocktail in every row. A table with the ingredients and measurements. The trouble I am having is linking all of the tables together for a query. I have attached a screenshot of how I have set up the database