Jump to content

Having Trouble Joining Some Tables.


WD1812
 Share

Recommended Posts

Hi,I'm still pretty new at mysql/php/web design, and have been at this for 2 weeks now, so I'm hoping someone can help straighten this out.First Table - Recipes:category_idtitleingredientsprepservesSecond Table - Categories:category_id (Values are: 1, 2, 3, 4, 5, 6, 7)category_name (Values are: Desserts, Drinks, Fowl, Meat, Pasta, Seafood, Vegetables)Here's the scenario:User 1 enters the following into a form on the Main Desserts Page:Title: Apple StrudelIngredients: Apples, Flour, WaterPrep: Core and slice apples, etc.Serves: 6User 2 enters the following into the form:Title: Blueberry PieIngredients: Blueberries, Flour, WaterPrep: Wash blueberries, etc.Serves: 6Both titles now appear on the Main Desserts page, which is what I want. When a user clicks on the title, "Apple Strudel," the page that's created shows that recipe. However, when a user clicks on the title, "Blueberry Pie," while the ending of the URL address is recipesdesserts.php?recipeID=1Blueberry, the page that's created shows the recipe for the Strudel. And I have not been able to figure out why.No matter how I write the code (and I've tried several variations), the same thing keeps happening.I would appreciate any insight/suggestions/ideas for making this work. If any further info would be useful, please let me know.Thank you!

Link to comment
Share on other sites

You'll need to show the code which displays the records, also verify that the information for the second recipe does not have the same ID as for the first recipe (i.e., that the data in the database is correct).
Should also mention that this was done in DW CS4.Here's the code:<?php require_once('../../Connections/clan_db.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue;}}$currentPage = $_SERVER["PHP_SELF"];$editFormAction = $_SERVER['PHP_SELF'];if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);}if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO Recipes (Title, Ingredients, Prep, Serves) VALUES (%s, %s, %s, %s)", GetSQLValueString($_POST['Title'], "text"), GetSQLValueString($_POST['Ingredients'], "text"), GetSQLValueString($_POST['Prep'], "text"), GetSQLValueString($_POST['Serves'], "text")); mysql_select_db($database_clan_db, $clan_db); $Result1 = mysql_query($insertSQL, $clan_db) or die(mysql_error()); $insertGoTo = "../Recipes/recipesdesserts.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo));}$maxRows_GetRecipes = 10;$pageNum_GetRecipes = 0;if (isset($_GET['pageNum_GetRecipes'])) { $pageNum_GetRecipes = $_GET['pageNum_GetRecipes'];}$startRow_GetRecipes = $pageNum_GetRecipes * $maxRows_GetRecipes;mysql_select_db($database_clan_db, $clan_db);$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes";$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);$GetRecipes = mysql_query($query_limit_GetRecipes, $clan_db) or die(mysql_error());$row_GetRecipes = mysql_fetch_assoc($GetRecipes);if (isset($_GET['totalRows_GetRecipes'])) { $totalRows_GetRecipes = $_GET['totalRows_GetRecipes'];} else { $all_GetRecipes = mysql_query($query_GetRecipes); $totalRows_GetRecipes = mysql_num_rows($all_GetRecipes);}$totalPages_GetRecipes = ceil($totalRows_GetRecipes/$maxRows_GetRecipes)-1;$queryString_GetRecipes = "";if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_GetRecipes") == false && stristr($param, "totalRows_GetRecipes") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_GetRecipes = "&" . htmlentities(implode("&", $newParams)); }}$queryString_GetRecipes = sprintf("&totalRows_GetRecipes=%d%s", $totalRows_GetRecipes, $queryString_GetRecipes);?>also verify that the information for the second recipe does not have the same ID as for the first recipe (i.e., that the data in the database is correct).I checked the database (phpmyadmin), and the cateogry id in the Recipes Table is showing as 0 for each one. Title, Ingredients, Prep, Serves are correct for each one. The URL ending for the Strudel is recipesdesserts.php?recipeID=1Apple.
Link to comment
Share on other sites

I'm not seeing anywhere in that code where it looks for something called recipeID, in fact I don't see it displaying anything at all. It gets all of the recipes from the database, but never prints them. Is that the code for the page which displays the recipe?Did you write that code or was that generated? There are some large chunks of code that are essentially unnecessary or are duplicating built-in behavior.

Link to comment
Share on other sites

I'm not seeing anywhere in that code where it looks for something called recipeID, in fact I don't see it displaying anything at all. It gets all of the recipes from the database, but never prints them. Is that the code for the page which displays the recipe?Did you write that code or was that generated? There are some large chunks of code that are essentially unnecessary or are duplicating built-in behavior.
Sorry about that.This is the code (generated by DW) on the display page:<?php require_once('../../Connections/clan_db.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue;}}$currentPage = $_SERVER["PHP_SELF"];$editFormAction = $_SERVER['PHP_SELF'];if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);}if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO Recipes (Title, Ingredients, Prep, Serves) VALUES (%s, %s, %s, %s)", GetSQLValueString($_POST['Title'], "text"), GetSQLValueString($_POST['Ingredients'], "text"), GetSQLValueString($_POST['Prep'], "text"), GetSQLValueString($_POST['Serves'], "text")); mysql_select_db($database_clan_db, $clan_db); $Result1 = mysql_query($insertSQL, $clan_db) or die(mysql_error()); $insertGoTo = "recipesdesserts.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo));}$maxRows_GetRecipes = 10;$pageNum_GetRecipes = 0;if (isset($_GET['pageNum_GetRecipes'])) { $pageNum_GetRecipes = $_GET['pageNum_GetRecipes'];}$startRow_GetRecipes = $pageNum_GetRecipes * $maxRows_GetRecipes;mysql_select_db($database_clan_db, $clan_db);$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes";$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);$GetRecipes = mysql_query($query_limit_GetRecipes, $clan_db) or die(mysql_error());$row_GetRecipes = mysql_fetch_assoc($GetRecipes);if (isset($_GET['totalRows_GetRecipes'])) { $totalRows_GetRecipes = $_GET['totalRows_GetRecipes'];} else { $all_GetRecipes = mysql_query($query_GetRecipes); $totalRows_GetRecipes = mysql_num_rows($all_GetRecipes);}$totalPages_GetRecipes = ceil($totalRows_GetRecipes/$maxRows_GetRecipes)-1;$queryString_GetRecipes = "";if (!empty($_SERVER['QUERY_STRING'])) { $params = explode("&", $_SERVER['QUERY_STRING']); $newParams = array(); foreach ($params as $param) { if (stristr($param, "pageNum_GetRecipes") == false && stristr($param, "totalRows_GetRecipes") == false) { array_push($newParams, $param); } } if (count($newParams) != 0) { $queryString_GetRecipes = "&" . htmlentities(implode("&", $newParams)); }}$queryString_GetRecipes = sprintf("&totalRows_GetRecipes=%d%s", $totalRows_GetRecipes, $queryString_GetRecipes);?>This is the code for where the information is shown on the display page:<div id="titlecontainer"> <?php echo $row_GetRecipes['Title']; ?></div> <div id="ingredientscontainer"> <?php echo $row_GetRecipes['Ingredients']; ?></div> <div id="prepcontainer"> <?php echo $row_GetRecipes['Prep']; ?></div> <div id="servescontainer"> <?php echo $row_GetRecipes['Serves']; ?></div>
Link to comment
Share on other sites

That code still isn't using the recipeID value, the parts where it gets recipes from the database are getting all of the records from the recipes table with support for paging and stuff like that. This is the code which gets records:

$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes";$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);$GetRecipes = mysql_query($query_limit_GetRecipes, $clan_db) or die(mysql_error());$row_GetRecipes = mysql_fetch_assoc($GetRecipes);

You can see the select query doesn't even have a WHERE clause in it, which would select a specific record (like the one you passed the ID for). Instead, since there's no WHERE clause, it's just getting all records, but then it stores only the first record in $row_GetRecipes. So, if this is all the code on the page, the only thing this page will do is get all recipes, then display the first one.If you want it to get a specific record based on the ID in the URL, you need to add a WHERE clause to that query to tell it which record to get.

Link to comment
Share on other sites

That code still isn't using the recipeID value, the parts where it gets recipes from the database are getting all of the records from the recipes table with support for paging and stuff like that. This is the code which gets records:
$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes";$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);$GetRecipes = mysql_query($query_limit_GetRecipes, $clan_db) or die(mysql_error());$row_GetRecipes = mysql_fetch_assoc($GetRecipes);

You can see the select query doesn't even have a WHERE clause in it, which would select a specific record (like the one you passed the ID for). Instead, since there's no WHERE clause, it's just getting all records, but then it stores only the first record in $row_GetRecipes. So, if this is all the code on the page, the only thing this page will do is get all recipes, then display the first one.If you want it to get a specific record based on the ID in the URL, you need to add a WHERE clause to that query to tell it which record to get.

Tried"WHERE recipes.cat_id = categories.cat_id";and nothing changed.Would I also need an "AND" clause?
Link to comment
Share on other sites

How about this:

$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes WHERE Recipes.ID=" . GetSQLValueString($_GET['recipeID'], 'text');

That assumes the table contains a field called ID, with the value that was passed in the URL.

Link to comment
Share on other sites

How about this:
$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes WHERE Recipes.ID=" . GetSQLValueString($_GET['recipeID'], 'text');

That assumes the table contains a field called ID, with the value that was passed in the URL.

I replaced$query_GetRecipes = "SELECT Recipes.Title, Recipes.Ingredients, Recipes.Prep, Recipes.Serves FROM Recipes";with the code you provided, and the pages came up with the same URL addresses as before, but no text.Tried leaving just the code you provided, and the pages came up w/the same URL addresses as before, but no text. Left in the code you provided, and tried removing$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);and when I clicked on a Recipe Title, it came back Query was empty.The Recipes Table does have a field called id.With regards to the value that was passed in the URL, and this may be a stupid question, do I need to add values to id, as I did for category_id, and category_name in the Categories Table?
Link to comment
Share on other sites

I'm not quite sure what you're asking, but the ID passed in the URL needs to correspond to a value in the ID field with the same value. So, if the URL has "1Apple" for the recipeID, then the recipes table needs a record with "1Apple" in the ID field.

Link to comment
Share on other sites

I'm not quite sure what you're asking, but the ID passed in the URL needs to correspond to a value in the ID field with the same value. So, if the URL has "1Apple" for the recipeID, then the recipes table needs a record with "1Apple" in the ID field.
Maybe I'm not explaining things correctly, or I'm just lost. Because how can I enter a specific Record in the Recipes Table if I don't know what a user is going to Title the recipe that they enter?
Link to comment
Share on other sites

The code would either generate the ID or ask the user for it, and the links to view the recipes would just put the ID from the database in the link. So you don't need to know anything, you just have it get all of the records and print the links, and it will print whatever ID is in the record, and then use that ID on the next page to look up the record. But, if you're clicking on a link that says the recipeID is "1Apple", and you look in the database and don't see a record with an ID of "1Apple", then that's the reason it's not displaying anything, it can't find the record it's being told to look up.

Link to comment
Share on other sites

The code would either generate the ID or ask the user for it, and the links to view the recipes would just put the ID from the database in the link. So you don't need to know anything, you just have it get all of the records and print the links, and it will print whatever ID is in the record, and then use that ID on the next page to look up the record. But, if you're clicking on a link that says the recipeID is "1Apple", and you look in the database and don't see a record with an ID of "1Apple", then that's the reason it's not displaying anything, it can't find the record it's being told to look up.
if you're clicking on a link that says the recipeID is "1Apple", and you look in the database and don't see a record with an ID of "1Apple", then that's the reason it's not displaying anything, it can't find the record it's being told to look up.I guess that's what's happening, because if I click on Apple, the URL shows recipeID=1Apple, and if I click on Blueberry, the URL shows recipeID=1Blueberry. Even though I have a box in the form where the user can enter a Category ID (i.e. 1 for Desserts, 2 for Drinks, etc).Which leads me back to what I asked before about values in the fields. This is how the Categories Table is set up:Second Table - Categories:category_id (Values are: 1, 2, 3, 4, 5, 6, 7)category_name (Values are: Desserts, Drinks, Fowl, Meat, Pasta, Seafood, Vegetables)Should I have the same set up in the Recipes Table, since that's where the code is going to for the information?And for what it's worth, this is the code that creates the Link from the Title that's entered. <?php do { ?><li><a href="../Recipes/recipesdesserts.php?recipeID=1<?php echo $row_GetRecipes['Title']; ?>"><strong><?php echo $row_GetRecipes['Title']; ?></strong></a></li><?php } while ($row_GetRecipes = mysql_fetch_assoc($GetRecipes)); ?>
Link to comment
Share on other sites

Oh.. then it's not even printing the recipe ID, it's using the title, and for whatever reason always putting a 1 before it. You should change that so it prints only the recipe ID without the extra 1, the ID is what you use to look it up in the table. This isn't the category ID by the way, this is the recipe ID. In this case you're not looking up the category, you're looking up the recipe. You should see in the recipes table that the ID will probably also be an autonumber, it will probably start at 1 with the first record and go up from there.

Link to comment
Share on other sites

Oh.. then it's not even printing the recipe ID, it's using the title, and for whatever reason always putting a 1 before it. You should change that so it prints only the recipe ID without the extra 1, the ID is what you use to look it up in the table. This isn't the category ID by the way, this is the recipe ID. In this case you're not looking up the category, you're looking up the recipe. You should see in the recipes table that the ID will probably also be an autonumber, it will probably start at 1 with the first record and go up from there.
Just to be sure, I deleted the Recipes Table I had, and created a new one. Included a recipe_id set as Primary Key, and to auto increment. The only change was the very end of the URL, as it now reads: 1Apple%20Strudel, or 1Blueberry%20PieI removed the 1 from <li><a href="../Recipes/recipesdesserts.php?recipeID=1<?phpand I get a page without the info, with an ending URL of recipeID=I'm guessing I need to replace the 1 with something else?
Link to comment
Share on other sites

Make sure it prints the ID in the link and not the title.
Is that something like<li><a href="../Recipes/recipesdesserts.php?recipeID=1<?php echo $row_GetRecipes['Title']; ?>"><strong><?php echo $row_GetRecipes['Title']; ?><?php print_r($recipes); ?>/strong></a></li>
Link to comment
Share on other sites

<a href="../Recipes/recipesdesserts.php?recipeID=<?php echo $row_GetRecipes['ID']; ?>">Make sure the capitalization is correct.
Same result. No matter which title I click on, the recipe that comes up is for the Strudel. But the ending of the URL is the same for each one:recipesdesserts.php?recipeID=Could there be something wrong w/how the database was set up, or is it still the code?I appreciate your time!
Link to comment
Share on other sites

That means the field name is wrong, or the field is empty:$row_GetRecipes['ID']Make sure a field called "ID" exists in the recipe table, and that's not empty. Capitalization is important.
ID does exist in the recipes table. As far as whether or not it's empty, I'm still looking for the command to check that.
Link to comment
Share on other sites

You can use phpMyAdmin to look at all of the records in the table. The Browse tab on the table view will show you that. You can also just run a SQL statement in the SQL tab:SELECT * FROM users
I knew about the Browse tab, but thought it was something different I had to check. Anyway, this is what came up via Browse: recipe_id ID title ingredients prep serves 1 0 Apple Strudel Apples Mix 4 2 0 Blueberry Pie Blueberries Mix 4The recipe_id being equal to 1 and 2 respectively seems odd, because the ending of the URL is recipeID= Edited by WD1812
Link to comment
Share on other sites

That's not odd, the code is doing exactly what it's being told to do. It's being told to print the ID field, not the recipe_id field. The ID field looks like it has a value of 0. It needs to use the recipe_id field instead.
This would seem to be the answer:<?php do { ?><li><a href="../Recipes/recipesdesserts.php?recipeID=<?php echo $row_GetRecipes['recipe_id']; ?>"><strong><?php echo $row_GetRecipes['Title']; ?></strong></a></li><?php } while ($row_GetRecipes = mysql_fetch_assoc($GetRecipes)); ?>but yet, it's not.In phpmyadmin, recipe_id, appears as recipe_id, which, I'm assuming is because it's set as an integer. Any possibility it should actually be set as text or varchar? Because, if I do <?php echo $row_GetRecipes['Title'];?>"> the ending of the URL shows recipeID=Apple%20Strudel, and in phpmyadmin, Title is set as varchar.And if that's not the answer, is there any possibility that there's something wrong w/the rest of the php that DW generated, and which appears above <!DOCTYPE html PUBLIC ...?
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...