title: Combining MYSQL queries in php view
body: <p>I was given the task of making a web app to replace an excel booking sheet that was constantly locked due to growing number of users. The booking database (of about a hundred possible assets) looks something like this:</p>
  
  <pre><code>+--------------------+---------------+------+-----+---------+-------+
  | Field              | Type          | Null | Key | Default | Extra |
  +--------------------+---------------+------+-----+---------+-------+
  | ID                 | int(11)       | NO   | PRI | 0       |       |
  | bookedDate         | date          | YES  |     | NULL    |       |
  | bookedBy           | varchar(256)  | YES  |     | NULL    |       |
  | startDate          | date          | YES  |     | NULL    |       |
  | endDate            | date          | YES  |     | NULL    |       |
  | Equipment1         | varchar(512)  | YES  |     | NULL    |       |
  | Equipment2         | varchar(512)  | YES  |     | NULL    |       |
  | ...                |               |      |     |         |       |
  | Equipment15        | varchar(512)  | YES  |     | NULL    |       |
  +--------------------+---------------+------+-----+---------+-------+
  </code></pre>
  
  <p>So users can book out up to 15 assets in a booking for a period of 1d-1Y.</p>
  
  <p>On my php page the display view (only booked assets are shown) looks something like this:</p>
  
  <pre><code>        &lt;&lt;&lt; &lt;&lt; &lt; Prev week  Bookings for week 49 - 2014 Next week &gt; &gt;&gt; &gt;&gt;&gt;
        Monday     Tuesday    Wednesday   Thursday  Friday     Saturday    Sunday
       01/12/2014 02/12/2014 03/12/2014 04/12/2014 05/12/2014 06/12/2014 07/12/2014
  ---------------------------------------------------------------------------------
  Eq1  | fred                      tom      tom     tom                    harry
  Eq4  | tom        tom                                        frank
  Eq66 |                                                       tom          tom
  ...
  Eq832|                          harry
  </code></pre>
  
  <p>This all works but the page is very slow. 20-30 sec to generate a page with ~50 assets
  The code to pick which assets are displayed (takes about 3 sec) is:</p>
  
  <pre><code>function read_Equipment($sDate,$eDate){
      $query = mysql_query("SELECT Equipment1 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment2 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment3 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment4 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment5 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment6 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment7 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment8 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment9 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment10 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment11 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment12 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment13 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment14 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' union 
          SELECT Equipment15 FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;'$eDate' 
          order by Equipment1")or die();
      while($ad= mysql_fetch_array($query)){
          if($ad['Equipment1'] != "")
          {
              $rdata[$ad['Equipment1']] = $ad['Equipment1'];
          }
      }
      return($rdata);
  }
  </code></pre>
  
  <p>And getting each day code (takes about 120ms, and around 20s in total) looks like this</p>
  
  <pre><code>function read_booking($equipment,$sDate,$eDate){
      $query = mysql_query("SELECT * FROM oldDB WHERE startDate&lt;'$sDate' AND endDate&gt;='$eDate' AND (
          Equipment1 = '$equipment' OR 
          Equipment2 = '$equipment' OR 
          Equipment3 = '$equipment' OR 
          Equipment4 = '$equipment' OR 
          Equipment5 = '$equipment' OR 
          Equipment6 = '$equipment' OR 
          Equipment7 = '$equipment' OR 
          Equipment8 = '$equipment' OR 
          Equipment9 = '$equipment' OR 
          Equipment10 = '$equipment' OR 
          Equipment11 = '$equipment' OR 
          Equipment12 = '$equipment' OR 
          Equipment13 = '$equipment' OR 
          Equipment14 = '$equipment' OR 
          Equipment15 = '$equipment'
          )")or die();
      $rdata = "";
      while($ad = mysql_fetch_array($query)){
          if($ad != "")
          {
              $rdata += $ad['ID']." - ";
          }
      }
      return($rdata);
  }
  </code></pre>
  
  <p>The code for putting it all together looks like this</p>
  
  <pre><code>//$time_i = time at start of the week
  $tepp = read_Equipment($sDate,$eDate);
  foreach($tepp as $time){
      echo '&lt;tr&gt;&lt;th class="booking_time_th"&gt;' . $time . '&lt;/th&gt;';
      $i = 0;
      while($i &lt; 7){
          $i++;
          $msc=microtime(true);
          $strtmp = read_booking($time,date('Y-m-d', $time_i+($i)*24*3600),date('Y-m-d', $time_i+($i-1)*24*3600));
          echo '&lt;td&gt;'.(microtime(true)-$msc).'s&lt;div class="booking_time_div"&gt;
          &lt;div class="booking_time_cell_div" id="div:' . $strtmp . '" onclick="void(0)"&gt;' . $strtmp. '&lt;/div&gt;&lt;/div&gt;&lt;/td&gt;';
      }
      echo '&lt;/tr&gt;';
  }
  echo '&lt;/table&gt;';
  </code></pre>
  
  <p>The microtimes are just there to track the progress and dont affect the total length all that much. </p>
  
  <p>So my question is how can I better combine the sql queries so that there are not hundred of little queries (hundreds at 0.1ms total 20-30s)?
  Should I be rearranging the database better?</p>