...

View Full Version : Grouping data together



holty
12-03-2003, 08:55 AM
Hi,

I'm pulling various information about events from an oracle database.

To cut it short, lets say the 'Date' and 'Location'

At the moment info is displayed like:

Date Location
17-01-2004 Liverpool, UK
24-01-2004 Cardiff, UK
24-01-2004 Liverpool, UK

Where location is two separate fields in the table.

What I want to do is - if the date is the same, display like this (instead of long list)

Date Location
17-01-2004 Liverpool, UK
24-01-2004 Cardiff, UK
Liverpool, UK


Any ideas? something in php?

raf
12-03-2003, 01:04 PM
Inside your while loop, you need to check if the data is the same as the one from the previous record.

I was a bit less cryptic here
http://www.codingforums.com/showthread.php?s=&threadid=29119

holty
12-08-2003, 01:48 PM
Raf,

I've looked at your code but I can't seem to crack it...

I'm pulling my info from a stored procedure from oracle and looping through pullout out the details, e.g.



<?
while (OCIFetchInto($curs,&$data)) {
$date = $data[1];
$location = $data[2] . ", " . $data[3];
?>
<tr>
<td valign="top" width="150" height="16"><? echo $date; ?></td>
<td height="16" valign="top" width="150"><? echo $location; ?></td>
</tr>
<? } ?>
</table>
<?
OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>


how could i say if the date is equal to the previous date?

raf
12-08-2003, 03:06 PM
In your case, it would be :


<?
$prevdate='999999999999';
while (OCIFetchInto($curs,&$data)) {
$date = $data[1];
$location = $data[2] . ", " . $data[3];
echo('<tr><td valign="top" width="150" height="16">');
if ($prevdate !=$date) {
echo $date ;
}
echo ('</td><td height="16" valign="top" width="150">' . $location.'</td></tr>');
$prevdate = $date ;
}
echo '</table>';

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>

holty
12-09-2003, 09:24 AM
Raf,

Cheers - I can see how that works but it at the mo it draws a new table row for each location. I would like a <br> tag after each location in the <td>....

When I try it only does the one location and blanks out the others for that date.

Any ideas?

holty
12-09-2003, 09:30 AM
Got that to work....

but now I have a problem where I want to add extra colomns in...



$prevdate='999999999999';
while (OCIFetchInto($curs,&$data)) {
$date = $data[1];
$location = $data[6] . ", " . $data[7];

if ($prevdate !=$date) {
echo('<tr><td valign="top" width="150" height="16">');
echo $date ;
echo ('</td><td height="16" valign="top" width="150">');
}
echo $location . ('<br>');
$prevdate = $date ;
}
echo ('</td></tr></table>');


How can I add an extra colomn and still get it to look correct?

raf
12-09-2003, 02:31 PM
I'm not sure what you want + where you want these column. If you need them after the locations, then you can just add them after the location.

But your tablelayout doesn't make any sense to me and it will produce invalid syntax, if you have more then 1 different date. The closing tags of the cell and row should come after the Locationvalue.

holty
12-10-2003, 09:07 AM
Raf

Here is how I would like it to look:

Date: Location: Bill Date: Stock Level:
10/10/03 London, UK 15/10/03 10245
--------------Leeds, UK
--------------New York, USA
12/10/03 London, UK 17/10/03 254



where the locations are in one <td> with a <br> at the end.

Therefore it would be like:



<table>
<tr>
<td>Date:</td>
<td>Location:</td>
<td>Bill Date:</td>
<td>Stock Level:</td>
</tr>
<tr>
<td>10/10/03</td>
<td>London, UK<br>Leeds, UK<br>New York, USA</td>
<td>15/10/03</td>
<td>10245</td>
</tr>
<tr>
<td>12/10/03</td>
<td>London, UK</td>
<td>17/10/03</td>
<td>254</td>
</tr>
</table>


Any ideas how I can display it like that?

Thanks very much

raf
12-10-2003, 10:36 AM
Well, the easiest thing would be to just have another row for each location, where you only print the date and end columns if it's a record with a new date. So basically like my last code, buth with two if_($prevdate_!=$date)_checks.

If you need it inside the cell, then you best write out the row if the last record with that date is processed + an extra print after the loop (for the last date). But there is probably an oracle function to concatenate the values for a string-variable inside a 'group by' clause. The PHP way to do it would be:


<?

$i = 1;
$prevdate='999999999999';

while (OCIFetchInto($curs,&$data)) {

$date = $data[1];
$location = $data[2] . ", " . $data[3];

if ($prevdate !=$date) {
if ($i != 1){
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');
} else {
$i = 2 ;
}
$collocation = $location ;
$prevdate = $date ;
$lastcolumns = ($var1 .'</td><td>'. $var2);
} else {
$collocation .= ('<br />' . $location) ;
}
}
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');

echo '</table>';

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>


I didn't debug or test it, but it should be allright. You'll need to replace the $var1 and $var2 with variables from your recordset.

<edit>I forgot to include the printing of the last row in the code. Set typo straight (see below)</edit>

holty
12-10-2003, 11:57 AM
Raf,

I have just tried:



$i = 1;
$prevdate='999999999999';

while (OCIFetchInto($curs,&$data)) {

$date = $data[1];
$location = $data[6] . ", " . $data[7];
$billdate = $data[2];
$stocklevel = $data[3];

if ($prevdate !=$date) {
if ($i != 1){
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collacation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');
} else {
$i = 2 ;
}
$collocation = $location ;
$prevdate = $date ;
$lastcolumns = ($billdate .'</td><td>'. $stocklevel);
} else {
$collocation .= ('<br />' . $location) ;
}
}
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collacation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');

echo '</table>';

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>


but it produces a table like

Date:---------------Location:---------------Bill Date:---------------Stock Level:
10/10/2003----------------------------------12/10/2003
12/10/2003----------------------------------14/10/2003

i.e. its not printing a location or stock level..... just two rows...

any ideas? I'm only doing this to please the user and make it look better! arghhhhhhh!!

raf
12-10-2003, 12:36 PM
My bad. There's a typo

echo('<td>'. $collacation .'</td>');

correct it to

echo('<td>'. $collocation .'</td>');

and try again.

holty
12-10-2003, 01:29 PM
Raf - well spotted!

I've just tried that....

It doesn't print a location for any of the dates apart from the very last date.....

However the location information for that last date is correct.

Any ideas why its doing that?

raf
12-10-2003, 01:38 PM
The first thing i would think of is that you changed the

$collocation = $location ;

into

$collacation = $location ;

because that would give you exactly that result.

But you need to do the reverse : change the

echo('<td>'. $collacation .'</td>');

into

echo('<td>'. $collocation .'</td>');

--> both times :inside and after the loop

holty
12-10-2003, 02:02 PM
Raf

I hadn't change the one in the loop!

I'm trying now to stick in an if before as the only locations that need to be grouped are outside the uk

so i've done:



$i = 1;
$prevdate='999999999999';

while (OCIFetchInto($curs,&$data)) {

$date = $data[1];
$location = $data[6] . ", " . $data[7];
$billdate = $data[2];
$stocklevel = $data[3];

if ($data[7] != "UK") {
if ($prevdate !=$date) {
if ($i != 1){
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');
} else {
$i = 2 ;
}
$collocation = $location ;
$prevdate = $date ;
$lastcolumns = ('<center><img src="images/high.gif"><br><font size="1">high</center></td><td>'. $billdate);
} else {
$collocation .= ('<br />' . $location) ;
}
$prevdate = $date ;
}
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');

} else {
echo('<tr><td valign="top" width="150" height="16">'. $date.'</td>');
echo('<td>'. $location .'</td>');
echo('<td>'. $stocklevel.'</td><td>'. $billdate .'</td></tr>');
}

echo '</table>';

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>


i get a parse error on my final else.... can u see the problem?

thanks for all the help - you've been great once again!

raf
12-10-2003, 02:38 PM
The problem obviously is the

}
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');

that isn't placed correctly anymore.
The } will close the while-loop, and you want the
} else {
echo('<tr><td valign="top" width="150" height="16">'. $date.'</td>');
echo('<td>'. $location .'</td>');
echo('<td>'. $stocklevel.'</td><td>'. $billdate .'</td></tr>');

inside the loop, right. So it would then need to be


$i = 1;
$prevdate='999999999999';

while (OCIFetchInto($curs,&$data)) {

$date = $data[1];
$location = $data[6] . ", " . $data[7];
$billdate = $data[2];
$stocklevel = $data[3];

if ($data[7] != "UK") {
if ($prevdate !=$date) {
if ($i != 1){
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');
} else {
$i = 2 ;
}
$collocation = $location ;
$prevdate = $date ;
$lastcolumns = ('<center><img src="images/high.gif"><br><font size="1">high</center></td><td>'. $billdate);
} else {
$collocation .= ('<br />' . $location) ;
}
$prevdate = $date ;

} else {
echo('<tr><td valign="top" width="150" height="16">'. $date.'</td>');
echo('<td>'. $location .'</td>');
echo('<td>'. $stocklevel.'</td><td>'. $billdate .'</td></tr>');
}

}
echo('<tr><td valign="top" width="150" height="16">'. $prevdate .'</td>');
echo('<td>'. $collocation .'</td>');
echo('<td>'. $lastcolumns .'</td></tr>');

echo '</table>';

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);
?>


To have is semantically correct. But i have no idea what your trying to achieve with that code.

holty
12-10-2003, 03:36 PM
Raf

Its not my choice - unfortunately its a user requirement - they want to group the location by date where it not in the UK...

I have tested the code and it prints the correct information however not in the correct order.

It used to be ordered by date but now it does:

location in uk - Jul 2003
location in uk - Sep 2003
locations outside uk - Jul 2003
locations outside uk - sep 2003
location in uk - Nov 2003
location in uk - Jan 2004
locations outside uk - Nov 2003
locations outside uk - Feb 2004

any ideas on how to get it back in order?

Thanks for all your help
:thumbsup:

raf
12-10-2003, 05:08 PM
I don't know your current sql-statement, but it should end with

... ORDER BY datevariable ASC

it would be a lot easier if you include an extra variable inside the recordset that indicates if it's a UK or non UK location, so that you can also order on that variable.

... ORDER BY datevariable ASC, ukvariable ASC

holty
12-11-2003, 10:21 AM
Raf

I just needed to order it by the date and location - before it was just by the date....

All works well

Thanks very much for your help!:thumbsup:

raf
12-11-2003, 02:36 PM
You're welcome. Glad you got it running.

Small note, ordering on the location --> wount UK and non-UK be mingled if there are non-UK codes like 'VW' or so ? Could be that your current sollution (just ordering on the locationvariable) works perfectly for now, but that it will get you into problems if you later on add locations that will appear after the 'UK' location ...

holty
12-18-2003, 10:36 AM
raf - sorry to bring this back up.....

I have a problem and it doesn't appear in my pl/sql as it returns the correct order....

For some reason its printing:
location in uk - Mar 2003
location in uk - Mar 2003
location in uk - Jul 2003
locations outside uk - Apr 2003
locations outside uk - Nov 2003

Here is my php



$i = 1;
$previous_date = 0;
$j = 0;

while (OCIFetchInto($curs,&$data)) {
$date = $data[1];
$bill_date = $data[2];
$location = $data[6] . ", " . $data[7];

//create unix timestamps
$today_stamp = strtotime($getdate);
$bill_date_stamp = strtotime($bill_date);

if ($bill_date_stamp < $today_stamp) {
if ($data[7] != "UK") {
if ($previous_date != $date) {
if ($i != 1){
echo('<tr><td valign="top" width="150" height="16">'. $previous_date . " (" . $days . ")" .'</td>');
echo('<td valign="top">'. $col_location .'</td>');
echo('<td valign="top">'. $col_bill_date .'</td></tr>');
} else {
$i = 2 ;
}
$col_location = $location ;
$previous_date = $date;
$col_bill_date = $bill_date;
} else {
$col_location .= ('<br />' . $location) ;
}
$previous_date = $date ;

} else {
echo('<tr><td valign="top" width="150" height="16">'. $date.'</td>');
echo('<td valign="top">'. $location .'</td>');
echo('<td valign="top">'. $bill_date .'</td></tr>');
}
$j = $j + 1;
}
}
if ($j = 0) {
echo('<tr><td valign="top" width="150" height="16" colspan="4">There are no archive invoices available</td></tr>');
} else {
echo('<tr><td valign="top" width="150" height="16">'. $date . " (" . $days . ")" .'</td>');
echo('<td valign="top">'. $col_location .'</td>');
echo('<td valign="top">'. $col_bill_date .'</td></tr>');
}
echo ('</table>');

OCIFreeCursor($stmt);
OCIFreeStatement($curs);
OCILogoff($conn);


It must be something i'm doing in there as the function returns the data in the correct order - have you any ideas? sorry again!

holty
12-19-2003, 11:56 AM
Raf - If you look at the code above this is what it is doing:

Location----Value(i)----Notes
UK------------1-----------Draws 1st table row
UK------------1-----------Draws 2nd table row
USA---------- 2-----------Assigns variables and sets prev date = date
JAM-----------2-----------Appends Jam onto USA in location variable
UK------------2-----------Draws 3rd table row
USA---------- 2------------Draws 4th table row with old USA and JAM info, assigns new variables and sets prev date to date
JAM-----------2-----------Appends Jam onto USA in location variable
UK------------2------------Draws 5th table row

So you can see that it draws the 3rd UK location before the 1st non-UK location.

How can I get it to do it in the correct order?

holty
12-19-2003, 12:38 PM
raf - nailed it mate

I just added:


if (($i != 1) && ($previous_date != date)) {
echo('<tr><td valign="top" width="150" height="16">'. $previous_date.'</td>');
echo('<td valign="top">'. $col_location .'</td>');
echo('<td valign="top">'. $last_columns .'</td></tr>');
$i = 1;


in the else statement if the location is in the UK....

also removed the last record after the while loop

raf
12-19-2003, 12:54 PM
i'm to tiered to realy think it through now. My quick and dirty sollution would be to drop the nested conditions and have 3 combined conditions where you check on both the location, date and strlen of the non-UK variables.

Like


$previous_date = '';
while (OCIFetchInto($curs,&$data)) {
$date = $data[1];
$bill_date = $data[2];
$location = $data[6] . ", " . $data[7];

//create unix timestamps
$today_stamp = strtotime($getdate);
$bill_date_stamp = strtotime($bill_date);

if ($bill_date_stamp < $today_stamp) {
if($data[7]_==_"UK")_{
if_(strlen($col_location) >=_1){ // print out bufferd non-UK line
echo('<tr><td valign="top" width="150" height="16">'._$previous_date_._" ("_._$days_._")"_.'</td>');
echo('<td valign="top">'._$col_location_.'</td>');
echo('<td valign="top">'._$col_bill_date_.'</td></tr>');
}_
echo('<tr><td valign="top" width="150" height="16">'.__$date.'</td>');
echo('<td valign="top">'._$location_.'</td>');
echo('<td valign="top">'._$bill_date_.'</td></tr>');
}
if_(($previous_date_==_$date) and ($data[7]_!=_"UK"))_{
$col_location_.=_('<br />'_._$location)_;
}
if_(($previous_date_!=_$date) and ($data[7]_!=_"UK"))_{
if_(strlen($col_location) >=_1){ // print out bufferd non-UK line
echo('<tr><td valign="top" width="150" height="16">'._$previous_date_._" ("_._$days_._")"_.'</td>');
echo('<td valign="top">'._$col_location_.'</td>');
echo('<td valign="top">'._$col_bill_date_.'</td></tr>');
}
$col_location_=_$location_;
$col_bill_date_=_$bill_date;
}
$previous_date_=_$date;
}
}
if_(strlen($col_location) >=_1){ // print out bufferd non-UK line
echo('<tr><td valign="top" width="150" height="16">'. $date . " (" . $days . ")" .'</td>');
echo('<td valign="top">'. $col_location .'</td>');
echo('<td valign="top">'. $col_bill_date .'</td></tr>');
}


Didn't test it but you'll get the idea.

<edit>You nee to remove the underscores (all, except the once in variable or functionnames. I don't knowhow they came in here after i copied the text.</edit>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum