Day Planner

[eluser]PixelPanic[/eluser]
I'm coding a Day Planner for my Office Staff to help organize jobs as it will be on a Big TV
It only needs to show 5 days work (Mon-Fri) AND does not need to be continuous in days. Just a static Mon-Fri and data will be changed as and when needed etc. Hope that makes sense.

Each day is split into AM/PM and should allow for multi events for each staff member in AM/PM (EG Two events for a user in AM)

But I have run into a problem. I have the data outputting BUT it's not exporting multi events per AM/PM etc. It's only doing it for one event on each AM/PM etc

Example is in the SQL "This is a double test"

My SQL

Code:

CREATE TABLE IF NOT EXISTS `Planner` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Staff_ID` int(11) NOT NULL,
  `Day` varchar(2) NOT NULL,
  `event` text NOT NULL,
  `time` varchar(5) NOT NULL DEFAULT 'AM',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `Planner` (`ID`, `Staff_ID`, `Day`, `event`, `time`) VALUES
(1, 1, '1', 'Testing', 'AM'),
(2, 2, '1', 'Testing This', 'AM'),
(3, 2, '2', 'Testing Day 2', 'AM'),
(4, 2, '2', 'This is a double test', 'AM');

CREATE TABLE IF NOT EXISTS `Staff` (
  `Staff_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Tier` int(1) NOT NULL,
  `Email` varchar(100) NOT NULL,
  PRIMARY KEY (`Staff_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `Staff` (`Staff_ID`, `Name`, `Tier`, `Email`) VALUES
(1, 'Daniel', 1, ''),
(2, 'Test User', 1, '');

In my Controller all i have is

Code:

$data['staff'] = $this->db->get('Staff');

and ofcourse the standard view output

In my view

Code:

<div class="datagrid">
<pre>
</pre>
  <table>
    <thead>
      <tr>
        <th></th>
        <th>Monday</th>
        <th>Tuesday</th>
        <th>Wednesday</th>
        <th>Thursday</th>
        <th>Friday</th>
      </tr>
    </thead>
    <tbody>
    &lt;?php foreach ($staff->result() as $row): ?&gt;
      <tr>
        <td>&lt;?= $row->Name;?&gt;</td>
        <td><table>
         &lt;?php $day1a = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','1')->where('time','AM')->get('Planner')->row(); ?&gt;
         &lt;?php $day1p = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','1')->where('time','PM')->get('Planner')->row(); ?&gt;
            <tr>
              <td  none;">AM: &lt;?=@$day1a->event;?&gt;</td>
            </tr>
            <tr>
              <td  none;">PM: &lt;?=@$day1p->event;?&gt;</td>
            </tr>
          </table></td>
        <td><table>
         &lt;?php $day2a = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','2')->where('time','AM')->get('Planner')->row(); ?&gt;
         &lt;?php $day2p = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','2')->where('time','PM')->get('Planner')->row(); ?&gt;
            <tr>
              <td  none;">AM: &lt;?=@$day2a->event;?&gt;</td>
            </tr>
            <tr>
              <td  none;">PM: &lt;?=@$day2p->event;?&gt;</td>
            </tr>
          </table></td>
        <td><table>
         &lt;?php $day3a = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','3')->where('time','AM')->get('Planner')->row(); ?&gt;
         &lt;?php $day3p = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','3')->where('time','PM')->get('Planner')->row(); ?&gt;
            <tr>
              <td  none;">AM: &lt;?=@$day3a->event;?&gt;</td>
            </tr>
            <tr>
              <td  none;">PM: &lt;?=@$day3p->event;?&gt;</td>
            </tr>
          </table></td>
        <td><table>
         &lt;?php $day4a = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','4')->where('time','AM')->get('Planner')->row(); ?&gt;
         &lt;?php $day4p = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','4')->where('time','PM')->get('Planner')->row(); ?&gt;
            <tr>
              <td  none;">AM: &lt;?=@$day4a->event;?&gt;</td>
            </tr>
            <tr>
              <td  none;">PM: &lt;?=@$day4p->event;?&gt;</td>
            </tr>
          </table></td>
        <td><table>
         &lt;?php $day5a = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','5')->where('time','AM')->get('Planner')->row(); ?&gt;
         &lt;?php $day5p = $this->db->where('Staff_ID', $row->Staff_ID)->where('Day','5')->where('time','PM')->get('Planner')->row(); ?&gt;
            <tr>
              <td  none;">AM: &lt;?=@$day5a->event;?&gt;</td>
            </tr>
            <tr>
              <td  none;">PM: &lt;?=@$day5p->event;?&gt;</td>
            </tr>
          </table></td>
      </tr>
      &lt;?php endforeach; ?&gt;
    </tbody>
  </table>
</div>

If this is not correct would someone please mind helping and showing me please, as I have now reached a dead end. It's doing my head in xD

[eluser]CroNiX[/eluser]
Wow, you should really consider refactoring this so that you only need 1 query to retrieve all data for the week instead of 10.

That being said, I think your problem is because all of those queries in the view (queries shouldn't be in a view, pass data from controller TO the view) are only retrieving a single row(), so, it's only retrieving a single result. Use result() to retrieve multiple rows. Then you'd have to loop over them to display multiple events.

[eluser]PixelPanic[/eluser]
Would you be able to do a quick example? Only reason I ask is because I'm now stuck and this was the only way i could kind of get it working.

[eluser]CroNiX[/eluser]
Something like this:

Code:

$q = $this->db
  ->select('planner.day, planner.event, planner.time, staff.Tier, staff.Name')
  ->join('planner', 'staff.Staff_ID = planner.Staff_ID')
  ->where('planner.day BETWEEN 1 AND 5')
  ->order_by('planner.day', 'asc')
  ->order_by('planner.time', 'asc')
  ->order_by('staff.name', 'asc')
  ->get('staff')
  ->result_array();

Then as you're looping through them, check to see if day changes. If it does, start a new tr. And adjust the where() to get the days you need.