title: Optimising table structure for Monday period 1-9
body: <p>What would be the best way to optimise the below table?</p>
  
  <pre><code>// Monday period 1
  
  echo '
  &lt;td class="gridSide"&gt;Mon&lt;/td&gt;
  &lt;td class="box" id="grid2_mon1"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 1
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 2
  
  echo '
  &lt;td class="box" id="grid2_mon2"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 2
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 3
  
  echo '
  &lt;td class="box" id="grid2_mon3"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 3
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 4
  
  echo 
  '&lt;td class="box" id="grid2_mon4"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 4
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 5
  
  echo '
  &lt;td class="box" id="grid2_mon5"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 5
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 6
  
  echo '
  &lt;td class="box" id="grid2_mon6"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 6
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 7
  
  echo '
  &lt;td class="box" id="grid2_mon7"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 7
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 8
  
  echo '
  &lt;td class="box" id="grid2_mon8"&gt;'; 
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 2
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  
  // Monday period 9
  
  echo '
  &lt;td class="box" id="grid2_mon9"&gt;';
  $sql = "
  SELECT 
    COUNT(*) totalCount 
  FROM 
    ts_room 
  WHERE 
    NOT EXISTS (
      SELECT 1
      FROM ts_roompref 
        JOIN ts_request 
        ON ts_roompref.request_id = ts_request.roompref_id
        AND day_id = 1 
        AND period_id = 2
      WHERE 
        ts_room.id = ts_roompref.room_id)
  ";
  $stm = $pdo-&gt;prepare( $sql );
  $stm-&gt;execute( array( ':room_id' =&gt; $room) );
  $rows = $stm-&gt;fetchColumn();
  echo $rows.'&lt;br&gt;free';
  echo '&lt;/td&gt;';
  }
  </code></pre>