Newbie Needing Help In Joining Databases

WD1812 · March 8, 2010

Hi,

Newbie here. Here's the scenario:

I have two tables in the database.

First table is Recipes. The fields in this one are: Title, Ingredients, Prep, Serves.

Second table is Categories. The fields in this one are: Title, Desserts, Drinks, Fowl, Meat, Pasta, Seafood, Vegetables.

User 1 enters the following into the form:

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. When a user clicks on the title, "Blueberry Pie," the ending of the URL address is recipesdesserts.php?recipeID=1Blueberry, but the page that's generated shows the recipe for the Strudel.

I've tried both of the following, and gotten the result mentioned above:

$query = "SELECT title".

"FROM recipes, categories".

"WHERE recipes.cat_id = categories.cat_id";

"AND category = desserts";

$query = "SELECT title".

"FROM recipes, categories".

"WHERE recipes.cat_id = categories.cat_id";

"AND recipeID = $recipeID";

This is how the recordset appears on the page that the recipe is supposed to be displayed on:

<?php echo $row_GetRecipes['Title']; ?></div>

<?php echo $row_GetRecipes['Ingredients']; ?></div>

<?php echo $row_GetRecipes['Prep']; ?></div>

<?php echo $row_GetRecipes['Serves']; ?></div>

I've been at this for a couple of days now, and would really appreciate any input on what exactly is wrong, and/or what changes need to be made.

Thank you.

gizmola · March 8, 2010

Hi,

Let's start with the information you've provided. It is not accurate, because you are apparently (and in my opinion correctly) using id's however you haven't indicated that in your preface. In looking at your tables, I'm not sure what you're doing with Categories, but it looks like you might need some restructuring.

Please do this using either the mysql client or phpMyAdmin:

describe Recipes;

describe Categories;


Also please wrap your php blocks in:

[code=php:0]  ...

and use code /code for your describes. This makes it easer to read, and to copy... paste, for those analyzing your problem.

[/code]

WD1812 · March 8, 2010

Hi,

I'm not exactly sure I understand what you mean by:

Please do this using either the mysql client or phpMyAdmin:

describe Recipes;

describe Categories;

Categories and Recipes are separate tables in the database.

This is how the recordset appears on the page that the recipe is supposed to be displayed on:

The code that appears underneath this in my original post, is in the body. Does it still need to be wrapped in php?

Thank you.

gizmola · March 8, 2010

The describe command will give you the table definition. If you have access to the server via ssh, and can run the mysql command line client this is helpful. If you only have phpMyAdmin, then use the export, uncheck data and export to sql. You will get something like this:

CREATE TABLE IF NOT EXISTS `innotest_utf8` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vc1` varchar(35) NOT NULL,
  `vc2` varchar(35) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10001 ;

Don't worry about the code for now, but for future posts use the php bbcode tag around your code.

WD1812 · March 8, 2010

This is what I got:

CREATE TABLE IF NOT EXISTS ‘Recipes’ (

‘Title’ varchar (100) DEFAULT NULL,

‘Ingredients’ varchar (1000) DEFAULT NULL,

‘Prep’ varchar (1000) DEFAULT NULL,

‘Serves’ varchar (30) DEFAULT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS ‘Categories’ (

‘Title’ varchar (100) DEFAULT NULL,

‘Desserts’ varchar (100) DEFAULT NULL,

‘Drinks’ varchar (100) DEFAULT NULL,

‘Fowl’ varchar (100) DEFAULT NULL,

‘Meat’ varchar (100) DEFAULT NULL,

‘Pasta’ varchar (100) DEFAULT NULL,

‘Seafood’ varchar (100) DEFAULT NULL,

‘Vegetables’ varchar (100) DEFAULT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

gizmola · March 9, 2010

Ok, so it does seem that you need some substantial rework of your database design. You seem to be confusing columns with rows. Your category table is almost entirely a repeating group, and doesn't actually relate to the Recipes in any useful way. You also don't have any primary keys or for that matter numeric keys, even though your queries included them.

I'll do my best to sort you out, but you'll have to do some reading on database normalization. I could also editorialize here, that it seems you have a bit of a mix of different things in your Categories table: for example, you have Desserts and Drinks, which only seems to be missing 'Appetizer' and 'Entre', & 'Side' mixed in with 'Fowl', 'Meat' & 'Seafood' which seem to me more to indicate the ingredients. Of course you are free to do whatever works for you in the design of the system, but I thought it might be worth thinking about.

Another system you could employ would be a 'Tag' system where you simply have a tag system, and you tag a Recipe with whatever tags you feel appropriate. That doesn't require too much thinking about your tags, and lets you side step the whole issue. If you think about a "tag" table as being the same as a 'Categories" row, it could work for you. Just to Help with the idea, I'll use your basic structure, but substitute a "tags" table for "Categories".

When you look in tags, you're going to see something like this:

tag_id: 1

tag: 'Dessert'

tag_id: 2

tag: 'Chicken'

tag_id: 3

tag: 'Pasta'

tag_id: 4

tag: 'Cocktail'

etc.

You can have as many different tags as you find useful, and again any recipe can be tagged as many times as makes sense for the recipe.

So the Main decision you need to make is:

Can a Recipe have more than one tag? (YES)

Can a tag be associated with more than one Recipe (YES)

What this means is that the relationship between Recipes and Tags is "Many to Many". Thus the structure requires 3 tables (recipes, tags, and recipestags). Here's how you put it all together:

If you find the diagram of use, make sure you copy it locally ... it's hosted on my server and I can't say for how long I'll leave it there.

So the main thing this does is handle properly the design of your relationship between categories (which I called tags) and recipes.

You might want to consider a similar exercise with ingredients -- it's more work, but would allow you to specify ingredients and get back recipes that use those ingredients. Ingredients also tend to have specific quantities associated with them, so it sometimes helps to quantify that, but this certainly adds greatly to the effort in loading in the data for a recipe. Depending on the nature of the site, while it offers advantages for searching, sorting etc., it makes it a lot tougher for data entry, which may or may not be worth it to you. Formalizing that would allow you to structure things like "substitutes" relationships -- but again this all depends on what you want out of the database.

Hope this helps get you on the right track -- if you have questions just ask.

# ---------------------------------------------------------------------- #
# Add table "recipes"                                                    #
# ---------------------------------------------------------------------- #

CREATE TABLE recipes (
    recipe_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    ingredients TEXT,
    prep TEXT,
    serves TINYINT UNSIGNED,
    created TIMESTAMP,
    CONSTRAINT PK_recipes PRIMARY KEY (recipe_id)
);

CREATE INDEX IDX_recipes_1 ON recipes (title);

# ---------------------------------------------------------------------- #
# Add table "tags"                                                       #
# ---------------------------------------------------------------------- #

CREATE TABLE tags (
    tag_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag VARCHAR(60) NOT NULL,
    created TIMESTAMP,
    CONSTRAINT PK_tags PRIMARY KEY (tag_id)
);

CREATE INDEX IDX_tags_1 ON tags (tag);

# ---------------------------------------------------------------------- #
# Add table "recipestags"                                                #
# ---------------------------------------------------------------------- #

CREATE TABLE recipestags (
    recipe_id INTEGER UNSIGNED NOT NULL,
    tag_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT PK_recipestags PRIMARY KEY (recipe_id, tag_id)
);

gizmola · March 9, 2010

Just because I'm sure you'll be wondering, the structure I provided gives you a couple different ways of approaching things. 1st off you can do a search on the "title". I've added an index on there, but any such search would need to be either an exact match or a LIKE 'search%'. You could also consider adding a FULLTEXT index on any of the TEXT columns I types (ingredients, prep etc.)

I changed serves to be numeric. If we assume "serves' is "a person" there's no reason to have a string in there.

Now for searching by Tags, you could have a Tag cloud, or a drop down of the "tag" list, or let people enter into a box and search on tags. The end goal is to come up with one or more tags. I'm not going to go into the complexity of multiple tags right now for the sake of simplicity -- but from a database point of view, let's just assume that somehow the user indicated a particular tag they wanted to use to match recipes. Once that is known, we can assume that we also know the tag_id. I say this because typically the UI will already display the tag name, and have the associated tag_id. It can be specified in a query, but often the tag_id is already resolved. So using the prior example, let's assume that someone wanted to get back a list of all recipes that were tagged with 'Chicken'.

SELECT * from recipes r JOIN recipestags rt ON (rt.recipe_id = r.recipe_id AND rc.tag_id = 3)

I had one small omission on the last .sql --- use this one

CREATE TABLE recipes (
    recipe_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    ingredients TEXT,
    prep TEXT,
    serves TINYINT UNSIGNED,
    created TIMESTAMP,
    CONSTRAINT PK_recipes PRIMARY KEY (recipe_id)
);

CREATE INDEX IDX_recipes_1 ON recipes (title);

# ---------------------------------------------------------------------- #
# Add table "tags"                                                       #
# ---------------------------------------------------------------------- #

CREATE TABLE tags (
    tag_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag VARCHAR(60) NOT NULL,
    created TIMESTAMP,
    CONSTRAINT PK_tags PRIMARY KEY (tag_id)
);

CREATE INDEX IDX_tags_1 ON tags (tag);

# ---------------------------------------------------------------------- #
# Add table "recipestags"                                                #
# ---------------------------------------------------------------------- #

CREATE TABLE recipestags (
    recipe_id INTEGER UNSIGNED NOT NULL,
    tag_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT PK_recipestags PRIMARY KEY (recipe_id, tag_id)
);

CREATE INDEX IDX_recipestags_1 ON recipestags (tag_id,recipe_id);

WD1812 · March 9, 2010

Talk about an overhaul!

Wow! Thank you!

I'm sorry, I should have also mentioned that I'm doing this in phpMyAdmin. Does that change anything?

gizmola · March 9, 2010

No, the scripts I provided can be run in a sql window in phpMyAdmin.

WD1812 · March 9, 2010

Ran the script in myadmin, and obviously it worked.

Added the SELECT code, and when previewing in a browser, got:

Parse error: syntax error, unexpected T_STRING

Do I have to reestablish the MySQL connection, or do something with the code and/or recordset in DW?

gizmola · March 9, 2010

No when you get a T_STRING error it means that your php syntax is incorrect. Often you are missing a required semicolon, endquote, end brace or any of the myriad details. A decent editor that does color syntax highlighting will often help you see the issue. Check your error log for the actual error message.

WD1812 · March 9, 2010

Entered:

tail /var/log/apache2/error_log

And the most recent error showing is:

[Mon Mar 08 12:40:38 2010] [error] script '/Library/WebServer/Documents/prx2.php' not found or unable to stat

That doesn't make any sense to me. Am I missing something here?

gizmola · March 9, 2010

Check for a php error file -- location would be indicated in phpinfo(). With that said, I'd just check your code. If it isn't a huge script paste it here inside the php bbcode blocks.

WD1812 · March 9, 2010

I've tried any command I could find to bring up the php error file, without any luck.

Can you give me an idea of what I should be looking for in the code?

Thank you!

gizmola · March 9, 2010

Post it here like so ... just omit any passwords


[code=php:0] 
Paste code in here

[/code]

WD1812 · March 9, 2010

<?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 = "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);
SELECT * from recipes r JOIN recipestags rt ON (rt.recipe_id = r.recipe_id AND rc.tag_id = 3)
?>

gizmola · March 10, 2010

If that is your exact code, I think you'll notice that the last line before the end tag looks like it was accidently pasted in. Try commenting out that line:

//SELECT * from recipes r JOIN recipestags rt ON (rt.recipe_id = r.recipe_id AND rc.tag_id = 3)

WD1812 · March 10, 2010

I got that last line from you. I wasn't supposed to enter that?

But of course, it might be a moot point anyway. Because now, I can't even enter/delete any code in DW. When I try to, all I get is the spinning rainbow, and DW just quits. I can't even do Edit>Cut.

I tried entering/deleting text in PS and Word, and did not have any problem.

I tried restarting the computer, nothing changed.

WD1812 · March 10, 2010

DW up and running again.

Friend of mine suggested holding down the shift key while restarting DW, and it worked.

Removed that last line of code, but still not getting the results I need.

Sign In

Newbie Needing Help In Joining Databases

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information