View Full Version : How To: Get data out of related tables and output it nicely?

02-25-2007, 09:22 AM
Hey Guys, What a top forum! I just started getting into PHP a week ago, I saw the light, stopped my ASP progress and gave in to PHP. :thumbsup:

So I really need help now with an issue, I have done my utmost to describe and show you guys the issue in as much relevant detail as possible here:

Please help me out I have spent a day scouring the web, w3school.com, www.php-mysql-tutorial.com, deeptraining.com and lots of other but I just can't put it together and my head hurts. :confused:

02-25-2007, 01:03 PM
explain your problem here, half the text is missing when I look at that page.

02-25-2007, 05:25 PM
If your issue is displaying the data then this thread belongs in the php forum. Post mysql query related problems in the mysql forum please.

02-28-2007, 09:38 AM
Okay thanks for the responses guys. I'll try to explain the problem again.

So what I'm trying to do is get it to output the lessons and their corresponding media displayed something like the below

- media_ID(corresponding mapped in lnk_lessons_media table)
- media_ID(corresponding mapped in lnk_lessons_media table)

- media_ID(corresponding mapped in lnk_lessons_media table)
- media_ID(corresponding mapped in lnk_lessons_media table)


Tables are here:
http://www.camtutor.net/dev/dbs.jpg (http://www.camtutor.net/dev/dbs.jpg)

The PHP:

$con = mysql_connect("localhost","xxxxxx","xxxxxxx"); //mysql_connect(servername,username,password);
if (!$con)
die('Could not connect: ' . mysql_error());

mysql_select_db("tables", $con)
or die("Error Could not select database");

$result_lessons = mysql_query("
(lessons INNER JOIN lnk_lessons_media ON lessons.lesson_ID = lnk_lessons_media.lesson_ID)
media ON lnk_lessons_media.media_ID=media.media_ID

while($row = mysql_fetch_array($result_lessons))

echo "{$row['lesson_ID']} | {$row['lesson_title']} | {$row['media_ID']} | {$row['media_title']} <br />";


mysql_close($con); //closes the database connection


so far the only output I can achieve is this:

1 | Ni Hao | 1 | Ni Hao Flash Lesson
1 | Ni Hao | 2 | Teacher goes crazy Flash Lesson
2 | Eating with your Teeth | 3 | 1st Media for lesson 2
2 | Eating with your Teeth | 4 | 2nd Media for Lesson 2

Not what I'm wanting.... Thanks guys! Its baking my little noodle!

02-28-2007, 05:37 PM
Looks like you've got the data you need, you just need to use some looping fun to display it right. I'd probably go old-school like this:

$previousLessonID = '';
while($row = mysql_fetch_array($result_lessons))

if ($row['lesson_ID'] != $previousLessonID) {
echo "Lesson {$row['lesson_ID']}: {$row['lesson_title']}<br />\n";
echo "{$row['media_ID']} | {$row['media_title']} <br />\n";
$previousLessonID = $row['lesson_ID'];


02-28-2007, 06:39 PM
Thanks Fumigator!!!!

That worked like a charm!!!!!! Now I'm back on track! Legendary!

Hey if that's old-school... whats new-school? Thats a serious question there :)

02-28-2007, 11:47 PM
Hehe... I'm sure some young wizkid has a new way of what we used to call "control breaks", and will be quick to point out how horrible that old way is. :D It's the cycle of life, new technology poo-poos old technology (even though the old way may work just fine).