Jump to content

Join command, relation database, or what?


badams_ios

Recommended Posts

Sorry, I'm pushing 60 and just don't absorb reading material well anymore:/ I don’t know if I should have some kind of relation between tables, use a join command or what. I’ll need help with the syntax too. I’m working on a recipe program. Think of it like this. I have several cookbooks. When you open a cookbook, I want to see chapters in that cookbook. When I click on a chapter I want to see recipes. If I open a different cookbook I want to see chapters related only to that cookbook. I’ll include the database structure below. Also including an image of my web page.

 

Using dreamweaver cs3 I created a table with 2 rows and 4 columns. Top row, left most column just says “cookbooks”. Next column says “add cookbook”. 2nd row, left most column displays cookbook names using a repeat region. e.g. “Brian’s Cookbook”, “Mary’s cookbook”…etc. Next column is change cookbook name, then delete cookbook, and last column says chapters. Each book has its own chapters. E.g. Brian’s cookbook might have a chapter called drinks, another called stocks, and another called sauces. Mary’s cookbook may have pasta dishes, cookies…etc. How do I get it so that if I click chapters next to Brian’s cookbook I get my chapters, and when I click on Mary’s cookbook she sees her own chapters? Also, when I create a new cookbook it needs to create a new place to hold chapters for that cookbook. I have a cookbook program (cook’n version 10) that does just this, but it literally takes 50 seconds to load. Moving around the program is slow, and it crashes all the time.

 

Here’s my mysql structure:

-- phpMyAdmin SQL Dump

-- version 3.3.5

-- http://www.phpmyadmin.net

--

-- Host: 127.0.0.1

-- Generation Time: Sep 29, 2013 at 03:25 AM

-- Server version: 5.1.36

-- PHP Version: 5.3.26

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

 

--

-- Database: `Gourmet_Entertainment`

--

 

-- --------------------------------------------------------

 

--

-- Table structure for table `chapter`

--

 

CREATE TABLE IF NOT EXISTS `chapter` (

  `chapter_id` int(11) NOT NULL AUTO_INCREMENT,

  `Chapter_Name` varchar(100) NOT NULL,

  PRIMARY KEY (`chapter_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

 

--

-- Dumping data for table `chapter`

--

 

INSERT INTO `chapter` (`chapter_id`, `Chapter_Name`) VALUES

(1, 'Pasta'),

(2, 'Sauces'),

(4, 'Soups'),

(5, 'booze');

 

-- --------------------------------------------------------

 

--

-- Table structure for table `cookbook`

--

 

CREATE TABLE IF NOT EXISTS `cookbook` (

  `cookbook_id` int(11) NOT NULL AUTO_INCREMENT,

  `cookbook_name` varchar(100) NOT NULL,

  PRIMARY KEY (`cookbook_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

 

--

-- Dumping data for table `cookbook`

--

 

INSERT INTO `cookbook` (`cookbook_id`, `cookbook_name`) VALUES

(13, 'Brian''s Cookbook'),

(18, 'Mary''s'),

(20, '');

 

-- --------------------------------------------------------

 

--

-- Table structure for table `menu`

--

 

CREATE TABLE IF NOT EXISTS `menu` (

  `menu_id` int(11) NOT NULL AUTO_INCREMENT,

  `recipe_id` int(11) DEFAULT '0',

  `name` varchar(100) DEFAULT NULL,

  `prep_date` date DEFAULT NULL,

  `cook_date` date DEFAULT NULL,

  PRIMARY KEY (`menu_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

--

-- Dumping data for table `menu`

--

 

 

-- --------------------------------------------------------

 

--

-- Table structure for table `recipe`

--

 

CREATE TABLE IF NOT EXISTS `recipe` (

  `recipe_id` int(11) NOT NULL AUTO_INCREMENT,

  `cookbook_id` int(11) DEFAULT '0',

  `chapter` int(11) DEFAULT '0',

  `page` int(11) DEFAULT '0',

  `recipe_name` varchar(100) DEFAULT NULL,

  `recipe` text,

  PRIMARY KEY (`recipe_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

--

-- Dumping data for table `recipe`

--

 

INSERT INTO `recipe` (`recipe_id`, `cookbook_id`, `chapter`, `page`, `recipe_name`, `recipe`) VALUES

(1, 2, 2, 0, 'Drunken shrimp', 'cook with scotch'),

(2, 2, 2, 0, 'Alfredo Sauce', 'Lots of cream.');

 

-- --------------------------------------------------------

 

--

-- Table structure for table `recipe_incredents`

--

 

CREATE TABLE IF NOT EXISTS `recipe_incredents` (

  `recipe_incredents_id` int(11) NOT NULL AUTO_INCREMENT,

  `recipe_id` int(11) DEFAULT '0',

  `incredents_id` int(11) DEFAULT '0',

  `name` varchar(100) DEFAULT NULL,

  `quantity` float DEFAULT '0',

  `measurements` varchar(20) NOT NULL,

  PRIMARY KEY (`recipe_incredents_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

post-165621-0-05969400-1380492571_thumb.jpg

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.