Halcyon Repose

Connect to MySQL from the command line:

msql -u *username* -p

Connect to the database (schema):

USE *database name*

Check to see what database you are currently set to:

SELECT DATABASE();

NOTES on SCFC Database

CREATE TABLE users (
ID INT PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Email varchar(50) NOT NULL,
Phone varchar(20),
Password varchar(20) NOT NULL,
CoachID INT Not NULL
);

CREATE TABLE Lesson_Slots(
ID int primary key,
InstructorID int,
StartTime datetime,
StudentID INT DEFAULT 0,
Weapon INT DEFAULT 0
);

insert into users Values (1, ‘Jaime’, ‘Wood’, ‘jaime@southcoastfencing.com’, ‘(714) 836-9505′,’temp’,0);

insert into Lesson_Slots values (1, 1, ’2005-10-21 19:00:00′, 1, 1);
insert into Lesson_Slots values (2, 1, ’2005-10-21 19:20:00′, 1, 1);
insert into Lesson_Slots values (3, 1, ’2005-10-22 19:00:00′, 1, 1);
insert into Lesson_Slots values (4, 1, ’2005-10-22 19:20:00′, 1, 1);

select date_format(StartTime,’%M %d, %Y – %a’) from Lesson_Slots;

PERL Code;
#Initialize
use DBI;

my $dsn = ‘DBI:mysql:southcoa_scfc:localhost’;
my $db_user_name = ‘southcoa_scfc’;
my $db_password = ‘RealFencing’;
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

# Query (using arguments
my $rs = $dbh->prepare(‘SELECT firstname, lastname from users
WHERE upper(firstname) = upper(?)
and upper(lastname) = upper(?)
and password = ?
and CoachID > -1′);

$rs->execute($in_fname, $in_lname, $in_code);
my ($sFirstName, $sLastName) = $rs->fetchrow_array();

$rs->finish();

# Number of rows returned
$rs->rows()
– Table to hold each individual class
DROP TABLE class_dates;
CREATE TABLE class_dates(
class_id integer(5) not null primary key auto_increment,
session_id integer(5) not null,
class_date date not null,
class_start time not null,
class_end time not null,
class_type integer(2) not null,
class_title varchar(100) not null,
order_id integer(2) not null,
deleted
);
ALTER TABLE class_dates ADD INDEX(session_id);

– Table to hold sessions (class groupings)
DROP TABLE class_session;
CREATE TABLE class_session(
session_id integer(5) not null primary key auto_increment,
session_title varchar(100) not null,
start_date date not null,
post_date date not null
);
ALTER TABLE class_session ADD INDEX(post_date);

– Table to hold email bodies
DROP TABLE mail_body;
CREATE TABLE mail_body(
Mail_ID integer(5) not null primary key auto_increment,
Mail_Type varchar(255),
Mail_Body text
);

– Table to hold all the class templates
DROP TABLE class_template;
CREATE TABLE class_template(
Template_ID integer(5) not null primary key auto_increment,
Class_Title varchar(100) not null,
Class_Type int(2) not null,
Class_Notes text,
Class_DayTime varchar(255),
Class_Length varchar(20),
Class_Cost varchar(10),
Class_Day int(1),
Class_Default int(1) default 0,
Deleted int(1) default 0
);
ALTER TABLE class_template ADD INDEX(Template_ID);

– Table to hold each individual who registers for class
DROP TABLE class_registration;
CREATE TABLE class_registration(
Registration_ID integer(5) not null primary key auto_increment,
Template_ID integer(5) not null,
Session_ID integer(5) not null,
Class_Date date not null,
Deleted int(1) default 0
);
ALTER TABLE class_registration ADD INDEX(Registration_ID);

– User Table
DROP TABLE scfc_users;
CREATE TABLE scfc_users(
User_ID integer(5) not null primary key auto_increment,
First_Name varchar(25),
Last_Name varchar(25),
Email varchar(50),
Address text,
Phone varchar(15),
Release binary(1),
Notes text,
Username varchar(25),
Password varchar(32)
);
ALTER TABLE scfc_users ADD INDEX(First_Name, Last_Name);
ALTER TABLE scfc_users ADD INDEX(Username);

– User Class Registration
DROP TABLE class_users;
CREATE TABLE class_users(
User_ID integer(5) not null,
Class_ID integer(5) not null,
Paid binary(1),
Comments text
);
ALTER TABLE class_users ADD INDEX (Class_ID);

– Instructor Table
DROP TABLE Instructor;
CREATE TABLE Instructor(
Instructor_ID integer(5) not null primary key auto_increment,
Instructor_Name varchar(30)
);

– Private Lesson Templates
DROP TABLE Lesson_Template;
CREATE TABLE Lesson_Template(
Template_ID integer(5) not null primary key auto_increment,
Instructor_ID integer(5) not null,
Time time not null,
Day integer(2) not null,
Student_ID integer(5) default NULL,
Status integer(2) default 0
);
ALTER TABLE Lesson_Template ADD INDEX (Instructor_ID);
ALTER TABLE Lesson_Template ADD INDEX (Day);

– Private Lesson Table
DROP TABLE Private_Lesson;
CREATE TABLE Private_Lesson(
Lesson_ID integer(5) not null primary key auto_increment,
Instructor_ID integer(5) not null,
Lesson_Date DateTime not null,
Student_ID integer(5),
Status integer(2) default 0
);
ALTER TABLE Private_Lesson ADD INDEX (Instructor_ID);
ALTER TABLE Private_Lesson ADD INDEX (Lesson_Date);

– For use on the web page, gets the next session that has not already passed
select min(post_date) from class_session where post_date > now();

SQL Scraps

select cd.class_id, ct.type_desc, date_format(cd.class_date, ‘%W’),
date_format(cd.class_date,’%M %D’), date_format(cd.class_start,’%l:%i’),
date_format(cd.class_end,’%l:%i %p’), cd.class_title
from class_dates cd, class_type ct
where cd.session_id = ?
order by ct.type_id, cd.order_id;

select min(session_id) from class_session
where start_date < CURDATE()
and post_date > CURDATE();

select session_id from class_session
where start_date = (SELECT max(start_date) from class_session);

(my $Class_ID, $Class_Type, $Class_Date, $Class_Start, $Class_end, $Class_Title) = $rs->fetchrow_array();

select date_format(class_end,’%l:%i %p’)
from class_dates;

– Class List
SELECT cr.Registration_ID, ct.Class_Title, ct.Class_Notes, ct.Class_DayTime,
cr.Class_Date, ct.Class_Length, ct.Class_Cost
FROM class_template ct, class_registration cr
WHERE cr.Session_ID = 14
AND cr.Template_ID = ct.Template_ID
AND cr.Deleted = 0;

DROP TABLE Whats_New;
CREATE TABLE Whats_New(
ID int not null primary key auto_increment,
PostDate datetime,
PostSubject varchar(255),
PostBody text,
Deleted int(1) default 0,
Visible int(1) default 1,
DisplayOrder int(1) default 0
);
ALTER TABLE Whats_New ADD INDEX(ID);
ALTER TABLE Whats_New ADD INDEX(PostDate);

DROP TABLE Image_Rotate;
CREATE TABLE Image_Rotate(
ID int not null primary key auto_increment,
Image_Location varchar(100),
Image_Comment varchar(75),
Display_Order int(2) default 0,
Date_Created timestamp
);
ALTER TABLE Image_Rotate ADD INDEX(ID);

–Payment Table
DROP TABLE Payment;
CREATE TABLE Payment(
ID int not null primary key auto_increment,
User_ID integer(5) not null,
Payment_Amt float(4,2),
Pay_Start datetime,
Pay_End datetime,
Payment_Type integer(1),
Payment_Note TEXT,
Payment_Currency integer(1),
Date_Entered datetime,
Entered_By integer(5)
);

ALTER TABLE Payment ADD INDEX(Pay_Start, Pay_End);
ALTER TABLE Payment ADD INDEX(User_ID);

– Credit Table
DROP TABLE Payment_Credit;
CREATE TABLE Payment_Credit(
ID int not null primary key auto_increment,
User_ID integer(5) not null,
Credit_Amt float(4,2),
Credit_Remain float(4,2),
Credit_Note TEXT,
Date_Entered datetime,
Entered_By integer(5)
);

ALTER TABLE Payment_Credit ADD INDEX(User_ID);

– Payment Types
DROP TABLE Payment_Type;
CREATE TABLE Payment_Type(
ID int not null primary key auto_increment,
Payment_Type varchar(25)
);

insert into Payment_Type values (1,’Dues’);
insert into Payment_Type values (2,’Class’);
insert into Payment_Type values (3,’Equipment’);

– Payment Types
DROP TABLE Payment_Currency;
CREATE TABLE Payment_Currency(
ID int not null primary key auto_increment,
Currency varchar(25)
);

insert into Payment_Currency values (1,’Cash’);
insert into Payment_Currency values (2,’Check’);
insert into Payment_Currency values (3,’Credit Card’);
insert into Payment_Currency values (4,’SCFC Credit’);

Links:

Tutorial
Date and Time Functions