SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: SQL-help needed

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)?

  3. #3
    SitePoint Zealot Mr Chocolate's Avatar
    Join Date
    May 2002
    Location
    Australia
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is that what your after?
    PHP Code:
    $sql " SELECT userid, username, firstname, lastname, id, filename from users, assignments  WHERE users.userid=assignments.userid"

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Thanks!

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But for the users without a file I want to display something ie. 'no file'.
    How would I do that?

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone care to have a look at it?
    Cheers,
    Einar

  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •