displaying data from multiple tables

dduck1934

Hey guys,

Lets see if i can explain myself clearly.

I have three tables in a mySQL database I need to pull into a a php page.

Tables are :
courses, coursedates, and coursesections.

coursesections only lists the sections of courses that are available
for example section 1 could be Microsoft Word.

the courses database has a section id that relates to the section id in coursesections

same with the coursedates.

Hope you all are still with me.

Now I just need to display in a table all the data. I know i need to have two loop statements, and maybe two SQL statements at least, but I want to display the information like this in an html table.

so I need to have one loop that loops through the sections, then a second loop that loops through the courses and dates that are with that Section and displays it under each section.

There are only 4 dates for every course, so there doesnt have to be a loop for the dates, just a loop to display the right dates with the right course

Hope that is all clear as crystal. If not let me know what you dont understand.

Any help with this would be greatly appreciated.

Thanks,
dduck1934 😃

tomhath

I'd do it with a single query and handle the formatting in a php while loop.

select s.sectionname, c.coursename, cd.coursedate
from coursesections s
inner join courses c
on s. sectionid = c.sectionid
inner join coursedates cd
on c.courseid = cd.courseid

Then your in php flow is just something like this (at a very high level):

start with empty line buffer

while ( fetch a row)
if new coursename or sectionname {
print line buffer
set line buffer to course name
else
append date to line buffer
}
if new section {
print new section name
}
end while
print line buffer

Sxooter

Look into joining your tables if you can. It works something like this:

for instance, lets say your Courses table has a unique ID field, and your course_sections references that, and then your course dates table references that, you could get a listing by doing something like this:

select * from courses c join course_sections s on (c.id=s.cid)
join section_dates d on (s.id=d.sid);

That way the database does all the work and not you in getting all the data.

dduck1934

Thanks for your replies.

Sxooter I couldnt get your sql statement to work for me. sorry. Its my error Im sure.

tomhath, I am not sure I understand your coding example. I know it is just the idea of what the code should do, but I am new to php pretty much, so I need more help.

I used your sql statement tomhath which i modified it a little to the right tables etc. so the statement reads:

select s.sectionname, c.coursename
from coursesections s
inner join courses c on s. section = c.section
inner join coursedates cd
on c.courseID = cd.courseID

I just dont understand how to write the code from what you said.

Thanks,
dduck1934

kenpty

I will do something like this:

query1 = select * from sections

for each row in query1
section = sections.id
query2 = select * from courses, coursesdates where courses.x = coursesdates.x and courses.sec = section

         for each row in query2
                 echo course.name, coursesdates.date1 .....
        end 
 end

MytchE

That could take a decent amount of time if you're using a loop/... is there anyway you can setup an inner join or left outer join instead?