Creating new table in databse everyday

[feilhk]

Hello, i am working a flash project on attendance , when i submit the attendance, i want to create a new table for everyday.
the table should be based on the module course and the date
But i got problem of it, it doesnt create new table when i submit the attendance. Coudl somebody help me?/ thz

i declare
//today's date
$date=date("d"."/"."m".","."y");
//the new table, is it correct??
$newtable=$_POST['moduleName'].$_POST['courseName'].$date;
There is part of the program


//today's date
$date=date("d"."/"."m".","."y");
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["RC_insert"])) && ($_POST["RC_insert"] == "form1")) {
//the new table, is it correct??
$newtable=$_POST['moduleName'].$_POST['courseName'].$date;
$createSQL = sprintf("Create TABLE $newtable (studentNO varchar(9),time varchar(40),moduleName varchar(255),courseName varchar(255));");
$insertSQL = sprintf("INSERT INTO record (studentNO, time, moduleName, courseName) VALUES (%s, %s, %s, %s)",
GetSQLValueString($_POST['studentNO'], "text"),
GetSQLValueString($_POST['Time'], "text"),
GetSQLValueString($_POST['moduleName'], "text"),
GetSQLValueString($_POST['courseName'], "text"));
mysql_select_db($database_flash_agent, $flash_agent);
$Result1 = mysql_query($createSQL, $flash_agent) or die(mysql_error());
$Result1 = mysql_query($insertSQL, $flash_agent) or die(mysql_error());
}

[bwh2]

nonononononono. creating a new table for everyday is a really bad idea. you should probably do something like:


tbl_students
-------------------
student_id (PK)
student_name_first
student_name_last

tbl_courses
-------------------
course_id (PK)
course_name
course_dscpn
course_teacher_id (FK)

tbl_courses_days
-------------------
course_id (FK)
day_id (FK)
time_start
time_end

tbl_days
-------------------
day_id (PK)
day_name

tbl_students_courses
-------------------
student_id (FK)
course_id (FK)
semester_id (FK)
status_id (FK)

tbl_students_courses_status
-------------------
status_id (PK)
status_name

tbl_teachers
-------------------
teacher_id (PK)
teacher_name_first
teacher_name_last

tbl_attendance
-------------------
attendance_id (PK)
student_id (FK)
course_id (FK)
date
attendance_status_id (FK)

tbl_attendance_status
-------------------
attendance_status_id (FK)
attendance_status_name

that's just a quick example. i don't know what a module is, so i didn't include that. regardless, the last thing you should do is make a table for every day.

[SlowRoasted]

Im with bwh2 on this one. Relational databases are much better. It will make you life a lot easier when trying to make use of your data.

[Seb Hughes]

Im with bwh2 on this one. Relational databases are much better. It will make you life a lot easier when trying to make use of your data.

Yea, When bwh2 introduced me to RD i was like OMg I cant believe I have never done this before.:hugegrin:

[skrolikowski]

Creating a new table everyday... Your computer would explode!!!

[feilhk]

nonononononono. creating a new table for everyday is a really bad idea. you should probably do something like:


tbl_students
-------------------
student_id
student_name_first
student_name_last

tbl_courses
-------------------
course_id
course_name
course_dscpn
course_teacher_id

tbl_courses_days
-------------------
course_id
day_id
time_start
time_end

tbl_days
-------------------
day_id
day_name

tbl_students_courses
-------------------
student_id
course_id
semester_id
status_id

tbl_students_courses_status
-------------------
status_id
status_name

tbl_teachers
-------------------
teacher_id
teacher_name_first
teacher_name_last

tbl_attendance
-------------------
attendance_id
student_id
course_id
date
attendance_status_id

tbl_attendance_status
-------------------
attendance_status_id
attendance_status_name

that's just a quick example. i don't know what a module is, so i didn't include that. regardless, the last thing you should do is make a table for every day.


thz anyways, umm i dont know much about relational database... coudl u explain a bit more teh code above, btw, module in my project stands for subject, java, c++ etc. thz

[feilhk]

is it create tbl_students and add these keys?

student_id
student_name_first
student_name_last

[bwh2]

those are the fields (aka columns). of those, the primary key would be student_id would be the primary key. i have updated my original post to show which columns are designated as primary and foreign keys.

read the Relational Database Design tutorial (http://kirupa.com/developer/php/relational_db_design.htm) to help get you started with a relational design.

[feilhk]

thank you very much!

[bwh2]

no problem. post here if you have any questions about how you should design this.

[feilhk]

deleted