[PHP-DB] Re: [PHP] Query - Grouping Results
Doesn't seem to work, how would I print that out with PHP? On Monday 19 March 2001 13:52, you wrote: how about something like select distinct name, date_format(time, "%W %D %M %Y") as login from users, user_logins where user_logins.user_id = users.id order by name,time -Original Message- From: Jordan Elver [mailto:[EMAIL PROTECTED]] Sent: Monday, March 19, 2001 1:43 PM To: PHP Database Mailing List; PHP General Mailing List Subject: [PHP] Query - Grouping Results Hi, I've got a table like: iduser_id ip time 1 2 127.0.0.1 20010316105018 Etc, etc. I do a join on the this table and the users table to get the coresponding username to user_id like this: SELECT users.name AS name, user_logins.ip AS ip, UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE user_logins.user_id = users.id ORDER BY time ASC How can I display the results grouped by username? So, I want to be able to display: Logins for John Thursday 10th Friday 12th Monday 23rd Logins for Bob Monday 1st Tuesday 2nd Saturday 31st Thanks for any help, Jord -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] RE: [PHP] Query - Grouping Results
The php below illustrates how to print database results from
mySQL in PHP. Apologies for the long post.
You should be able to extrapolate from here - ie this is
generic, you will want to keep track of the usernames and
only print them when they change. Dont forget to print the
last one if you do this. (Jackson Structured Programming
fans - all together now...)
Regards
Jeff
---
?php
# Set these up with the right values
$host = 'localhost';
$user = 'me';
$password = 'mine';
$database = 'thedb';
$sql = 'select * from sometable';
# Connect to the database
$dbh = mysql_pconnect($host,$user,$password);
if (!$dbh) {
echo "Unable to connect to database";
exit;
}
# Run the query on the db
$sth = mysql_db_query($database,$sql,$dbh);
if (!$sth) {
echo "DB Error:($database) $sqlBR\n",
mysql_error($dbh);
exit;
}
# Check how may items came back
$rows = mysql_num_rows( $sth );
if ( !$rows ) {
echo "DB No rows returnedBR($database) $sqlBR\n";
exit;
}
echo "TABLE BORDER=0 CELLPADDING=2 CELLSPACING=0\n",
" TRTDDatabasenbsp;/TDTD$database/TD/TR\n",
" TRTDStatementnbsp;/TDTD$sql/TD/TR\n",
" TRTDRecordsnbsp;/TDTD$rows/TD/TR\n",
"TABLE\n\n";
# print the results in an HTML table
echo "TABLE BORDER=1 CELLPADDING=1 CELLSPACING=0\n";
$row_count=0;
while ( $rec = mysql_fetch_array( $sth, MYSQL_ASSOC ) ) {
# Print a header row, first time only
if(!$row_count) {
echo "TR\n";
foreach ($rec as $column = $value) {
echo " TH$columnnbsp;/TH\n";
}
echo "/TR\n";
}
# print the results
echo "TR\n";
foreach ($rec as $column = $value) {
echo " TD$valuenbsp;/TD\n";
}
echo "/TR\n";
$row_count++;
}
echo "/TABLE\n\n";
?
-Original Message-
From: Jordan Elver [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 19, 2001 3:03 PM
To: Jeff Armstrong
Cc: PHP Database Mailing List; PHP General Mailing List
Subject: Re: [PHP] Query - Grouping Results
Sorry, the query works fine, but how do I print it out to the browser so
that
it looks like:
John
Dates go here
Bob
Dates go here
Gill
Dates go here
I hope that's clear,
Thanks,
Jord
On Monday 19 March 2001 14:44, you wrote:
works fine for me.
what error do you get.
-Original Message-
From: Jordan Elver [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 19, 2001 2:39 PM
To: Jeff Armstrong
Cc: PHP Database Mailing List; PHP General Mailing List
Subject: Re: [PHP] Query - Grouping Results
Doesn't seem to work, how would I print that out with PHP?
On Monday 19 March 2001 13:52, you wrote:
how about something like
select distinct
name,
date_format(time, "%W %D %M %Y") as login
from
users, user_logins
where
user_logins.user_id = users.id
order by name,time
-Original Message-
From: Jordan Elver [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 19, 2001 1:43 PM
To: PHP Database Mailing List; PHP General Mailing List
Subject: [PHP] Query - Grouping Results
Hi,
I've got a table like:
id user_id ip time
1 2 127.0.0.1 20010316105018
Etc, etc.
I do a join on the this table and the users table to get the
coresponding
username to user_id like this:
SELECT users.name AS name, user_logins.ip AS ip,
UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE
user_logins.user_id = users.id ORDER BY time ASC
How can I display the results grouped by username?
So, I want to be able to display:
Logins for John
Thursday 10th
Friday 12th
Monday 23rd
Logins for Bob
Monday 1st
Tuesday 2nd
Saturday 31st
Thanks for any help,
Jord
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] RE: [PHP] Query - Grouping Results
how about something like select distinct name, date_format(time, "%W %D %M %Y") as login from users, user_logins where user_logins.user_id = users.id order by name,time -Original Message- From: Jordan Elver [mailto:[EMAIL PROTECTED]] Sent: Monday, March 19, 2001 1:43 PM To: PHP Database Mailing List; PHP General Mailing List Subject: [PHP] Query - Grouping Results Hi, I've got a table like: id user_id ip time 1 2 127.0.0.1 20010316105018 Etc, etc. I do a join on the this table and the users table to get the coresponding username to user_id like this: SELECT users.name AS name, user_logins.ip AS ip, UNIX_TIMESTAMP(user_logins.time) AS time FROM users, user_logins WHERE user_logins.user_id = users.id ORDER BY time ASC How can I display the results grouped by username? So, I want to be able to display: Logins for John Thursday 10th Friday 12th Monday 23rd Logins for Bob Monday 1st Tuesday 2nd Saturday 31st Thanks for any help, Jord -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: [PHP] Query - Grouping Results
Doesn't seem to work, how would I print that out with PHP?
The results will come back sorted by name, then time. While processing each
row, you'll need to keep track of when the username changes, something like
this (this is just rough code, not quite valid PHP):
$username = '';
while ($data = fetchrow)
{
if ($username != $data['name'])
{
$username = $data['name'];
print "Logins for " . $username . "\n\n";
}
print $login . "\n";
}
Keep track of the current user. As you examine each record, check if it's
different than the current one. If it is, save it as the current username,
and print the login info. If it's the same, just print the login info.
- Darryl
--
Darryl Friesen, B.Sc., Programmer/Analyst[EMAIL PROTECTED]
Education Research Technology Services, http://gollum.usask.ca/
Department of Computing Services,
University of Saskatchewan
--
"Go not to the Elves for counsel, for they will say both no and yes"
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
