By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Left Join Query MySQL Database

P: 19
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
Oct 8 '11 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,347
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.
Oct 9 '11 #2

P: 19
Thanks Rabbit--do you happen to have a tutorial where I can see and analyze a similar code? Thanks again!
Oct 9 '11 #3

Rabbit
Expert Mod 10K+
P: 12,347
Something similar would be this
Expand|Select|Wrap|Line Numbers
  1. SELECT a.FullName, b.DownloadName
  2. FROM tblUsers AS a
  3.    INNER JOIN tblUserDownloads AS c
  4.    ON a.UserID = c.UserID
  5.  
  6.    INNER JOIN tblDownloads AS b
  7.    ON b.DownloadID = c.DownloadID
Oct 9 '11 #4

P: 19
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 | sampleemail@domainname.com
  • Archives.pdf
  • Excel-Sheet.xls
  • FileName.pdf

2. Jane Doe | 555-555-5555 | sampleemail@domainname.com
  • FileName.pdf

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

Expand|Select|Wrap|Line Numbers
  1. <?php 
  2. $sql = "SELECT \n"
  3.     . " usrid,\n"
  4.     . " name,\n"
  5.     . " phone,\n"
  6.     . " email,\n"
  7.     . " downloadid,\n"
  8.     . " filename\n"
  9.     . "FROM\n"
  10.     . " downloaded d\n"
  11.     . "INNER JOIN\n"
  12.     . " download_manager dm\n"
  13.     . "ON\n"
  14.     . " (d.downloadid = dm.id)\n"
  15.     . "INNER JOIN\n"
  16.     . " tz_members tz\n"
  17.     . "ON\n"
  18.     . " (d.usrid = tz.id) ORDER BY `tz`.`name` ASC LIMIT 0, 30 ";
  19.  
  20.     $result = mysql_query($sql) or die(mysql_error());
  21.  
  22.     while($row = mysql_fetch_array($result)){
  23.         echo "<div id=\"file-manager\"><p style=\"color: blue;\">" . $row['usrid'] . " - ".$row['name'] . " - " .$row['phone']  . " - " .$row['email'] . " - " .$row['filename']; ?>
  24. <?php echo "</p></div>";} ?>
Oct 11 '11 #5

Rabbit
Expert Mod 10K+
P: 12,347
Just keep track of the name and print it only when it changes.
Oct 11 '11 #6

P: 19
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!
Oct 11 '11 #7

Rabbit
Expert Mod 10K+
P: 12,347
Basically, in pseudocode, it would be
Expand|Select|Wrap|Line Numbers
  1. ClientName = ""
  2.  
  3. while(row = read a row)
  4.    if row('ClientName') != ClientName
  5.       print row('ClientName')
  6.       print row('Download')[/
  7.       ClientName = row('ClientName')
  8.    else
  9.       print row('Download')
Oct 11 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.