Connect to MySQL from the command line:<\/p>\n
msql -u *username* -p<\/p>\n
Connect to the database (schema):<\/p>\n
USE *database name*<\/p>\n
Check to see what database you are currently set to:<\/p>\n
SELECT DATABASE();<\/p>\n
NOTES on SCFC Database<\/p>\n
CREATE TABLE users (
\nID INT PRIMARY KEY,
\nFirstName varchar(30) NOT NULL,
\nLastName varchar(30) NOT NULL,
\nEmail varchar(50) NOT NULL,
\nPhone varchar(20),
\nPassword varchar(20) NOT NULL,
\nCoachID INT Not NULL
\n);<\/p>\n
CREATE TABLE Lesson_Slots(
\nID int primary key,
\nInstructorID int,
\nStartTime datetime,
\nStudentID INT DEFAULT 0,
\nWeapon INT DEFAULT 0
\n);<\/p>\n
insert into users Values (1, ‘Jaime’, ‘Wood’, ‘jaime@southcoastfencing.com’, ‘(714) 836-9505′,’temp’,0);<\/p>\n
insert into Lesson_Slots values (1, 1, ‘2005-10-21 19:00:00’, 1, 1);
\ninsert into Lesson_Slots values (2, 1, ‘2005-10-21 19:20:00’, 1, 1);
\ninsert into Lesson_Slots values (3, 1, ‘2005-10-22 19:00:00’, 1, 1);
\ninsert into Lesson_Slots values (4, 1, ‘2005-10-22 19:20:00′, 1, 1);<\/p>\n
select date_format(StartTime,’%M %d, %Y – %a’) from Lesson_Slots;<\/p>\n
PERL Code;
\n#Initialize
\nuse DBI;<\/p>\n
my $dsn = ‘DBI:mysql:southcoa_scfc:localhost’;
\nmy $db_user_name = ‘southcoa_scfc’;
\nmy $db_password = ‘RealFencing’;
\nmy ($id, $password);
\nmy $dbh = DBI->connect($dsn, $db_user_name, $db_password);<\/p>\n
# Query (using arguments
\n my $rs = $dbh->prepare(‘SELECT firstname, lastname from users
\n WHERE upper(firstname) = upper(?)
\n and upper(lastname) = upper(?)
\n and password = ?
\n and CoachID > -1’);<\/p>\n
$rs->execute($in_fname, $in_lname, $in_code);
\n my ($sFirstName, $sLastName) = $rs->fetchrow_array();<\/p>\n
$rs->finish();<\/p>\n
# Number of rows returned
\n$rs->rows()
\n— Table to hold each individual class
\nDROP TABLE class_dates;
\nCREATE TABLE class_dates(
\nclass_id integer(5) not null primary key auto_increment,
\nsession_id integer(5) not null,
\nclass_date date not null,
\nclass_start time not null,
\nclass_end time not null,
\nclass_type integer(2) not null,
\nclass_title varchar(100) not null,
\norder_id integer(2) not null,
\ndeleted
\n);
\nALTER TABLE class_dates ADD INDEX(session_id);<\/p>\n
— Table to hold sessions (class groupings)
\nDROP TABLE class_session;
\nCREATE TABLE class_session(
\nsession_id integer(5) not null primary key auto_increment,
\nsession_title varchar(100) not null,
\nstart_date date not null,
\npost_date date not null
\n);
\nALTER TABLE class_session ADD INDEX(post_date);<\/p>\n
— Table to hold email bodies
\nDROP TABLE mail_body;
\nCREATE TABLE mail_body(
\nMail_ID integer(5) not null primary key auto_increment,
\nMail_Type varchar(255),
\nMail_Body text
\n);<\/p>\n
— Table to hold all the class templates
\nDROP TABLE class_template;
\nCREATE TABLE class_template(
\nTemplate_ID integer(5) not null primary key auto_increment,
\nClass_Title varchar(100) not null,
\nClass_Type int(2) not null,
\nClass_Notes text,
\nClass_DayTime varchar(255),
\nClass_Length varchar(20),
\nClass_Cost varchar(10),
\nClass_Day int(1),
\nClass_Default int(1) default 0,
\nDeleted int(1) default 0
\n);
\nALTER TABLE class_template ADD INDEX(Template_ID);<\/p>\n
— Table to hold each individual who registers for class
\nDROP TABLE class_registration;
\nCREATE TABLE class_registration(
\nRegistration_ID integer(5) not null primary key auto_increment,
\nTemplate_ID integer(5) not null,
\nSession_ID integer(5) not null,
\nClass_Date date not null,
\nDeleted int(1) default 0
\n);
\nALTER TABLE class_registration ADD INDEX(Registration_ID);<\/p>\n
— User Table
\nDROP TABLE scfc_users;
\nCREATE TABLE scfc_users(
\nUser_ID integer(5) not null primary key auto_increment,
\nFirst_Name varchar(25),
\nLast_Name varchar(25),
\nEmail varchar(50),
\nAddress text,
\nPhone varchar(15),
\nRelease binary(1),
\nNotes text,
\nUsername varchar(25),
\nPassword varchar(32)
\n);
\nALTER TABLE scfc_users ADD INDEX(First_Name, Last_Name);
\nALTER TABLE scfc_users ADD INDEX(Username);<\/p>\n
— User Class Registration
\nDROP TABLE class_users;
\nCREATE TABLE class_users(
\nUser_ID integer(5) not null,
\nClass_ID integer(5) not null,
\nPaid binary(1),
\nComments text
\n);
\nALTER TABLE class_users ADD INDEX (Class_ID);<\/p>\n
— Instructor Table
\nDROP TABLE Instructor;
\nCREATE TABLE Instructor(
\nInstructor_ID integer(5) not null primary key auto_increment,
\nInstructor_Name varchar(30)
\n);<\/p>\n
— Private Lesson Templates
\nDROP TABLE Lesson_Template;
\nCREATE TABLE Lesson_Template(
\nTemplate_ID integer(5) not null primary key auto_increment,
\nInstructor_ID integer(5) not null,
\nTime time not null,
\nDay integer(2) not null,
\nStudent_ID integer(5) default NULL,
\nStatus integer(2) default 0
\n);
\nALTER TABLE Lesson_Template ADD INDEX (Instructor_ID);
\nALTER TABLE Lesson_Template ADD INDEX (Day);<\/p>\n
— Private Lesson Table
\nDROP TABLE Private_Lesson;
\nCREATE TABLE Private_Lesson(
\nLesson_ID integer(5) not null primary key auto_increment,
\nInstructor_ID integer(5) not null,
\nLesson_Date DateTime not null,
\nStudent_ID integer(5),
\nStatus integer(2) default 0
\n);
\nALTER TABLE Private_Lesson ADD INDEX (Instructor_ID);
\nALTER TABLE Private_Lesson ADD INDEX (Lesson_Date);<\/p>\n
— For use on the web page, gets the next session that has not already passed
\nselect min(post_date) from class_session where post_date > now();<\/p>\n
SQL Scraps<\/p>\n
select cd.class_id, ct.type_desc, date_format(cd.class_date, ‘%W’),
\n date_format(cd.class_date,’%M %D’), date_format(cd.class_start,’%l:%i’),
\n date_format(cd.class_end,’%l:%i %p’), cd.class_title
\nfrom class_dates cd, class_type ct
\nwhere cd.session_id = ?
\norder by ct.type_id, cd.order_id;<\/p>\n
select min(session_id) from class_session
\nwhere start_date < CURDATE()\n and post_date > CURDATE();<\/p>\n
select session_id from class_session
\nwhere start_date = (SELECT max(start_date) from class_session);<\/p>\n
(my $Class_ID, $Class_Type, $Class_Date, $Class_Start, $Class_end, $Class_Title) = $rs->fetchrow_array();<\/p>\n
select date_format(class_end,’%l:%i %p’)
\nfrom class_dates;<\/p>\n
— Class List
\nSELECT cr.Registration_ID, ct.Class_Title, ct.Class_Notes, ct.Class_DayTime,
\n cr.Class_Date, ct.Class_Length, ct.Class_Cost
\n FROM class_template ct, class_registration cr
\n WHERE cr.Session_ID = 14
\n AND cr.Template_ID = ct.Template_ID
\n AND cr.Deleted = 0;<\/p>\n
DROP TABLE Whats_New;
\nCREATE TABLE Whats_New(
\nID int not null primary key auto_increment,
\nPostDate datetime,
\nPostSubject varchar(255),
\nPostBody text,
\nDeleted int(1) default 0,
\nVisible int(1) default 1,
\nDisplayOrder int(1) default 0
\n);
\nALTER TABLE Whats_New ADD INDEX(ID);
\nALTER TABLE Whats_New ADD INDEX(PostDate);<\/p>\n
DROP TABLE Image_Rotate;
\nCREATE TABLE Image_Rotate(
\nID int not null primary key auto_increment,
\nImage_Location varchar(100),
\nImage_Comment varchar(75),
\nDisplay_Order int(2) default 0,
\nDate_Created timestamp
\n);
\nALTER TABLE Image_Rotate ADD INDEX(ID);<\/p>\n
–Payment Table
\nDROP TABLE Payment;
\nCREATE TABLE Payment(
\nID int not null primary key auto_increment,
\nUser_ID integer(5) not null,
\nPayment_Amt float(4,2),
\nPay_Start datetime,
\nPay_End datetime,
\nPayment_Type integer(1),
\nPayment_Note TEXT,
\nPayment_Currency integer(1),
\nDate_Entered datetime,
\nEntered_By integer(5)
\n);<\/p>\n
ALTER TABLE Payment ADD INDEX(Pay_Start, Pay_End);
\nALTER TABLE Payment ADD INDEX(User_ID);<\/p>\n
— Credit Table
\nDROP TABLE Payment_Credit;
\nCREATE TABLE Payment_Credit(
\nID int not null primary key auto_increment,
\nUser_ID integer(5) not null,
\nCredit_Amt float(4,2),
\nCredit_Remain float(4,2),
\nCredit_Note TEXT,
\nDate_Entered datetime,
\nEntered_By integer(5)
\n);<\/p>\n
ALTER TABLE Payment_Credit ADD INDEX(User_ID);<\/p>\n
— Payment Types
\nDROP TABLE Payment_Type;
\nCREATE TABLE Payment_Type(
\nID int not null primary key auto_increment,
\nPayment_Type varchar(25)
\n);<\/p>\n
insert into Payment_Type values (1,’Dues’);
\ninsert into Payment_Type values (2,’Class’);
\ninsert into Payment_Type values (3,’Equipment’);<\/p>\n
— Payment Types
\nDROP TABLE Payment_Currency;
\nCREATE TABLE Payment_Currency(
\nID int not null primary key auto_increment,
\nCurrency varchar(25)
\n);<\/p>\n
insert into Payment_Currency values (1,’Cash’);
\ninsert into Payment_Currency values (2,’Check’);
\ninsert into Payment_Currency values (3,’Credit Card’);
\ninsert into Payment_Currency values (4,’SCFC Credit’);<\/p>\n
Links:<\/p>\n
Tutorial<\/a> 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, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-general"],"_links":{"self":[{"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15"}],"version-history":[{"count":0,"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=\/wp\/v2\/posts\/15\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.halcyonrepose.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nDate and Time Functions <\/a><\/p>\n","protected":false},"excerpt":{"rendered":"