Page 1 of 1

Joining Tables Problem

Posted: Fri Mar 12, 2010 1:11 pm
by WD1812
Hi,

I'm still pretty new at php/web design, and have been at this for a week now, so I'm hoping someone can straighten this out.

Here's the scenario:

User 1 enters the following into a form on the Main Desserts Page:

Title: Apple Strudel
Ingredients: Apples, Flour, Water
Prep: Core and slice apples, etc.
Serves: 6

User 2 enters the following into the form:

Title: Blueberry Pie
Ingredients: Blueberries, Flour, Water
Prep: Wash blueberries, etc.
Serves: 6

Both titles now appear on the Main Desserts page, which is what I want. When a user clicks on the title, "Apple Strudel," they are taken to the page with that recipe on it. 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 generated shows the recipe for the Strudel.

I've tried entering each of the following codes on both pages, as well as one page at a time, without any luck.

$query = "SELECT title".
"FROM recipes, categories".
"WHERE recipes.cat_id = categories.cat_id";
"AND recipeID = $recipeID";

$query = "SELECT r.title
FROM recipes r
WHERE r.recipeID = $recipeID";
echo $query;

$query = "SELECT title".
"FROM recipes, categories".
"WHERE recipes.cat_id = categories.cat_id" . "AND recipeID = $recipeID";


I would appreciate any insight/suggestions/ideas for making this work. If any further info would be useful, please let me know.

Thank you.

Re: Joining Tables Problem

Posted: Fri Mar 12, 2010 3:39 pm
by DaiLaughing
I don't get this at all. Are those two not the same thing - recipes? So they would be held in the same table as (say) recipe 1 and recipe 2.

The two tables would be recipe and ingredient and the join would be one recipe to many ingredients.

As for your question (I think) both links would be identical up to recipeID=. Then you would $_GET the value for that and use it in your query. We certainly need much more information about this to help.

Re: Joining Tables Problem

Posted: Mon Mar 15, 2010 10:40 am
by WD1812
Made some changes to the tables, but still no luck w/the query.

First Table - Recipes:

category_id
title
ingredients
prep
serves

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

Re: Joining Tables Problem

Posted: Mon Mar 15, 2010 12:12 pm
by AbraCadaver
You need to echo your vars and make sure they are what you think they are. What is $recipeID?

Re: Joining Tables Problem

Posted: Tue Mar 30, 2010 9:41 am
by WD1812
I made some changes to the tables, which are now:

First Table - Recipes:

Columns are:

recipe_id (Set as Primary Key, Auto Increment)
title
ingredients
prep
serves

Second Table - Categories:

Columns/values are:

category_id (Values: 1, 2, 3, 4, 5, 6, 7)
category_name (Values: Desserts, Drinks, Fowl, Meat, Pasta, Seafood, Vegetables)

And tried this code:

<?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 still have the same recipe being displayed no matter which recipe title I click on. And the ending of the URL is recipeID=

And yet, if I replace <?php echo $row_GetRecipes['recipe_id']; ?>"> with <?php echo $row_GetRecipes['Title']; ?>">

the ending of the URL is recipeID=Apple20%Strudel no matter which recipe title I click on.

In phpmyadmin, recipe_id is set as an integer, and title as varchar. Any chance that recipe_id needs to be varchar, as well? And if that's not the answer, is there any chance that there's something in the rest of the code that DW generated, and which appears above <!DOCTYPE html PUBLIC...?

Re: Joining Tables Problem

Posted: Tue Mar 30, 2010 9:49 am
by mikosiko
the code that you are showing is incomplete to be able to give you better advise... show the code that include the select that you are using

Miko

Re: Joining Tables Problem

Posted: Tue Mar 30, 2010 10:01 am
by WD1812

Code: Select all

<?php require_once('../../Connections/clan_db.php'); ?>
<?php
if (!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);
?>
And in case this ties into it some how, this is the code regarding previous and next:

<a href="<?php printf("%s?pageNum_GetRecipes=%d%s", $currentPage, max(0, $pageNum_GetRecipes - 1), $queryString_GetRecipes); ?>">Previous</a> Showing<?php echo ($startRow_GetRecipes + 1) ?>...<?php echo min($startRow_GetRecipes + $maxRows_GetRecipes, $totalRows_GetRecipes) ?> out of <?php echo $totalRows_GetRecipes ?> <a href="<?php printf("%s?pageNum_GetRecipes=%d%s", $currentPage, min($totalPages_GetRecipes, $pageNum_GetRecipes + 1), $queryString_GetRecipes); ?>">Next</a>

Thank you.

Re: Joining Tables Problem

Posted: Tue Mar 30, 2010 11:46 am
by mikosiko

Code: Select all

$query_limit_GetRecipes = sprintf("%s LIMIT %d, %d", $query_GetRecipes, $startRow_GetRecipes, $maxRows_GetRecipes);
check the values of $startRow_GetRecipes, $maxRows_GetRecipes before this line and analyze from there...

also check the value of $_GET['totalRows_GetRecipes']

Re: Joining Tables Problem

Posted: Tue Mar 30, 2010 2:17 pm
by WD1812
Forgive me for asking, but how do I check the values of $startRow_GetRecipes, $maxRows_GetRecipes, etc?

Re: Joining Tables Problem

Posted: Thu Apr 01, 2010 8:51 am
by mikosiko
using the basic echo function.... here : http://php.net/manual/en/function.echo.php