SQL-help needed

[lavik]

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

[redemption]

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

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)?

[Mr Chocolate]

is that what your after?

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

[lavik]

Ok, what I want is something like this:

<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>

[lavik]

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

<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
Thanks!

[lavik]

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

[redemption]

Try using the IFNULL() function:

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

[lavik]

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:

$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

[lavik]

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

[redemption]

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

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'