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]
[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 --- \n", mysql_error($dbh); exit; } # Check how may items came back $rows = mysql_num_rows( $sth ); if ( !$rows ) { echo "DB No rows returned($database) $sql\n"; exit; } echo "\n", " Database $database\n", " Statement $sql\n", " Records $rows\n", "\n\n"; # print the results in an HTML table echo "\n"; $row_count=0; while ( $rec = mysql_fetch_array( $sth, MYSQL_ASSOC ) ) { # Print a header row, first time only if(!$row_count) { echo "\n"; foreach ($rec as $column => $value) { echo " $column \n"; } echo "\n"; } # print the results echo "\n"; foreach ($rec as $column => $value) { echo " $value \n"; } echo "\n"; $row_count++; } echo "\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
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
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]