Solarpitch Posted January 5, 2009 Share Posted January 5, 2009 Hey Guys, I have a sales table as below. Each item will have a ticket number associated with it as shown. The problem is, some items will have the same ticket number assigned to them as they were part of the same sale. I need to group items that have the same ticketnumber. In the example below, I put in the dark row colors which define each sale and the items in that sale. Ideally I would like to format it like the bottom image but if I cant, I would like to put a dark row color at the end of each ticket number... so when the ticket number changes the system should know that the next row will be a different sale. Hope this makes sense. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 5, 2009 Share Posted January 5, 2009 Perhaps it's just me, but i've no idea what the problem is. Do you have a database setup? Are you asking how you should store that information? Or how to retrieve it? Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 Sorry.. its probably just hard to explain. Yeah, database is set up, I can retrieve the information etc. If you look at the top image thats essentially a screenshot of the results from the webpage. That is my database table sales... I just done a select * from sales and printed it. You can see the ticket numbers on the right that are assigned to each item. Some items have the same ticket number which means they are part of the same sale. When the result set is being printed to screen I want a way to group or seperate the results so you can clearly see all the items that belong to the same sale ie. ticket number or receipt .. whatever you want to call it. I drew in the dark row colors to differentiate each sale item in the list... this is what I'm trying to achieve. Is that any clearer? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 5, 2009 Share Posted January 5, 2009 Ok, so the above is a screenshot except that you added in some lines? If i understand you correctly, the correct approach is to order by the ticket number (which it looks like you've done). Then use a variable to keep track of the previous ticket number in your loop. Each time the loop runs, check to see if the ticket number in the row is different to the previous ticket number. If it is, output a blank row and update the ticket number variable. I can't give you any more than that without seeing the code. And if that's still not the problem you've got, you'll have to re-explain again. Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 Your spot on! Thats the problem alright and your solution is what I had in mind... I was just having some problem figuring out how to implement it. Here's an attempt. Something like this I'm guessing. <?php function sales_breakdown() { $query = $this->client->query("SELECT * FROM sales WHERE date = ".$this->yesterday.""); if ($query->num_rows() > 0) { $this->display .= " <table border='0' cellspacing='0' cellpadding='0' id='breakdown_table'> <tr bgcolor='".$this->bgcolor."'> <td width='280'>Item</td> <td width='60'>Price</td> <td width='70'>Type</td> <td width='60'>Till</td> <td width='60'>Time</td> <td width='110'>Ticket Number</td> </tr>"; foreach ($query->result() as $row) { $track_num = $row->ticketnumber; if($row->ticketnumber != $track_num) { //print a blank row? This is where I get stuck } $this->display .= " <tr> <td>".$row->item."</td> <td>".$row->price."</td> <td>".$row->type."</td> <td>".$row->till."</td> <td>".$row->till."</td> <td>".$row->ticketnumber."</td> </tr>"; } } $this->display .= "</table>"; return $this->display; } ?> Quote Link to comment Share on other sites More sharing options...
revraz Posted January 5, 2009 Share Posted January 5, 2009 Group by ticket number maybe? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 5, 2009 Share Posted January 5, 2009 1.) You need to be ordering by the ticket number to ensure this works. 2.) You should define the variable prior to the loop starting. 3.) You should test the value of $track_num prior to changing it. Otherwise it'd always be true. <?php function sales_breakdown() { $query = $this->client->query("SELECT * FROM sales WHERE date = ".$this->yesterday." ORDER BY ticketnumber"); if ($query->num_rows() > 0) { $this->display .= " <table border='0' cellspacing='0' cellpadding='0' id='breakdown_table'> <tr bgcolor='".$this->bgcolor."'> <td width='280'>Item</td> <td width='60'>Price</td> <td width='70'>Type</td> <td width='60'>Till</td> <td width='60'>Time</td> <td width='110'>Ticket Number</td> </tr>"; $track_num = '';//define it before the loop - you shouldn't check against an undefined variable foreach ($query->result() as $row) { if($row->ticketnumber != $track_num && $track_num != ''){ //also check to make sure $track_num has been set. Dont want a blank row as the first row of the table. //print a blank row? This is where I get stuck $this->display . = '<tr><td colspan="6"> </td></tr>'; $track_num = $row->ticketnumber; } $this->display .= " <tr> <td>".$row->item."</td> <td>".$row->price."</td> <td>".$row->type."</td> <td>".$row->till."</td> <td>".$row->till."</td> <td>".$row->ticketnumber."</td> </tr>"; } } $this->display .= "</table>"; return $this->display; } ?> Revraz, you don't want to group by the ticket number. That'd mean you'd only have one row for each ticket number. Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 I understand everything you just said. Tried to implement it there... query runs fine but doesnt seem to put gap between the rows. Here's the full function at the moment. <?php function sales_breakdown() { $query = $this->client->query("SELECT * FROM sales WHERE date = ".$this->yesterday." ORDER BY ticketnumber"); if ($query->num_rows() > 0) { $this->display .= " <table border='0' cellspacing='0' cellpadding='0' id='breakdown_table'> <tr> <td width='280'>Item</td> <td width='60'>Price</td> <td width='70'>Type</td> <td width='60'>Till</td> <td width='60'>Time</td> <td width='110'>Ticket Number</td> </tr>"; $track_num = ''; foreach ($query->result() as $row) { if($row->ticketnumber != $track_num && $track_num != ''){ $this->display .= '<tr><td colspan="6"> </td></tr>'; $track_num = $row->ticketnumber; } if ($this->bgcolor == "#F4F6F6"){ $this->bgcolor = "#F0F0F0"; }else{ $this->bgcolor = "#F4F6F6"; } $this->display .= " <tr bgcolor='".$this->bgcolor."'> <td>".$row->item."</td> <td>".$row->price."</td> <td>".$row->type."</td> <td>".$row->till."</td> <td>".$row->till."</td> <td>".$row->ticketnumber."</td> </tr>"; } } $this->display .= "</table>"; return $this->display; } ?> Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 5, 2009 Share Posted January 5, 2009 My fault. The value of $track_num needs to be changed outside the if statement. Otherwise, it's value will never change - the if statement wont run until the value changes. if($row->ticketnumber != $track_num && $track_num != ''){ $this->display .= '<tr><td colspan="6"> </td></tr>'; } $track_num = $row->ticketnumber; Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 Man that works perfect. I have a load of projects coming up where I can use that now. Will make life easy for the client also. Thanks Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 Sorry... there's just one more question I have. See the way its formatting at the min with the blank line printed after the items, is there anyway to change it so the line will print before the items. The reason I ask is because I'm now using the line to display the ticket number as there's no point in having the ticket number listed for all the items with the same number. So id like to have it TICKET NUM 1 (blank line) item 1 item 2 item 3 TICKET NUM 2 (blank line) item 1 item 2 instead of... item 1 item 2 item 3 TICKET NUM 1 (blank line) item 1 item 2 TICKET NUM 2 (blank line) I've tried putting another foreach just after the query but obviously this will print all of the numbers before it even prints the items, Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 5, 2009 Share Posted January 5, 2009 Sure; just change it so the old ticket number is echoed instead of the current one(edit: except the first time through; you'll want the current ticket number then) and remove the check that currently prevents the first line being blank. Edit again: That didn't make much sense. Just have it echo out the current ticket number on a blank line every time it changes. Remove the check that prevents this happening the first time the loop runs. Quote Link to comment Share on other sites More sharing options...
Solarpitch Posted January 5, 2009 Author Share Posted January 5, 2009 I think I get ya... like this, <?php .... if($row->ticketnumber == $track_num){ $this->display .= '<tr bgcolor="#CCCCCC"><td colspan="6"><b>'; $this->display .= $row->ticketnumber; $this->display .='</b></td></tr>'; $this->display .= '<tr><td colspan="6"> </td></tr>'; } $track_num = $row->ticketnumber; .... ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.