RE: [PHP] Query - Grouping Results
| From: | Jeff Armstrong | Date: | Mon, 19 Mar 2001 15:13:46 +0000 |
| Subject: | RE: [PHP] Query - Grouping Results | ||
| References: | 1 | Groups: | php.db php.general |
| Request: | Send a blank email to php-db+get-7597@lists.php.net to get a copy of this message | ||
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) $sql<BR>\n",
mysql_error($dbh);
exit;
}
# Check how may items came back
$rows = mysql_num_rows( $sth );
if ( !$rows ) {
echo "DB No rows returned<BR>($database) $sql<BR>\n";
exit;
}
echo "<TABLE BORDER=0 CELLPADDING=2 CELLSPACING=0>\n",
"
<TR><TD>Database </TD><TD>$database</TD></TR>\n",
"
<TR><TD>Statement </TD><TD>$sql</TD></TR>\n",
"
<TR><TD>Records </TD><TD>$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>$column </TH>\n";
}
echo "</TR>\n";
}
# print the results
echo "<TR>\n";
foreach ($rec as $column => $value) {
echo " <TD>$value </TD>\n";
}
echo "</TR>\n";
$row_count++;
}
echo "</TABLE>\n\n";
?>
-----Original Message-----
From: Jordan Elver [mailto:jjelver@worldsites.net]
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:jjelver@worldsites.net]
> 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:jjelver@worldsites.net]
> > 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: php-general-unsubscribe@lists.php.net
For additional commands, e-mail: php-general-help@lists.php.net
To contact the list administrators, e-mail: php-list-admin@lists.php.net