...

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



neillglobal
02-25-2007, 08: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:
http://www.camtutor.net/problem.html

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:

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

guelphdad
02-25-2007, 04: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.

neillglobal
02-28-2007, 08: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

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

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

etc...

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

The PHP:


<?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("
SELECT *
FROM
(lessons INNER JOIN lnk_lessons_media ON lessons.lesson_ID = lnk_lessons_media.lesson_ID)
INNER JOIN
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!

Fumigator
02-28-2007, 04: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'];

}

neillglobal
02-28-2007, 05: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 :)

Fumigator
02-28-2007, 10: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).



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum