Left Join Query MySQL Database

[atlanteavila]

Man have I hit a pitfall on this one--I need help figuring out how to join three tables to display results in this format:

Obi One

downloaded adobe.pdf

downloaded apples.pdf

downloaded mymusic.mp3

Luke Skywalker - 555-5455

downloaded sample.pdf

downloaded apples.pdf

downloaded myband.mp3

So as you can see I have a users table which stores first and last name, and phone number, I've got another table where I store the available downloads identifiable by a unique ID and lastly I have a third table where I store the id of the user and the id of the item thy downloaded. But for the life of me I just don't understand how to join the tables and display each person in that particular format with the title and then the information they've downloaded.

any help in the right direction will be forever appreciated!!

Thanks a bunch!

Atlante

[Rabbit]

You join the third table to users table on the user id and then join the to the downloads table by the download id. No need for a left join.

[atlanteavila]

Thanks Rabbit--do you happen to have a tutorial where I can see and analyze a similar code? Thanks again!

[Rabbit]

Something similar would be this

SELECT a.FullName, b.DownloadName
FROM tblUsers AS a
   INNER JOIN tblUserDownloads AS c
   ON a.UserID = c.UserID
 
   INNER JOIN tblDownloads AS b
   ON b.DownloadID = c.DownloadID

[atlanteavila]

Okay, I've got this piece of php code here and finally got it to work! The only problem is this a single user can download multiple files, and when I execute the code below, it returns a list of the name of the user and what files he downloaded as expected, but I just want it to return the user's information once and the files he downloaded below it like this:

1. Full Name | 555-555-5555 | [e-mail]

• Archives.pdf
• Excel-Sheet.xls
• FileName.pdf

2. Jane Doe | 555-555-5555 | [e-mail]

• FileName.pdf

Here's the code any help is truly appreciated!!!

<?php 
$sql = "SELECT \n"
    . " usrid,\n"
    . " name,\n"
    . " phone,\n"
    . " email,\n"
    . " downloadid,\n"
    . " filename\n"
    . "FROM\n"
    . " downloaded d\n"
    . "INNER JOIN\n"
    . " download_manager dm\n"
    . "ON\n"
    . " (d.downloadid = dm.id)\n"
    . "INNER JOIN\n"
    . " tz_members tz\n"
    . "ON\n"
    . " (d.usrid = tz.id) ORDER BY `tz`.`name` ASC LIMIT 0, 30 ";
 
    $result = mysql_query($sql) or die(mysql_error());
 
    while($row = mysql_fetch_array($result)){
        echo "<div id=\"file-manager\"><p style=\"color: blue;\">" . $row['usrid'] . " - ".$row['name'] . " - " .$row['phone']  . " - " .$row['email'] . " - " .$row['filename']; ?>
<?php echo "</p></div>";} ?>

[Rabbit]

Just keep track of the name and print it only when it changes.

[atlanteavila]

Thanks again Rabbit, but unfortunately that's exactly what I don't know how to do, do you have a piece of code or a reference? Thanks a bunch!

[Rabbit]

Basically, in pseudocode, it would be

ClientName = ""
 
while(row = read a row)
   if row('ClientName') != ClientName
      print row('ClientName')
      print row('Download')[/
      ClientName = row('ClientName')
   else
      print row('Download')