Styjun

I want to dynamically generate an HTML table based on MySql data, and the user would be able to update the table directly on the page (through JQuery and Ajax).
Week rows will be inserted for the entire year all at once in table schedule.
Each week has no more than 2 users assigned to.
A user can be removed at any time and replaced with another user.
The first table column of each row contains all the users and then each column represents a week from tuesday to tuesday for the entire year. Expected HTML result :

+-------+--------------------------+--------------------------+--------------------------+
| Users | 2019-03-19 to 2019-03-26 | 2019-03-26 to 2019-04-02 | 2019-04-02 to 2019-04-09 |
+-------+--------------------------+--------------------------+--------------------------+
| 1 | | | |
+-------+--------------------------+--------------------------+--------------------------+
| 2 | X | X | |
+-------+--------------------------+--------------------------+--------------------------+
| 3 | X | | |
+-------+--------------------------+--------------------------+--------------------------+
| 4 | | | |
+-------+--------------------------+--------------------------+--------------------------+
| 5 | | X | |
+-------+--------------------------+--------------------------+--------------------------+

Schema :
table schedule

+-------------+------------+------------+------------+------------+
| schedule_id | user1 | user2 | dateStart | dateEnd |
| PK AUTO_INC | FK int(11) | FK int(11) | date | date |
+-------------+------------+------------+------------+------------+
| 1 | 3 | 2 | 2019-03-19 | 2019-03-26 |
+-------------+------------+------------+------------+------------+
| 2 | 5 | 2 | 2019-03-26 | 2019-04-02 |
+-------------+------------+------------+------------+------------+
| 3 | null | null | 2019-04-02 | 2019-04-09 |
+-------------+------------+------------+------------+------------+

Users are gradually being assigned. The user1 and user2 columns allow for null and later on someone can enter the users for a particular week.

I need to check with PHP if one or two users are already assigned, then someone can update a row with either a user id or null (removing a user).
For this purpose I use variables $set1 and $set2 and data-attributes (for later use with jQuery) telling me which week has a user in user1 or user2.

I can echo the table <th> with all the necessary data.
Now my question is how to echo the table <td>. I need to find a way to generete <tr> for each users and <td> for each <th> while matching the user id value.

Therefore, if the week inserted in <th> has user 2 assigned, I will add an 'X' to the <td> below that <th> on user 2 row (see expected result above).

I was thinking myabe I can run another MySql query and while loop to echo by users and order by dates?

My code so far :

// Connect to database decyb
include 'dbconnect.php';

// MySQL query
$getUpdateDispoEnq = "
SELECT dispo_enq.*,
u1.firstName as enq1First, 
u1.lastName as enq1Last, 
u2.firstName as enq2First, 
u2.lastName as enq2Last
FROM dispo_enq
JOIN users u1 ON dispo_enq.userEnq1 = u1.user_id
JOIN users u2 ON dispo_enq.userEnq2 = u2.user_id;"; 

 // Prepared statements
 $stmt = mysqli_stmt_init($conn);
 if (!mysqli_stmt_prepare($stmt, $getUpdateDispoEnq))
 echo '<div class="red-text" style="margin-top: 1em">SQL error!</div>';
 exit;
 else 

 mysqli_stmt_execute($stmt);

 $results = mysqli_stmt_get_result($stmt);
 $resultCheck = mysqli_num_rows($results);

 $week = 0;

 echo '<table class="dispo-enq">';
 echo '<tr>';
 echo '<th class="regular"><div><span>Enquêteurs</span></div></th>';
 echo '<th class="regular"><div><span>Cellulaire</span></div></th>';

 if ($resultCheck > 0) 

 while ($row = mysqli_fetch_assoc($results)) 
 $enq1 = $row['userEnq1'];
 $enq2 = $row['userEnq2'];
 $dateStart = $row['dateStart'];
 $dateEnd = $row['dateEnd'];
 $week++;

 // Check if users are set
 if ($enq1 > 0)
 $set1 = 1;
 else 
 $set1 = 0;
 

 if ($enq2 > 0)
 $set2 = 1;
 else 
 $set2 = 0;
 

 // Display table headers
 echo '<th class="rotate" data-week="'.$week.'" data-enq1="'.$set1.'" data-enq2="'.$set2.'"><div><span>'.$dateStart.' to '.$dateEnd.'</span></div></th>';
 

 

 echo '</tr>';
 echo '</table>';
 exit;
 

You have a task similar to mine in a way but since I can get the code I used there I decided to simply post what I can get out of my brain under such a short notice.

// MySQL query
$getUpdateDispoEnq = "
SELECT dispo_enq.*, u1.firstName as enq1First, u1.lastName as enq1Last, u2.firstName as enq2First, u2.lastName as enq2Last
FROM dispo_enq
JOIN users u1 ON dispo_enq.userEnq1 = u1.user_id
JOIN users u2 ON dispo_enq.userEnq2 = u2.user_id;"; 

$html = '';
// Prepared statements
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $getUpdateDispoEnq))
 $html .= '<div class="red-text" style="margin-top: 1em">SQL error!</div>';
 exit;
 else 
 mysqli_stmt_execute($stmt);

 $results = mysqli_stmt_get_result($stmt);
 $resultCheck = mysqli_num_rows($results);

 $week = $set1 = $set2 = 0;
 $dateStart = $dateEnd = '';
 $html .= '<table class="dispo-enq"><tr>';
 $html .= '<th class="regular"><div><span>Enquêteurs</span></div></th>';
 $html .= '<th class="regular"><div><span>Cellulaire</span></div></th>';

 if ($resultCheck > 0) 
 while ($row = mysqli_fetch_assoc($results)) 
 $enq1 = $row['userEnq1'];
 $enq2 = $row['userEnq2'];
 $dateStart = $row['dateStart'];
 $dateEnd = $row['dateEnd'];
 $week++;

 // Check if users are set
 if ($enq1 > 0) $set1 = 1; 
 if ($enq2 > 0) $set2 = 1;

 // Display table headers
 $html .= '<th class="rotate" data-week="'.$week.'" data-enq1="'.$set1.'" data-enq2="'.$set2.'">';
 $html .= '<div><span>'.$dateStart.' to '.$dateEnd.'</span></div></th>';
 

 
 $html .= '</tr>';

 $result = mysql_query("SELECT firstName, lastName FROM users");
 while ($rows = mysql_fetch_assoc($result)) 
 $html .= '<tr>';
 $html = '<td>'.$week.'</td>';
 foreach ($rows as $row) $html .= '<td>'.$row.'</td>';
 $html = '<td>'.$dateStart.'</td>';
 $html = '<td>'.$dateEnd.'</td>';
 $html .= '</tr>';
 

 $html .= '</table>';
 exit;


return $html;