Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping data together

    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?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    GROUP BY... How to use it?

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    PHP Code:
    <?
    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?

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In your case, it would be :
    PHP Code:
    <?
    $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);
    ?>

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got that to work....

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

    PHP Code:
    $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?

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    PHP Code:
    <table>
      <
    tr>
        <
    td>Date:</td>
        <
    td>Location:</td>
        <
    td>Bill Date:</td>
        <
    td>Stock Level:</td>
      </
    tr>
      <
    tr>
        <
    td>10/10/03</td>
        <
    td>LondonUK<br>LeedsUK<br>New YorkUSA</td>
        <
    td>15/10/03</td>
        <
    td>10245</td>
      </
    tr>
      <
    tr>
        <
    td>12/10/03</td>
        <
    td>LondonUK</td>
        <
    td>17/10/03</td>
        <
    td>254</td>
      </
    tr>
    </
    table
    Any ideas how I can display it like that?

    Thanks very much

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    PHP Code:
    <?

    $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 ;
         }
         
    $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>
    Last edited by raf; 12-10-2003 at 01:39 PM.

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Raf,

    I have just tried:

    PHP Code:
    $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 ;
         }
         
    $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!!
    Last edited by holty; 12-10-2003 at 11:59 AM.

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My bad. There's a typo

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

    correct it to

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

    and try again.

  • #12
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #14
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    PHP Code:
    $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 ;
         }
         
    $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!
    Last edited by holty; 12-10-2003 at 02:05 PM.

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    PHP Code:
    $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 ;
          }
          
    $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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •