php - MYSQL- Displaying data while looping through week ending dates -
for past month of so, have been creating benchmark system allows users add categories & benchmarkitems retain data every week within specific year. having trouble creating mysql statement shows each benchmarkitem results weeks within yr if week there no data or no data year. below queries have been testing , table columns. using codeigniter , plan display results in format mapped out @ bottom. apologize lengthy description attempting clear possible. appreciated.
tblbenchmarkitem ================= itemid | itemdescription | itemtarget | itemfreq | fkcategoryid tblbenchmarkdata ================ dataid | fkitemid | resultdate | result | dateadded | datemodified tblcategories =============== categoryid | categoryname | parentid | fkdeptid tblcalendardates - stores 52 weeks year saturday friday ================ id | year | startdate | enddate these mysql statements in model. going set default value in function param year.
public function get_data() { $dates = $this->db->query("select enddate tblcalendardates year = '2014' order enddate"); $dates = $dates->result(); foreach ($dates $date ) { //$query = "select tblcalendardates.enddate, tblbenchmarkitems.itemdescription, tblbenchmarkitems.itemtarget, tblbenchmarkitems.itemfrequency, tblbenchmarkdata.resultdate, ifnull(tblbenchmarkdata.result,0) result, (select strcategoryname tblcategories tblbenchmarkitems.fkcategoryid = tblcategories.categoryid) category tblbenchmarkdata left join tblbenchmarkitems on tblbenchmarkdata.fkitemid = tblbenchmarkitems.itemid join tblcalendardates date(tblbenchmarkdata.resultdate) between (select min(date(tblcalendardates.startdate)) tblcalendardates tblcalendardates.year = " . date('y') . ") , (select max(date(tblcalendardates.enddate)) tblcalendardates tblcalendardates.year = " . date('y') . ") group category order tblcalendardates.enddate"; $query = 'select c.*, i.itemdescription, i.itemtarget, ifnull(d.result, 0) result,"' . $date ->enddate . '" tblcategories c join tblbenchmarkitems on i.fkcategoryid = c.categoryid join tblbenchmarkdata d on i.itemid = d.fkitemid join tblcalendardates date(d.resultdate) between (select min(date(tblcalendardates.startdate)) tblcalendardates tblcalendardates.year = "' . date('y') . '") , (select max(date(tblcalendardates.enddate)) tblcalendardates tblcalendardates.year = "' . date('y') . '") group strcategoryname'; $query = $this->db->query($query); return $query->result(); } } this in view
<table class="table table-hover data-list"> <thead> <tr> <th class="th-set-width1">benchmark item</th> <th class="th-set-width2">target</th> <?php foreach( $weeks $week ): ?> <th class="th-set-width2"><?php echo date('m-d-y', strtotime($week->enddate)); ?></th> <?php endforeach; ?> </tr> </thead> <tbody> <?php foreach( $entries $entry ) : ?> <tr> <td><?php echo $entry->itemdescription; ?></td> <td><?php echo $entry->itemtarget; ?></td> <?php ($i = 0; $i < 52; $i++) : ?> <?php if ($entry->result != null || $entry->result > 0): ?> <td><?php echo $entry->result; ?></td> <?php else: ?> <td>0</td> <?php endif; ?> <?php endfor; ?> </tr> <?php endforeach; ?> </tbody> </table> this how want display data in table.
----------------------------------------------------------------------------------- categoryname ==================================================================================== itemdescription | itemtarget | 01-03-14 | 01-10-14 | 01-17-14 | 01-24-14 | 01-31-14 ===================================================================================== no. of visits | 12.00 | null | null | 15.00 | null | 20.00 no. of calls | 17.00 | 12.00 | null | 17.00 | 22.00 | null
i have added changes code yields response looking for. issue have 2 items being displayed 1 result each item.
my model:
public function get_data() { $items = $this->db->query('select * tblbenchmarkitems'); $items = $items->result(); foreach ( $items $item ) { $query = 'select c.enddate, ifnull(d.result,0) result tblbenchmarkdata d right join tblcalendardates c on (date(d.resultdate) = c.enddate) join tblbenchmarkitems c.year = "2014" , i.itemid = "' . $item->itemid .'" group c.enddate'; $query = $this->db->query($query); $data[] = array( 'itemid' => $item->itemid, 'itemdescription' => $item->itemdescription, 'itemtarget' => $item->itemtarget, 'dates' => $query->result() ); } return $data; } my view:
<table class="table table-hover data-list"> <thead> <tr> <th class="th-set-width1">benchmark item</th> <th class="th-set-width2">target</th> <?php foreach( $weeks $week ): ?> <th class="th-set-width2"><?php echo date('m-d-y', strtotime($week->enddate)); ?></th> <?php endforeach; ?> </tr> </thead> <tbody> <?php for( $n = 0; $n < count($entries); $n++ ) : ?> <tr> <td><?php echo $entries[$n]['itemdescription']; ?></td> <td><?php echo $entries[$n]['itemtarget']; ?></td> <?php for( $i = 0; $i < count($entries[$n]['dates']); $i++) :?> <td><?php echo $entries[$n]['dates'][$i]->result; ?></td> <?php endfor; ?> </tr> <?php endfor; ?> </tbody> </table> table format query result - issue record 1 , record 2 have both results being displayed. record 1 has result recorded on 11/7/14 , record 2 has result recorded on 7/11/14:
+-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ |benchmark item | target |01-03-2014|01-10-2014|01-17-2014|01-24-2014|01-31-2014|02-07-2014|02-14-2014|02-21-2014|02-28-2014|03-07-2014|03-14-2014|03-21-2014|03-28-2014|04-04-2014|04-11-2014|04-18-2014|04-25-2014|05-02-2014|05-09-2014|05-16-2014|05-23-2014|05-30-2014|06-06-2014|06-13-2014|06-20-2014|06-27-2014|07-04-2014|07-11-2014|07-18-2014|07-25-2014|08-01-2014|08-08-2014|08-15-2014|08-22-2014|08-29-2014|09-05-2014|09-12-2014|09-19-2014|09-26-2014|10-03-2014|10-10-2014|10-17-2014|10-24-2014|10-31-2014|11-07-2014|11-14-2014|11-21-2014|11-28-2014|12-05-2014|12-12-2014|12-19-2014|12-26-2014| +-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ |total no. of calls conducted 17.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 **2.00** 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 **12.00** 0.00 0.00 0.00 0.00 0.00 0.00 0.00 | +-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ |total no. of random visits 29.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 **2.00** 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 **12.00** 0.00 0.00 0.00 0.00 0.00 0.00 0.00 | +-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
Comments
Post a Comment