title: Finding ocuppations via SQL and/or PHP
body: <p>I am making a student web app. Amongst other tables, I have a table in which students enroll and enrollments are between two dates.</p>
  <p>This app uses MySQL 5.6 and PHP 7.2
  It has the following fields:</p>
  <ul>
  <li><strong>IDStudent</strong></li>
  <li><strong>StartDate</strong></li>
  <li><strong>EndDate</strong></li>
  <li><strong>IDCourse</strong></li>
  </ul>
  <p>Each course has a maximum capacity in which it cannot be surpassed.</p>
  <p>I want to know, given a <strong>start date</strong>, <strong>end date</strong> and <strong>IDCourse</strong>, how many concurrent students are in a course. I get an approxiumate value just counting rows between two dates</p>
  <pre><code>SELECT COUNT(*) FROM enrollments
      WHERE IDCourse = ? 
      AND (
        (StartDate BETWEEN &quot;&lt;start date&gt;&quot; AND &quot;&lt;end date&gt;&quot;)
        OR
        (EndDate BETWEEN &quot;&lt;start date&gt;&quot; AND &quot;&lt;end date&gt;&quot;)
        OR
        (StartDate &lt;= &quot;&lt;start date&gt;&quot; AND EndDate&gt;= &quot;&lt;end date&gt;&quot;)
      )
  </code></pre>
  <p>But that doesn't take account non overlapping ranges. It counts every enrollment.</p>
  <p>For example, I have this very simple case:</p>
  <p>Want to find how many students are enrolled between <strong>01/01/2021</strong> and <strong>05/01/2021</strong> at a specified course</p>
  <p>And I have those 3 enrollments on that course:</p>
  <ol>
  <li>01/01/2021 - 02/01/2021</li>
  <li>03/01/2021 - 04/01/2021</li>
  <li>20/12/2020 - 01/02/2021</li>
  </ol>
  <p>I should get <strong>2</strong> count and <strong>not</strong> <strong>3</strong>, because 1 and 2 don't overlap while 3 overlaps both.
  I tried to search online but I didn't found something similar, maybe I am not using the correct keywords!
  I found <a href="https://stackoverflow.com/questions/53720067/determine-max-number-of-overlapping-datetime-ranges">Determine max number of overlapping DATETIME ranges</a> but that is for MySQL 8</p>
  <p>Many thanks for your help
  Regards</p>