[Resolved] [Resolved] I need help with many-2-many tables AND querying...

[kamy99]

Hi all:

I work for a private college and trying to organize our courses and programs.

We offer courses and programs. This creates a many-to-many situation, because each course can go into many programs and each program can have many courses.

To simplify; I have three table.

1) courses with fields:
course_id, course_name, course_level

2) Programs:
program_id, program_name

3) career_programs:
course_id, program_id

Everything is in PHP, mySQL.

I can retrieve data from the db, but I'm pretty sure I'm doing it wrongly!

Basically I write while loops within eachother!!
🙁 🙁 :bemused:

I know I probably should use an INNER join or something, but just can't figure it out....

My wonderfully written code looks like this: (I've removed the echo parts to make it smaller):

$sql=mysql_query("Select * from  career_programs where program_id=$myProgram");
   while($i=(mysql_fetch_array($sql)))
   {
	   $myCourse=$i['course_id'];
	   $getCourses=mysql_query("Select * from  courses where course_id=$myCourse");
	   while($j=(mysql_fetch_array($getCourses)))
	   {
	    echo 'test'; }   
   }

To wrap up, I need to list courses that are listed in each program ordered by course_level.

HEEEEEEEEELP!

Thanx in advance

[AstroTeg]

Inner (and outer) joins will be your friend when doing stuff like this.

Try this:

$query = 'SELECT * FROM career_programs INNER JOIN programs ON programs.program_id = career_programs.program_id INNER JOIN courses ON courses.course_id = career_programs.course_id WHERE career_programs.program_id = ' . $myProgram;

Untested but should work. I HIGHLY recommend checking some tutorials on using inner and left/outer joins. Making multiple query calls like you did takes a huge performance to the web and database servers and will cause poor performance in page loads. The fewer the queries you run against the database, the usually faster the page will load (unless you're running a terribly ugly looking query and in those cases, smaller queries are better - but this isn't the case here).

[kamy99]

Thank you AstroTeg for the wonderful solution, it worked!

I'm going to read up on JOINS asap.

Kamy