Connecting to all tables and display in html table

#1

Here is the challenge …
I have set up a maintenance site, i have created a page with a html table which is an overview of the whole maintenance logs. Along the side is a list of the logs which are fire alarm test, fire door test, showerhead temperature hot & cold, health & safety and vacant rooms flush and along the top is a list of week numbers (1-52)

I have a database called mldemo and in that database is the following tables:
firesystem, firdoors, weekly, hsechecks and flush.

Each table has a row date (name date, type date)

So my table links are:

fire alarm test (database table firesystem)
fire door test (database table firesdoors)
showerhead temperature hot & cold (database table weekly)
health & safety (database table hsechecks)
vacant rooms flush(database table flush)

I also have 2 images.
yestick.jpg
notick.jpg

Here is what i am trying to do.

Connect to all the tables in mldemo in one go !

Convert the date format to weeknumber !

select a query from each table relevant to the log.

For example if a user fills out the form to submit the fire alarm test and selects the date January 5th 2023 then the firesystem table, date row in the database will show 2023-01-05 This needs to be converted into week1 and then because that row has data, in the html table under fire alarm test under week 1 it will display the yestick.jpg. If the date is empty then in the html table under fire alarm week 1 it will display the notick.jpg

same for all others so

if a user fills out the form to submit the health & safety form and selects the date April 10th 2023 then the table hsechecks, date row in the database will show 2023-04-10 This needs to be converted into week 15 and then because that row has data, in the html table under health & safety week 15 it will display the yestick.jpg. If the date is empty it will display the notick.jpg

I hope it allmakes sense just need to connect to all tables in one go and check each table cell with the database to see if it has data or not. If it has data then shows the yestick.jpg if it has no data then shows the notick.jpg.

Here is my page i set up for the html table.

<?php

include('../inc/config.php');

include(ROOT_PATH . 'inc/database2.php');




?>



<!DOCTYPE html>

<html>

    <head>

        <title>Danforth Care</title>

        <meta charset="utf-8">

        <meta name="viewport" content="width=device-width, initial-scale=1" />

        <link rel="stylesheet" href="<?php echo BASE_URL; ?>css/normalize.css">

        <link rel="stylesheet" href="<?php echo BASE_URL; ?>css/main.css">

        <link rel="stylesheet" href="<?php echo BASE_URL; ?>css/captcha.css">

        <link rel='stylesheet' href='https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css'>

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>



        <style>

            #wrapper h1 {

                text-align:center;

            }

            #wrapper form {

                margin:0 auto;

            }

            input[type="file"] {

                padding-left:0;

            }

            table {

                font-family: Arial, Helvetica, sans-serif;

                border-collapse: collapse;

                width: 100%;

            }



            table td, table th {

                border: 1px solid #ddd;

                padding: 8px;

            }



            table tr:nth-child(even) {

                background-color: #f2f2f2;

            }



            table th {

                padding-top: 12px;

                padding-bottom: 12px;

                text-align: left;

                background-color:#0066ff;

                color: white;

            }

            table a {

                color:blue;

            }

        </style>

<style>







#link {



color: #0000FF !important; /** or whatever hex color code you want **/



}







</style>



<style>



.responsive {



  width: 60%;



  max-width: 300px;



  height: auto;



margin: 10px;



}</style>





<style>

    

    

    table.customTable {

  width: 100%;

  background-color: #FFFFFF;

  border-collapse: collapse;

  border-width: 2px;

  border-color: #7EA8F8;

  border-style: solid;

  color: #000000;

}



table.customTable td, table.customTable th {

  border-width: 2px;

  border-color: #7EA8F8;

  border-style: solid;

  padding: 5px;

}



table.customTable thead {

  background-color: #5976AD;

}



</style>



    </head>

    <body>

        <div id="wrapper">





<p><center><img src="/img/log1logo.png" width="350"></center></p>



<p><center><h2>Danforth Care</h2></center></p>

<p><center><h2>Weekly Maintenance Logs</h2></center></p>

<p><center>Overview Page<br>
Logs Down Side, Week Numbers Across Top<br>On Mobile Scroll Left To View.</center></p>

<div style="overflow-x:auto;">

<table class="customTable">

  <thead>

    <tr>

      <th>Log</th>

      <th><center>1</center></th>

      <th><center>2</center></th>

      <th><center>3</center></th>

      <th><center>4</center></th>

      <th><center>5</center></th>

      <th><center>6</center></th>

      <th><center>7</center></th>

      <th><center>8</center></th>

      <th><center>9</center></th>

      <th><center>10</center></th>

      <th><center>11</center></th>

      <th><center>12</center></th>

      <th><center>13</center></th>

      <th><center>14</center></th>

      <th><center>15</center></th>

      <th><center>16</center></th>

      <th><center>17</center></th>

      <th><center>18</center></th>

      <th><center>19</center></th>

      <th><center>20</center></th>

      <th><center>21</center></th>

      <th><center>22</center></th>

      <th><center>23</center></th>

      <th><center>24</center></th>

      <th><center>25</center></th>

      <th><center>26</center></th>

      <th><center>27</center></th>

      <th><center>28</center></th>

      <th><center>29</center></th>

      <th><center>30</center></th>

      <th><center>31</center></th>

      <th><center>32</center></th>

      <th><center>33</center></th>

      <th><center>34</center></th>

      <th><center>35</center></th>

      <th><center>36</center></th>

      <th><center>37</center></th>

      <th><center>38</center></th>

      <th><center>39</center></th>

      <th><center>40</center></th>

      <th><center>41</center></th>

      <th><center>42</center></th>

      <th><center>43</center></th>

      <th><center>44</center></th>

      <th><center>45</center></th>

      <th><center>46</center></th>

      <th><center>47</center></th>

      <th><center>48</center></th>

      <th><center>49</center></th>

      <th><center>50</center></th>

      <th><center>51</center></th>

      <th><center>52</center></th>

    </tr>

  </thead>

  <tbody>

    <tr>

      <td bgcolor="#0066FF" style="color:#ffffff">Fire Alarm Test</td>

      <td>Row 1, Cell 2</td>

      <td>Row 1, Cell 3</td>

      <td>Row 1, Cell 4</td>

      <td>Row 1, Cell 5</td>

      <td>Row 1, Cell 6</td>

      <td>Row 1, Cell 7</td>

      <td>Row 1, Cell 8</td>

      <td>Row 1, Cell 9</td>

      <td>Row 1, Cell 10</td>

      <td>Row 1, Cell 11</td>

      <td>Row 1, Cell 12</td>

      <td>Row 1, Cell 13</td>

      <td>Row 1, Cell 14</td>

      <td>Row 1, Cell 15</td>

      <td>Row 1, Cell 16</td>

      <td>Row 1, Cell 17</td>

      <td>Row 1, Cell 18</td>

      <td>Row 1, Cell 19</td>

      <td>Row 1, Cell 20</td>

      <td>Row 1, Cell 21</td>

      <td>Row 1, Cell 22</td>

      <td>Row 1, Cell 23</td>

      <td>Row 1, Cell 24</td>

      <td>Row 1, Cell 25</td>

      <td>Row 1, Cell 26</td>

      <td>Row 1, Cell 27</td>

      <td>Row 1, Cell 28</td>

      <td>Row 1, Cell 29</td>

      <td>Row 1, Cell 30</td>

      <td>Row 1, Cell 31</td>

      <td>Row 1, Cell 32</td>

      <td>Row 1, Cell 33</td>

      <td>Row 1, Cell 34</td>

      <td>Row 1, Cell 35</td>

      <td>Row 1, Cell 36</td>

      <td>Row 1, Cell 37</td>

      <td>Row 1, Cell 38</td>

      <td>Row 1, Cell 39</td>

      <td>Row 1, Cell 40</td>

      <td>Row 1, Cell 41</td>

      <td>Row 1, Cell 42</td>

      <td>Row 1, Cell 43</td>

      <td>Row 1, Cell 44</td>

      <td>Row 1, Cell 45</td>

      <td>Row 1, Cell 46</td>

      <td>Row 1, Cell 47</td>

      <td>Row 1, Cell 48</td>

      <td>Row 1, Cell 49</td>

      <td>Row 1, Cell 50</td>

      <td>Row 1, Cell 51</td>

      <td>Row 1, Cell 52</td>

      <td>Row 1, Cell 53</td>

    </tr>

    <tr>

      <td bgcolor="#0066FF" style="color:#ffffff">Fire Door Test</td>

      <td>Row 2, Cell 2</td>

      <td>Row 2, Cell 3</td>

      <td>Row 2, Cell 4</td>

      <td>Row 2, Cell 5</td>

      <td>Row 2, Cell 6</td>

      <td>Row 2, Cell 7</td>

      <td>Row 2, Cell 8</td>

      <td>Row 2, Cell 9</td>

      <td>Row 2, Cell 10</td>

      <td>Row 2, Cell 11</td>

      <td>Row 2, Cell 12</td>

      <td>Row 2, Cell 13</td>

      <td>Row 2, Cell 14</td>

      <td>Row 2, Cell 15</td>

      <td>Row 2, Cell 16</td>

      <td>Row 2, Cell 17</td>

      <td>Row 2, Cell 18</td>

      <td>Row 2, Cell 19</td>

      <td>Row 2, Cell 20</td>

      <td>Row 2, Cell 21</td>

      <td>Row 2, Cell 22</td>

      <td>Row 2, Cell 23</td>

      <td>Row 2, Cell 24</td>

      <td>Row 2, Cell 25</td>

      <td>Row 2, Cell 26</td>

      <td>Row 2, Cell 27</td>

      <td>Row 2, Cell 28</td>

      <td>Row 2, Cell 29</td>

      <td>Row 2, Cell 30</td>

      <td>Row 2, Cell 31</td>

      <td>Row 2, Cell 32</td>

      <td>Row 2, Cell 33</td>

      <td>Row 2, Cell 34</td>

      <td>Row 2, Cell 35</td>

      <td>Row 2, Cell 36</td>

      <td>Row 2, Cell 37</td>

      <td>Row 2, Cell 38</td>

      <td>Row 2, Cell 39</td>

      <td>Row 2, Cell 40</td>

      <td>Row 2, Cell 41</td>

      <td>Row 2, Cell 42</td>

      <td>Row 2, Cell 43</td>

      <td>Row 2, Cell 44</td>

      <td>Row 2, Cell 45</td>

      <td>Row 2, Cell 46</td>

      <td>Row 2, Cell 47</td>

      <td>Row 2, Cell 48</td>

      <td>Row 2, Cell 49</td>

      <td>Row 2, Cell 50</td>

      <td>Row 2, Cell 51</td>

      <td>Row 2, Cell 52</td>

      <td>Row 2, Cell 53</td>

    </tr>

    <tr>

      <td bgcolor="#0066FF" style="color:#ffffff">Shower Head Temperature Hot & Cold</td>

      <td>Row 3, Cell 2</td>

      <td>Row 3, Cell 3</td>

      <td>Row 3, Cell 4</td>

      <td>Row 3, Cell 5</td>

      <td>Row 3, Cell 6</td>

      <td>Row 3, Cell 7</td>

      <td>Row 3, Cell 8</td>

      <td>Row 3, Cell 9</td>

      <td>Row 3, Cell 10</td>

      <td>Row 3, Cell 11</td>

      <td>Row 3, Cell 12</td>

      <td>Row 3, Cell 13</td>

      <td>Row 3, Cell 14</td>

      <td>Row 3, Cell 15</td>

      <td>Row 3, Cell 16</td>

      <td>Row 3, Cell 17</td>

      <td>Row 3, Cell 18</td>

      <td>Row 3, Cell 19</td>

      <td>Row 3, Cell 20</td>

      <td>Row 3, Cell 21</td>

      <td>Row 3, Cell 22</td>

      <td>Row 3, Cell 23</td>

      <td>Row 3, Cell 24</td>

      <td>Row 3, Cell 25</td>

      <td>Row 3, Cell 26</td>

      <td>Row 3, Cell 27</td>

      <td>Row 3, Cell 28</td>

      <td>Row 3, Cell 29</td>

      <td>Row 3, Cell 30</td>

      <td>Row 3, Cell 31</td>

      <td>Row 3, Cell 32</td>

      <td>Row 3, Cell 33</td>

      <td>Row 3, Cell 34</td>

      <td>Row 3, Cell 35</td>

      <td>Row 3, Cell 36</td>

      <td>Row 3, Cell 37</td>

      <td>Row 3, Cell 38</td>

      <td>Row 3, Cell 39</td>

      <td>Row 3, Cell 40</td>

      <td>Row 3, Cell 41</td>

      <td>Row 3, Cell 42</td>

      <td>Row 3, Cell 43</td>

      <td>Row 3, Cell 44</td>

      <td>Row 3, Cell 45</td>

      <td>Row 3, Cell 46</td>

      <td>Row 3, Cell 47</td>

      <td>Row 3, Cell 48</td>

      <td>Row 3, Cell 49</td>

      <td>Row 3, Cell 50</td>

      <td>Row 3, Cell 51</td>

      <td>Row 3, Cell 52</td>

      <td>Row 3, Cell 53</td>

    </tr>

        <tr>

      <td bgcolor="#0066FF" style="color:#ffffff">Health & Safety</td>

      <td>Row 5, Cell 2</td>

      <td>Row 5, Cell 3</td>

      <td>Row 5, Cell 4</td>

      <td>Row 5, Cell 5</td>

      <td>Row 5, Cell 6</td>

      <td>Row 5, Cell 7</td>

      <td>Row 5, Cell 8</td>

      <td>Row 5, Cell 9</td>

      <td>Row 5, Cell 10</td>

      <td>Row 5, Cell 11</td>

      <td>Row 5, Cell 12</td>

      <td>Row 5, Cell 13</td>

      <td>Row 5, Cell 14</td>

      <td>Row 5, Cell 15</td>

      <td>Row 5, Cell 16</td>

      <td>Row 5, Cell 17</td>

      <td>Row 5, Cell 18</td>

      <td>Row 5, Cell 19</td>

      <td>Row 5, Cell 20</td>

      <td>Row 5, Cell 21</td>

      <td>Row 5, Cell 22</td>

      <td>Row 5, Cell 23</td>

      <td>Row 5, Cell 24</td>

      <td>Row 5, Cell 25</td>

      <td>Row 5, Cell 26</td>

      <td>Row 5, Cell 27</td>

      <td>Row 5, Cell 28</td>

      <td>Row 5, Cell 29</td>

      <td>Row 5, Cell 30</td>

      <td>Row 5, Cell 31</td>

      <td>Row 5, Cell 32</td>

      <td>Row 5, Cell 33</td>

      <td>Row 5, Cell 34</td>

      <td>Row 5, Cell 35</td>

      <td>Row 5, Cell 36</td>

      <td>Row 5, Cell 37</td>

      <td>Row 5, Cell 38</td>

      <td>Row 5, Cell 39</td>

      <td>Row 5, Cell 40</td>

      <td>Row 5, Cell 41</td>

      <td>Row 5, Cell 42</td>

      <td>Row 5, Cell 43</td>

      <td>Row 5, Cell 44</td>

      <td>Row 5, Cell 45</td>

      <td>Row 5, Cell 46</td>

      <td>Row 5, Cell 47</td>

      <td>Row 5, Cell 48</td>

      <td>Row 5, Cell 49</td>

      <td>Row 5, Cell 50</td>

      <td>Row 5, Cell 51</td>

      <td>Row 5, Cell 52</td>

      <td>Row 5, Cell 53</td>

    </tr>

    <tr>

      <td bgcolor="#0066FF" style="color:#ffffff">Vacant Rooms Flush</td>

      <td>Row 6, Cell 2</td>

      <td>Row 6, Cell 3</td>

      <td>Row 6, Cell 4</td>

      <td>Row 6, Cell 5</td>

      <td>Row 6, Cell 6</td>

      <td>Row 6, Cell 7</td>

      <td>Row 6, Cell 8</td>

      <td>Row 6, Cell 9</td>

      <td>Row 6, Cell 10</td>

      <td>Row 6, Cell 11</td>

      <td>Row 6, Cell 12</td>

      <td>Row 6, Cell 13</td>

      <td>Row 6, Cell 14</td>

      <td>Row 6, Cell 15</td>

      <td>Row 6, Cell 16</td>

      <td>Row 6, Cell 17</td>

      <td>Row 6, Cell 18</td>

      <td>Row 6, Cell 19</td>

      <td>Row 6, Cell 20</td>

      <td>Row 6, Cell 21</td>

      <td>Row 6, Cell 22</td>

      <td>Row 6, Cell 23</td>

      <td>Row 6, Cell 24</td>

      <td>Row 6, Cell 25</td>

      <td>Row 6, Cell 26</td>

      <td>Row 6, Cell 27</td>

      <td>Row 6, Cell 28</td>

      <td>Row 6, Cell 29</td>

      <td>Row 6, Cell 30</td>

      <td>Row 6, Cell 31</td>

      <td>Row 6, Cell 32</td>

      <td>Row 6, Cell 33</td>

      <td>Row 6, Cell 34</td>

      <td>Row 6, Cell 35</td>

      <td>Row 6, Cell 36</td>

      <td>Row 6, Cell 37</td>

      <td>Row 6, Cell 38</td>

      <td>Row 6, Cell 39</td>

      <td>Row 6, Cell 40</td>

      <td>Row 6, Cell 41</td>

      <td>Row 6, Cell 42</td>

      <td>Row 6, Cell 43</td>

      <td>Row 6, Cell 44</td>

      <td>Row 6, Cell 45</td>

      <td>Row 6, Cell 46</td>

      <td>Row 6, Cell 47</td>

      <td>Row 6, Cell 48</td>

      <td>Row 6, Cell 49</td>

      <td>Row 6, Cell 50</td>

      <td>Row 6, Cell 51</td>

      <td>Row 6, Cell 52</td>

      <td>Row 6, Cell 53</td>

    </tr>

    
  </tbody>

</table>

</div>

<br><br>








<p><center><img src="/sslogo.png" class="responsive"></center></p>

                    </div>











    </body>

</html>

Here is a image of the table.

tablepic
tablepic1920×594 90.2 KB

Many thanks

#2

You would start by generalizing/normalizing your data. You should have a category table (id, name) that defines the different log entries - fire alarm test, fire door test, showerhead temperature hot & cold, health & safety, and vacant rooms flush. You would then have a maintenance log table (id, category_id, date) that holds the log data.

To get the data needed for the page, you would have a way of selecting the Year you want to display, probably with a default value of the current year. You would then query to get data matching the entered Year. You can get the week number corresponding to the date using MySql’s WEEK(date[,mode]) function. When you fetch the data from the query, index/pivot it using the category_id and the week number as the array indexes.

To produce the output, loop over the category/category_id values, then for each category loop over the range of week numbers. For each categor_id/week number combination, test if there is fetched data. If there is, output the yestick image, if there is not. output the notick image.

Sponsor our Newsletter | Privacy Policy | Terms of Service