Re: [PHP-DB] Grouped sql output
Hi, Sorry for the delay. I'm still about to test the code. Been busy with Perl and ColdFusion. It's on my list of things to do still. I'll be sure to post my code and result as soon as I can Won't be today though. I know there is at least one problem ( the $thiszip in the if block. ) Thanks Rudi. - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Rudi Starcevic" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, March 01, 2002 9:05 AM Subject: Re: [PHP-DB] Grouped sql output > Hi Rudi, > > > What do you think ? > > There are some problems. > > > I'll test it tomorrow at work. > > I'll let you know how she goes. > > How did you get on? > > Regards, > =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi Rudi, > What do you think ? There are some problems. > I'll test it tomorrow at work. > I'll let you know how she goes. How did you get on? Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Grouped sql output
Hi,
Thanks for the input,
I'll stop messing around and post some code
Be warned this code is untested :-)
It's kinda like thinking out loud.
I think this should produce a nested list output from a query.
Given this query :
SELECT count(zip) AS zipcount,zip,cust_last_name
FROM customers
GROUP BY zip
ORDER BY zip,cust_last_name;
My php code might look like : ( for Postgresql )
$thiszip = pg_result($res,0,0); // the first zip code in the record set
echo ""; // open main list
for ( $i=0; $i<=pg_numrows($res); ++$i ) // begin loop over record set
{
if ( pg_result($res,$i,0) != $thiszip ) // if this zip is the same as the last
{
if ( $thiszip != "no_zip_yet" ) // if this is true we have an open ol list
{ echo "";}
echo "".pg_result($res,$i,0); // output zip code
echo ""; // open inner list
echo "".pg_result($res,$i,1); // output customer data
$thiszip = pg_result($res,$i,0); // track current zip
}
else // zip is same as last one
{
echo "".pg_result($res,$i,1); // output customer data
}
} // finish loop.
echo ""; // close main list.
What do you think ?
I'll test it tomorrow at work.
I'll let you know how she goes.
Thanks for your time, suggestions and attention.
Much appreciated.
Regards
Rudi.
=
Get your personalised email and homepage at www.easymail.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Grouped sql output
Not entirely sure what you're trying to do, but would it be sensible to
use...
$sql="SELECT * FROM Customers ORDER BY zip ASC";
.
.
and then when you've got the results...
if ($myrow = mysql_fetch_array($result)) {
$zip=0;
$zipcount=0;
do {
if ($zip!=$myrow[zip]){
if ($zipcount!=0){
echo ("\n\n");
}else{
$zipcount=1;
}
echo ("\n$zip\n\n");
}
echo ("$myrow[customer]\n");
}while ($myrow = mysql_fetch_array($result));
echo ("\n\n");
}
or something like that anyways ;)
hope that's useful?
cheers,
Matt
-Original Message-
From: Rudi Starcevic [mailto:[EMAIL PROTECTED]]
Sent: 26 February 2002 11:02
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Grouped sql output
Hi,
I've done this before in coldfusion.
It's the one thing I'm note sure on how to do the same(ish)
in PHP - my language of choice.
If we have the record set the CF code would look like
#zip#
#customer#
So I'm think also to use 2 loops for the same purpose in PHP.
Can I do this from the record set like above or do I need
to build array's I wonder ?
Cheers
Rudi.
> Thanks Sven,
>
> I've already got the sql OK.
> I have the records set but I want to print it "report like"
> So It's needs some thing like a loop with an inner loop Like
>
>
>
> zip
>
> customer
> customer
>
> zip
>
> customer
> customer
>
>
>
>
> Hope this makes sense.
> I'll elaborate more if you think I should.
> Cheers
> Rudi.
>
> > Hi friends,
> >
>
> > I've been struggling for a while now so I thought I'd ask.
> > What's the easiest way to for to print group sql output. For
> > example I need to output customers grouped by zip. Like: zip
> > customer a customer b zip customer d customer e zip customer
> > c custoemr f customer g
> >
> > Do I need to build array's and loop through them or
> > can I print straight from a record set ( using Postgresql
> > or Mysql ) Thanks heaps ! Regards Rudi.
> >
> > =
> > Get your personalised email and homepage at www.easymail.info
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
>
> =
=
Get your personalised email and homepage at www.easymail.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.323 / Virus Database: 180 - Release Date: 08/02/02
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.323 / Virus Database: 180 - Release Date: 08/02/02
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi Rudi, > I've done this before in coldfusion. > It's the one thing I'm note sure on how to do the same(ish) > in PHP - my language of choice. > If we have the record set the CF code would look like > > > > #zip# > > > #customer# > > > > > > So I'm think also to use 2 loops for the same purpose in PHP. > Can I do this from the record set like above or do I need > to build array's I wonder ? No need for arrays - the resultset from the RDBMS effectively is one - SQL-talk says it's a table (please show us your SQL query if you have any doubt about this). Make sure the SQL resultset is sorted/grouped correctly! You're approach looks right to me - just more CF than PHP-like. How's this? query RDBMS check result ok/have at least one row extract first row initiate presentation WHILE not EoD note zip code ECHO zip heading WHILE this row's zip == noted zip AND not EoD ECHO customer data end while zip extract next row from resultset end while data terminate presentation OK? Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi Rudi, > I've been struggling for a while now so I thought I'd ask. > What's the easiest way to for to print group sql output. > For example I need to output customers grouped by zip. > Like: > zip > customer a > customer b > zip > customer d > customer e > zip > customer c > custoemr f > customer g > > Do I need to build array's and loop through them or > can I print straight from a record set ( using Postgresql or Mysql ) As a general rule, I would ask the RDBMS to do as much as possible (WHERE, GROUP BY, ORDER BY, HAVING), before handing the data to PHP - then use PHP to concentrate on getting the formatting/presentation (HTML) right! What you're asking for is quite common - and easy to implement (once you know how). Does that 'philosophy' answer your question, or were you wanting coding-level advice? If so, please post your current SQL query and if it's not too long, the pertinent part of your PHP prototype, and I/someone else here, will be happy to make suggestions. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Hi, I've done this before in coldfusion. It's the one thing I'm note sure on how to do the same(ish) in PHP - my language of choice. If we have the record set the CF code would look like #zip# #customer# So I'm think also to use 2 loops for the same purpose in PHP. Can I do this from the record set like above or do I need to build array's I wonder ? Cheers Rudi. > Thanks Sven, > > I've already got the sql OK. > I have the records set but I want to print it "report like" > So It's needs some thing like a loop with an inner loop Like > > > > zip > > customer > customer > > zip > > customer > customer > > > > > Hope this makes sense. > I'll elaborate more if you think I should. > Cheers > Rudi. > > > Hi friends, > > > > > I've been struggling for a while now so I thought I'd ask. > > What's the easiest way to for to print group sql output. For > > example I need to output customers grouped by zip. Like: zip > > customer a customer b zip customer d customer e zip customer > > c custoemr f customer g > > > > Do I need to build array's and loop through them or > > can I print straight from a record set ( using Postgresql > > or Mysql ) Thanks heaps ! Regards Rudi. > > > > = > > Get your personalised email and homepage at www.easymail.info > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > = = Get your personalised email and homepage at www.easymail.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Grouped sql output
Thanks Sven, I've already got the sql OK. I have the records set but I want to print it "report like" So It's needs some thing like a loop with an inner loop Like zip customer customer zip customer customer Hope this makes sense. I'll elaborate more if you think I should. Cheers Rudi. > Hi friends, > > I've been struggling for a while now so I thought I'd ask. > What's the easiest way to for to print group sql output. For > example I need to output customers grouped by zip. Like: zip > customer a customer b zip customer d customer e zip customer > c custoemr f customer g > > Do I need to build array's and loop through them or > can I print straight from a record set ( using Postgresql > or Mysql ) Thanks heaps ! Regards Rudi. > > = > Get your personalised email and homepage at www.easymail.info > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php = Get your personalised email and homepage at www.easymail.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
