PHPBuilder - So you want to use a database in your site? Page 2



RSS Twitter
Articles Databases

So you want to use a database in your site? - Page 2

by: Jess Castagnetto
|
July 30, 2000

In the examples below, we will use the database "documents" (non-normalized), in which we will store, for example, the articles published in this site. This database will contain 2 tables:
  • Table: article
    Fields: id, title, author, published, length, updated, notes
  • Table: body
    Fields: id, line_num, contents
In mSQL you will create the database and tables using the code:

% msqladmin create documents
% msql documents < schema.sql
in our case "schema.sql" will contain:

create table article (
  id        char(10) NOT NULL,    # unique ID 
  title        char(200) NOT NULL,    # title of the article
  author    char(200),        # one or more, comma separated list
  published    int NOT NULL,        # when was the article published
  length    int NOT NULL,        # how many lines (each of 120 char length)
  updated    int,            # when was it updated
  notes        text(80),        # notes for internal use
)\p\g

create unique index article_idx on article ( id )\p\g

create table body (
  id        char(10) NOT NULL,    # same as the article ID
  line_num    int,            # line number
  contents    char(120)        # a line of text from the article
)\p\g

create index body_idx on article ( id )\p\g
Of course the date fields could have been created using the DATE type, but using an INT type makes it easy for comparison using dates in the form: yyyymmdd, e.g. 19990415 for April 15, 1999. After setting up the database we populate it, and start having fun!

« Previous Page
1
|
2
|
3
|
4
|
5
|
6
|
7
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Jess Castagnetto

Comment:



Comment:

(Maximum characters: 1200). You have characters left.