SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: SQL-help needed
-
Apr 27, 2003, 03:39 #1
- 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
-
Apr 27, 2003, 03:49 #2
- 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
-
Apr 27, 2003, 03:53 #3
- 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";
-
Apr 27, 2003, 04:43 #4
- 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>
-
Apr 27, 2003, 04:52 #5
- 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>
Thanks!
-
Apr 27, 2003, 05:01 #6
- 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?
-
Apr 27, 2003, 05:25 #7
- 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
-
Apr 27, 2003, 07:05 #8
- 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'";
Einar
-
Apr 27, 2003, 10:14 #9
- Join Date
- Dec 2002
- Posts
- 74
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Anyone care to have a look at it?
Cheers,
Einar
-
Apr 28, 2003, 05:45 #10
- 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