PHP Slect with MYSQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jquickuk
Forum Newbie
Posts: 3
Joined: Tue May 06, 2008 2:34 pm

PHP Slect with MYSQL

Post 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
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP Slect with MYSQL

Post 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 :D
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP Slect with MYSQL

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply