Multiple Table Join?

Need help with admin part

[ksdfla]

Hello,

I'm working on a recipe database and trying to work out the content management portion, specifically to edit the recipe entries through a pre-populated form. I've done this in other projects, but they've only involved one or two tables, and no dropdown lists.

Here is the table structure:

recipe_vendor
vendorID
vendor

recipe_vendor_link
titleID
vendorID

recipe_category
categoryID
category

recipes_category_link
titleID
categoryID

recipe_titles
titleID
imagepath
title
ingredients
directions
contributor
approved
date_submitted

So, on the admin side, I click a link that takes me to the edit page where a form is pre-filled with the values so they can be edited and updated in the database. It's the query to retrieve the values that I'm having trouble with. If it were just pulling data from the recipe_titles, I know how to do that. But I also need to populate 3 dropdowns that show the vendors and 3 dropdowns that show the categories--and what if any vendors and categories have already been selected with a particular recipe (and is that even possible?). I'm totally lost on how to do such a join since the information comes from other tables.

I started to do this, among other things:

$query = "SELECT * 
FROM recipe_titles rt JOIN recipe_vendor_link rvl ON rt.titleID = rvl.titleID JOIN recipe_category_link rcl ON 
rt.titleID = rcl.titleID
? = '$titleID'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

but I've got the feeling I'm heading off in the wrong direction, plus I can't figure out how to bring the recipe_vendor and recipe_category tables into play since I need to display the vendors and categories, not the vendorID's and categoryID's.

I guess I need to select everything from recipe_titles -- and I need the vendors from recipe_vendor, and the categories from recipe_category, based on the titleID in recipe_vendor_link and recipe_category_link. (Each recipe can have up to 3 vendors and 3 categories associated with it, and each has at least one vendor and one category.)

For example, the recipe Brownies might have a titleID of 6 and appear under 2 categories, say Chocolate and Desserts. If Chocolate is category 1 and Desserts is category 3, then in the recipe_category_link, it would look like this:

titleID categoryID
6 1
6 3

and under recipe_category
categoryID category
1 Chocolate
3 Desserts

and under recipe_titles
titleID
6

In general, how in the world do I bring that all together to edit if I want to add/delete/or change a category? Would I query the recipe_category_link and recipe_category tables based on the titleID... delete what's been inserted, and then update the first two tables with the different category/ies? (And I've got the same situation with the vendors...)

I hope this has made some sense...

Help, please?

Thanks,
ksd

[coopster]

You're right, there is a lot going on here...let's take one thing at a time.

If it were just pulling data from the recipe_titles, I know how to do that. But I also need to populate 3 dropdowns that show the vendors and 3 dropdowns that show the categories

I'm not quite sure what you mean when you say you need three dropdowns to show the vendors? I'll assume for now you need one of the dropdowns to be a vendor list, so when building a drop down list like this, I'll often use the DISTINCT [dev.mysql.com] keyword:

$vendorID = (isset($_POST['vendorID']))? $_POST['vendorID'] : ''; 
$vendor_list = ''; // initialize list 
$sql = "SELECT 
 DISTINCT vendorID, vendor 
 FROM recipe_vendor 
 ORDER BY vendor 
"; 
$rows = mysql_query($sql); 
if (mysql_num_rows($rows) <> 0) { 
 while ($row = mysql_fetch_assoc($rows)) { 
  $vendor_list .= '<option value="' . htmlentities($row['vendorID']) . '"'; 
  if ($vendorID == $row['vendorID']) $vendor_list .= ' selected="selected"'; 
  $vendor_list .= '>' . htmlentities($row['vendor']). '</option>'; 
 } 
} 
mysql_free_result($rows); 
... 
// Then in the html... 
... 
<select id="vendor_list" name="vendor_list"> 
 <?php print $vendor_list;?> 
</select>

The only variable that isn't explained in here is the $vendorID variable. I use it to determine whether or not the user has already been on this form and selected it once already (notice it is set by checking the $_POST superglobal).

[ksdfla]

Hi Coopster, and thank you.

Somehow I missed that this post had a response, and I apologize.

I just tried this code but received this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ourfamil/public_html/coopster.php on line 46

and line 46 is this:
if (mysql_num_rows($rows) <> 0) {

Regarding the dropdowns, each recipe can have up to 3 vendors and up to 3 categories associated with it. So there are 3 pulldowns for selecting vendors and 3 pulldowns for selecting categories on the original submit form.

Thank you,
ksd

[jatar_k]

it would seem that the query died

change one line and then get the error message

$rows = mysql_query($sql);

to

$rows = mysql_query($sql) or die (mysql_error());

[ksdfla]

Thanks... I got this error message:

Parse error: parse error, unexpected T_IF in /home/ourfamil/public_html/coopster.php on line 47

and line 47 is
if (mysql_num_rows($rows) <> 0) {

Also, I'm still lost...even if I change my table structure to 3 tables instead of 5 as suggested yesterday, I'll need to prepopulate the pulldowns based on data from two tables in either case, recipes_vendor_link and recipe_vendor. I can retrieve the values that have been selected (at the present time from the recipes_vendor_link table, but I have to incorporate them with the values from the recipe_vendor table to give a full pulldown (with all the choices as in the original form), but also show the value already chosen, if any, as preselected.

Thanks,
ksp

[ergophobe]

The line 47 error is because of something *above* line 47, probably a missing semicolon, brace or the like.

Tom