Main menu

Subscribe to database

database

MySQL 5 Stored Procedure Examples

Submitted by mikehostetler on Thu, 04/26/2007 - 08:26

As I've been experimenting more with MySQL's stored procedures, I have found that there are very few examples online. Questions I had about the stored procedures were difficult to answer. So, here's an archive of stored procedure examples that I have written for a current project. Hopefully they will help someone.

Taxonomy upgrade extras: 
PHP
mysql
database
stored procedures
transactions
examples

How I build an application - Part 3 - The Tables

Submitted by mikehostetler on Thu, 03/29/2007 - 13:40

This article the 3rd installment in of a series titled 'How I build an application'. You can find the other two installments and subsection here.

I mentioned last time that I would discuss why I choose certain data types for certain fields.

Recipe Ingredient Table

--
-- Table structure for table recipe_ingredient
--

CREATE TABLE IF NOT EXISTS recipe_ingredient (
  recipe_ingredient_id int(10) unsigned NOT NULL auto_increment,
  recipe_id int(10) unsigned NOT NULL default '0',
  unit_id int(10) unsigned NOT NULL default '0',
  ingredient_id int(10) unsigned NOT NULL default '0',
  unit_amount tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (recipe_ingredient_id),
  KEY IDX_recipe_ingredient_recipe_id (recipe_id),
  KEY IDX_recipe_ingredient_unit_id (unit_id),
  KEY IDX_recipe_ingredient_ingredient_id (ingredient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is one that ties a lot together, so we'll start here. This table contains a primary key and three foreign keys. I try to always construct my integer keys using a display length of 10, marking them unsigned with a default of zero. Whether a field is marked at 'NULL' or 'NOT NULL' depends on the design.

How I build an application - Part 2 - The Database

Submitted by mikehostetler on Tue, 03/20/2007 - 09:29

Onto the next stage. I've got my vision nailed, now it's time to start building.

This article is a continuation of How I build an application - Part 1. Check it out if you missed it. Last time, I came up with the idea of a little tool to help plan meals with the goal of eating healthier and spending grocery money more efficiently. I came up with the following drawing for my database.