Grouping by Name

Anon

Hi,
I've got the following two tables in my db.

Users
+----+----------+
| id | username |
+----+----------+
| 1 | Gill |
| 2 | Bob |
+----+----------+

User Logins
+----+---------+-------------+-------------+
| id | user_id | ip | time |
+----+---------+-------------+-------------+
| 1 | 1 | 127.0.0.1 | 200103161050|
| 1 | 2 | 127.0.0.1 | 200103161050|
| 1 | 2 | 127.0.0.1 | 200103161050|
| 1 | 1 | 127.0.0.1 | 200103161050|
+----+---------+-------------+-------------+

How can i do a join on these two tables to return the data in such a way that I can print it out like:

Bob
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]

Gill
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]

Any ideas?

Cheers fro any help,

Jord

Anon

select u.username, l.ip, l.time
from users u, logins l
where u.id = l.user_id
order by name;

This will give you results like
| Bob | 127.0.0.1 | 200103161050|
| Bob | 127.0.0.1 | 200103161050|
| Gill | 2 | 127.0.0.1 | 200103161050|
| Gill | 2 | 127.0.0.1 | 200103161050|

you can then loop while the name is the same.

Anon

Thanks Bill. How would I print that in PHP?

I have this so far:

$sql = "select u.username, l.ip, l.time from users u, user_logins l where u.id = l.user_id order by name";

$result = @($sql);
while($row = mysql_fetch_array($result)) {

echo $row['username'].' ';
echo $row['ip'].' ';
echo date("H:i:s l d F", $row[time]).' ';

}

Thanks,

Jord

Anon

$row = mysql_fetch_array($result);
while($row) {
$name=$row['username'];
echo $row['username'].' ';
while($name=$row['username']){
echo $row['ip'].' ';
echo date("H:i:s l d F", $row[time]).' ';
$row = mysql_fetch_array($result);
}
}

Try that.

Anon

Cheers mate. It worked apart from the line:
while($name=$row['username'])

should be (I think):

while($name==$row['username'])

Thanks for all your help.

Cheers,

Jord

Anon

I do that all the time with the ==

Anon

Hi!
This solved my problem too: http://www.phpbuilder.com/forum/read.php3?num=5&id=9307&loc=0&thread=9307.
But a new one came up. What if I also wanted to order, say, the ip-numbers starting with the lowest? Is it possible somehow to use two ORDER BY in the same select or ...?

"Bob
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]
Gill
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]
Logged in on [date here] from [ip here]"

Thanks
Ole, Denmark