I am trying to write a simple Web App for guitar teachers using PHP, mySQL, and EXTjs -
i working on project school/myself. project description...
"i thinking of starting guitar teaching business , create web app enable me keep track of students, lesson schedule, musical interests, payment history, age, e-mail address, , phone number. able create record of information went on in lesson, along list of skills student has or has developed on time. when student makes monthly payment able click process payment , e-mail copy of invoice student, , myself. upkeep of student records use edit button update information after each lesson."
we have use extjs our presentation layer, php out logic layer, , mysql our database.
i feel pretty confident in being able populate grids , layout of page. i worried being able make sure can generate date student supposed have next lesson (example, today april 8th, students next class april 15th, 22nd etc.)
i haven't started php code yet, wondering if point me in correct direction because have no idea how want based of off date student began.
my database code given below.
use mydatabase; drop table teacher; drop table student; drop table payment; drop table skills; drop table teachertraininglog; drop table studenttraininglog; create table teacher (teacherid int, teacherlast varchar(30), teacherfirst varchar(30), teacheremail varchar(254), teacherdob date, teacheraddress varchar(50), teachercity varchar(30), teacherstate char(2), teacherzip varchar(10), teacherphone varchar(12), teacheruserid varchar(50), teacherpassword varchar(15)); alter table teacher add constraint teacherpk primary key (teacherid); create table student (studentid int, studentlast varchar(30), studentfirst varchar(30), studentemail varchar(254), datestarted date, studentdob date, musicinterest text, studentaddress varchar(50), studentcity varchar(30), studentstate char(2), studentzip varchar(10), studentphone varchar(12), studentuserid varchar(50), studentpassword varchar(15), teacherid int); alter table student add constraint studentpk primary key (studentid); alter table student add constraint teacherfk foreign key (teacherid) references teacher(teacherid); create table payment (paymentid int, paymentduedate date, paymentamount int, teacherid int, studentid int); alter table payment add constraint paymentpk primary key (paymentid); alter table payment add constraint teacherfk foreign key (teacherid) references teacher(teacherid); alter table payment add constraint studentfk foreign key (studentid) references student(studentid); create table skills (skillid int, skillname varchar(30)); alter table skills add constraint skillpk primary key (skillid); create table teachertraininglog (teacherid int, skillid int, teacherdatestarted date, teacherlevel int, teachertrainingnotes text); alter table teachertraininglog add constraint teachidfk foreign key (teacherid) references teacher (teacherid); alter table teachertraininglog add constraint skillidfk foreign key (skillid) references skills (skillid); alter table teachertraininglog add constraint teachertrainginglogpk primary key (teacherid, skillid); create table studenttraininglog (studentid int, skillid int, studentdatestarted date, studentlevel int, studenttrainingnotes text); alter table studenttraininglog add constraint studentidfk foreign key (studentid) references student (studentid); alter table studenttraininglog add constraint skillidfk foreign key (skillid) references skills (skillid); alter table studenttraininglog add constraint studenttrainginglogpk primary key (studentid, skillid); insert teacher values (1, 'bob', 'joe','teacher.troxy@gmail.com','1988/5/4', '1120 sweet st.', 'boulder', 'co', '55555', '555-555-5555','capn', 'crunch'); insert student values (1, 'joey', 'bobby','student.troxy@gmail.com','2013/4/8', '1988/9/12', 'led zepplin, taylor swift', '2120 garbage can ln', 'boulder', 'co', '55555', '333-333-333','student1', 'first', 1); insert skills values (1, 'chord'); insert skills values (2, 'strumming'); insert skills values (3, 'alternate picking'); insert skills values (4, 'bend'); insert skills values (5, 'bend/release'); insert skills values (6, 'hammer-on'); insert skills values (7, 'pull-off'); insert skills values (8, 'slide'); insert skills values (9, 'vibrato'); insert skills values (10, 'tapping'); insert skills values (11, 'natural harmonic'); insert skills values (12, 'pinch harmonic'); insert skills values (13, 'sweep picking'); insert skills values (14, 'finger picking');
thanks can give me. hope not vague. tried explain know how.
assuming lesson weekly starting date, in mysql next lesson starting date:
select date_add(datestarted, interval 7 day) `studentid` studentid = ?;
but want given student know when next lesson is. won't dependent on start date, , lesson days/times change people's schedules, recommend having db table lesson date/time tied studentid. populate table using form select student, enter date range , date & time of lessons, hit go, , php script enter lesson dates table. ...or enter them hand.
Comments
Post a Comment