SQL-help needed

Hi!
I have som problems with outputting some data from a MySQL-db using PHP.

Let's say I have 2 tables. user and assignment(delivered assignments). What I need is to list out all the users, those who have delivered an assignment should have the filename listed next to their name.

users:
fields: userid, username, firstname, lastname

assginments:
fields: id, filename, userid*

I don't know if this is clear enough, but I'll give it a shot :)

Cheers,
Einar

List all users (only username) and, if they have delivered an assignment, the filename is listed.

Code:

---

SELECT u.username, a.filename
FROM user u
  LEFT JOIN assignment a ON u.userid=a.userid

---

EDIT: hang on - did you want to know the SQL, or how to display the results using PHP (or both)?

is that what your after?

PHP Code:

---

$sql = " SELECT userid, username, firstname, lastname, id, filename from users, assignments  WHERE users.userid=assignments.userid"; 


---

Ok, what I want is something like this:

PHP Code:

---

<table border="1">
 <tr>
  <td>username</td>
  <td>filename</td>
 </tr>
<?php
require_once 'dbconn.inc';
$sql = "SELECT user.username, assignment.filename
  FROM user, assignment
    LEFT JOIN assignment ON user.uid=assignment.uid";
 
if (!$result = mysql_query($sql)) die('Error: ' . mysql_error());
while ($row = mysql_fetch_array($result)) {
  echo '<tr>';
   echo '<td>';
    echo $_POST['username'];
   echo '</td>';
  
   echo '<td>';
    echo $_POST['filename'];  //here I want the filename if it exists, otherwise, leave empty
   echo '<td>';
 echo '</tr>';
}
?>
</table>

---

Sorry, I was a bit quick, $_POST should be $row.
More like this:

PHP Code:

---

<table border="1">
 <tr>
  <td>username</td>
  <td>filename</td>
 </tr>
<?php
require_once 'dbconn.inc';
$sql = "SELECT user.username, assignment.filename
         FROM user 
         LEFT JOIN assignment ON user.uid=assignment.uid";
 
if (!$result = mysql_query($sql)) die('Error: ' . mysql_error());
while ($row = mysql_fetch_array($result)) {
  echo '<tr>';
   echo '<td>';
    echo $row['username'];
   echo '</td>';
  
   echo '<td>';
    echo $row['filename'];
   echo '<td>';
  echo '</tr>';
}
?>
</table>

---

It seems to work :yawn:
Thanks!

But for the users without a file I want to display something ie. 'no file'.
How would I do that?

Try using the IFNULL() function:

Code:

---

SELECT user.username, IFNULL(assignment.filename, 'no file') AS filename
FROM user
  LEFT JOIN assignment ON user.uid=assignment.uid

---

Ok, I got it to work, but I'm having trouble using it in a more complex example.

I have 4 tables(actually there are more, but I think only three are og interest here):

user: uid, username, firstname, lastname
assignment: aid, filename, uid
subject: sid, subjectname
user_subject: uid, subject

I've tried something like this:

PHP Code:

---

$sql = "SELECT DISTINCT user.firstname, user.lastname, assignment.filename 
      FROM user 
      LEFT JOIN user_subject ON user.uid=user_subject.uid 
      LEFT JOIN assignement ON user_subject.uid=assignment.uid 
      WHERE user_subject.subjectname='english' 
      AND assignment.aid='1'"; 


---

But it won't work, it only lists one student with a filename :(

Einar

Anyone care to have a look at it?
Cheers,
Einar

How about this then? (not too sure this time though)

Code:

---

SELECT DISTINCT user.firstname, user.lastname, assignment.filename
FROM user
LEFT JOIN user_subject ON user.uid=user_subject.uid
      AND user_subject.subjectname='english'   
LEFT JOIN assignement ON user_subject.uid=assignment.uid
      AND assignment.aid='1'

---