Displaying results from mutliple tables

danielhalawi

Hey,

I have five tables

courses

id, coursename, coursedes, coursesize
courses_dates
id, courseid, coursedate, location
staff
id, firstname, middlename, lastname, storenumber
store
id, storenumber, storename
course_attendance
id, staffid, courseid

I have shown above which row relates to each table.

The page url would be mysite.com/page.php?id=1
Where id refers to the id on the course_attendance table.

How do I display all the information from the other tables on the same page....?

Thanks for all help in advance.

Piranha

I guess that you need help with how the query should look like. Something like this:

SELECT course_attendance.id, courses.name, courses.des.courses.size,courses_dates.date, staff.fname, store.no // And the other columns in the tables as well
FROM course_attendance
INNER JOIN courses ON courses.id = course_attendance.courseid
LEFT JOIN courses_dates ON courses.id = courses_dates.courseid
// And the other two tables are joined the same way as INNER JOIN
WHERE courses_attendance.id = '$php_variable'
ORDER BY course_attendance.id

The only problem you will get into is that the courses can have lots of dates for each course and that all the dates will be returned as different rows. To solve that order by course_attendance.id, then for each result you check the id and if it is the same as the previous then you only handle the date, otherwise you handle everything.

danielhalawi

SELECT * FROM  benugo_courses.id, benugo_courses.coursename, benugo_courses.coursesize, benugo_courses_dates.id, benugo_courses_dates.courseid,, benugo_courses_dates.location, benugo_staff.id, benugo_staff.firstname, benugo_staff.lastname, benugo_staff.storenumber, benugo_store.id, benugo_store.storenumber, benugo_store.storename, benugo_course_attendance.id, benugo_course_attendance.staffid, benugo_course_attendance.courseid  // And the other columns in the tables as well 
FROM benugo_course_attendance 
INNER JOIN benugo_courses ON benugo_courses.id = benugo_course_attendance.courseid
INNER JOIN benugo_staff ON benugo_staff.id = benugo_course_attendance.staffid
INNER JOIN benugo_courses_dates ON benugo_courses_dates.location = store.id   

LEFT JOIN benugo_courses_dates ON benugo_courses_dates.id = benugo_courses_dates.courseid 
// And the other two tables are joined the same way as INNER JOIN 
WHERE benugo_courses_attendance.id = '$php_variable' 
ORDER BY benugo_course_attendance.id

Ok managed to do that... but how can I display the information.

The way I want it to come out is

Course Details:
courses.coursename
courses.coursedate
store.storename
Attendee Name:
staff.firstname staff.lastname

Nothing I seem to be doing is coming out I keep getting parse errors

Piranha

I thought that you understood basic PHP, SQL and HTML since your signature says that you are a designer for those languages. If you don't understand it I suggest that you do a few tutorials, as an example this tutorial.

Edit: You can try to search both this board (a tip: look in database forum) for this basic things, it is shown in about 1 thread out of 10.

danielhalawi

Thank you for that direction... I've now figured out that there was a problem with the select... can someone please have a look at the following....

SELECT benugo_courses.id, benugo_courses.coursename, benugo_courses.coursedes, benugo_courses.coursesize, benugo_courses_dates.id, benugo_courses_dates.courseid, benugo_courses_dates.coursedate, benugo_courses_dates.location, benugo_staff.id, benugo_staff.firstname, benugo_staff.lastname, benugo_staff.startdate, benugo_store.id, benugo_store.storenumber, benugo_store.type, benugo_courses_attendance.id, benugo_courses_attendance.staffid, benugo_courses_attendance.courseid
FROM benugo_courses, benugo_courses_attendance, benugo_courses_dates, benugo_staff, benugo_store
LEFT JOIN benugo_courses_attendance ON benugo_courses.id = benugo_courses_attendance.courseid
ORDER BY benugo_courses_attendance.id

I'm guessing the problem is with the JOIN, but I can't manage to figure it out....
Any help much appriceated.

#1066 - Not unique table/alias: 'benugo_courses_attendance'

Thank You

Piranha

You can only add a table once, the way you code you try to do it twice:

FROM benugo_courses, benugo_courses_attendance, benugo_courses_dates, benugo_staff, benugo_store
LEFT JOIN benugo_courses_attendance ON benugo_courses.id = benugo_courses_attendance.courseid

You need to make the relations in the query as well, otherwise the database will try to join every row in each table with every row in every other table.

danielhalawi

I keep getting this error.

#1066 - Not unique table/alias: 'benugo_courses_attendance'