Page 1 of 1
PHP Slect with MYSQL
Posted: Tue May 13, 2008 8:24 am
by jquickuk
Hello All
I have a final problem withmy application. I am doing a module definition document database for a university project. Modules are linked with many courses.
I know that using a SELECT box will work but how do I:
1) The options in the select box be from a table of a MYSQL databse.
2) store many courses (values) in one MYSQL field.
Thanks in advance
John
Re: PHP Slect with MYSQL
Posted: Tue May 13, 2008 9:40 am
by aceconcepts
Ok, so a relational database would be the best I approach me thinks.
You will need 3 base tables:
1. tblModule (intModuleId, strModuleTitle)
2. tblCourse (intCourseId, strCourseTitle)
3. tblModuleCourse (intModuleId, intCourseId)
In table 3 (tblModuleCourse) you will store intModuleId and intCourseId. This means that you can "relate" modules to courses.
When you come to display the modules and their courses you will have to use an SQL JOIN:
Code: Select all
$sql=("SELECT * FROM tblCourse
INNER JOIN tblModuleCourse ON tblCourse.intCourseId=tblModuleCourse.intCourseId
INNER JOIN tblModule ON tblModuleCourse.intModuleId=tblModule.intModuleId
GROUP BY tblCourse.strCourseTitle");
This is one (rather crude) way to do it.
There are better ways to do it but this should get you started.
Hope it helps

Re: PHP Slect with MYSQL
Posted: Tue May 13, 2008 9:54 am
by pickle
1) Run a query on your table, pull out the relevant data, & put it in an array. Then, in your display logic, iterate through that array & build an <option> element for each row.
2) Don't. If feel you need to store multiple values in one field for one row, then you either need to turn that one row into many, or make a new table with multiple rows. Essentially, one field in one row should hold one value.