Hi,
I've been attempting to create a mysql query which will show the following information from 2 tables.
I'm searching for strings contained within the mdl_lesson_pages 'contents' field and outputting which lessons these strings are located & the page within each lesson where the strings are located.
So I want to display when search for particular strings the following info:-
The onpage lesson id (when moused over from the main course page).
The lessonpage id (when inside a lesson)
The lesson title (of the lesson)
The previous page id (when inside a lesson)
The next page id (when inside a lesson)
Tables & Fields
The mdl_lesson_pages (id),
mdl_course_module (instance),
mdl_lesson_pages (lessonid),
mdl_lesson_pages (title),
mdl_course_module (course),
mdl_course_module (prevpageid),
mdl_course_module (nextpageid)
So far I have I can only get this to display all information except for the ID of the mdl_lesson_pages table. This is due to the fact id are both used in the two tables and I need to output the mdl_lesson_pages id and not the mdl_course_modules id but I can't.
So far I have this command :
SELECT instance, lessonid, title, course, prevpageid, nextpageid |
FROM mdl_course_modules |
JOIN mdl_lesson_pages |
ON mdl_course_modules.instance = mdl_lesson_pages.lessonid |
WHERE (`contents` LIKE '%/AsbAware_files/%') |
ORDER BY course |
LIMIT 0 , 300 |
this outputs the following :
instance | lessonid | title | course | prevpageid | nextpageid |
1374 | 1374 | 1.3 The three main types of asbestos | 60 | 60521 | 60523 |
1375 | 1375 | 2.1.2.4 The Asbestos legacy IS a Major Problem! | 60 | 60526 | 60528 |
1375 | 1375 | 2.2 Medical Examinations | 60 | 60527 | 60529 |
1375 | 1375 | 2.4 What are the risks? | 60 | 60529 | 60531 |
1376 | 1376 | 3.1 examples of asbestos in buildings | 60 | 60532 | 60534 |
1376 | 1376 | 3.2 Examples of Asbestos-Containing Materials | 60 | 60533 | 60535 |
1377 | 1377 | 4.0 What to do if you uncover Asbestos | 60 | 0 | 60537 |
1377 | 1377 | 4.2 Example Asbestos Management Policy (Cont.) | 60 | 60537 | 60539 |
1378 | 1378 | 5.4 Preventing or reducing exposure (regulation 11... | 60 | 60543 | 60545 |
1378 | 1378 | 6.1 Supporting Guidance | 60 | 60546 | 60548 |
1378 | 1378 | 6.2 Further Examples of Asbestos (Cont.) | 60 | 60547 | 60549 |
1378 | 1378 | 6.2 Further Examples of Asbestos (Cont.) | 60 | 60548 | 60550 |
1378 | 1378 | 6.2 Further Examples of Asbestos (Cont.) | 60 | 60549 | 60551 |
1378 | 1378 | 6.2 Further Examples of Asbestos (Cont.) | 60 | 60550 | 60552 |
1378 | 1378 | 6.2 Further Examples of Asbestos (Cont.) | 60 | 60551 | 60553 |
But as you can see this doesn't output the ID from the mdl_lesson_pages table.
I can't quite work out how to output the ID field (from either table as they are ambiguous)
I was hoping some kind sole would help me out here, this is driving me barmy! I have hundreds of pages where I need to locate certain strings, to do this manually is a knightmare with the lesson module as the ID's don't corresponde.
The lessonid & instance are the matching fields from the two tables.
Much appreciated for any assistance!!
P.S. Why the lesson module built this way I will never know, doesn't make much sense to me...
Steve
SELECT instance, lessonid, title, course, prevpageid, nextpageid |
FROM mdl_course_modules |
JOIN mdl_lesson_pages |
ON mdl_course_modules.instance = mdl_lesson_pages.lessonid |
WHERE (`contents` LIKE '%http://www.sheilds.co.uk/NCC/%') |
ORDER BY course |
LIMIT 0 , 300 |