Join 3 to 4 MySQL tables to display 2 fields

General PHP Help
#1

I’m lost and need help with a complex join of at least 3 tables.

I have 4 tables (recipes, recipes2tips, tips, and tips2recipes) with the following column names:

recipes = id, name, descrip, video

recipes2tips = id, recipe, tip

tips = id, name, descrip, video

tips2recipes = id, tip, recipe

I’m trying to display all tips that are related to each recipe WHERE the recipes.id will be the joining factor. The problem is that all of the tips ids are equal to the recipes2tips.tip and the tips2recipes.tip.

Here is my first attempt but it’s missing something:
[php]
SELECT tips.id, tips.name, recipes2tips.recipe, recipes2tips.tip, recipes.id
FROM tips, recipes2tips, recipes
WHERE tips.id = recipes2tips.tip;
[/php]

Any suggestions would be great.

Thanks

#2

There ya go:
[php]// pull all records…
$query = “SELECT * FROM recipies
LEFT JOIN recipies2tips
ON recipies.id=recipies2tips.id
LEFT JOIN tips
ON recipies.id=tips.id
LEFT JOIN tips2recipies
ON recipies.id=tips2recipies.id
ORDER BY recipies.id ASC”;
[/php]

[code]// to pull a single record swap the last line:
ORDER BY recipies.id ASC";

// for these two:
WHERE recipies.id = ‘" . $id . "’
LIMIT 1";
[/code]

Note: code is untested however it should work ok.
Red :wink:

#3

Thank you for this! Really pointed me in the right direction. Thanks again!

#4

Just something to add, use the built-in query builder. It’s so much more effective than writing your own queries.

#5

No worries, happy to help.
Red :wink:

Sponsor our Newsletter | Privacy Policy | Terms of Service