phpmyadmin Question posted in
The official documentation can be found here.

Date record from MYSQL to display in a html table based from the dates Month – Phpmyadmin

I have a table in mysql with 4 columns. 2 VARCHAR fields and 1 date field with NULL field to “Yes” and the default field “NULL” in phpmyadmin. I want to be able to display on a html table that has 12 columns one for each month (eg Jan,feb etc) that date Month in the MYSQL table to the correct month column. For example if my date field has 12/8/2019 i want that to be displayed on the August Column.

I have tried using a date field for every month and the use a form with 12 date pickers but i found that i could not have any empty date fields.

I do not know what code to use to display the date month in a month column but this is the html

<table class="table table-bordered table-sm small">
  <thead class="thead-dark">
  <tr>
      <th>Owner</th>
      <th>Jan</th>
      <th>Feb</th>
      <th>Mar</th>
      <th>T1 Break</th>
      <th>Apr</th>
      <th>May</th>
      <th>Jun</th>
      <th>T2 Break</th>
      <th>Jul</th>
      <th>Aug</th>
      <th>Sep</th>
      <th>T3 Break</th>
      <th>Oct</th>
      <th>Nov</th>
      <th>Dec</th>
      <th>T4 Break</th>
  </thead>
  </tr>
</Table>

For example if my date field has 12/8/2019 i want that to be displayed on the August Column.

<?php
require 'db.php';
$sqlview="SELECT * FROM booking";
$myData = mysqli_query($con, $sqlview);
while($viewrecord = mysqli_fetch_array($myData))
  {
echo "<form action=overseebooking.php method=POST>";
      echo "<tr>";
      echo "<td>" . $viewrecord['booking_owner'] . " </td>";
      echo "<td>" . $viewrecord['booking_jan'] . " </td>";
      echo "<td>" . $viewrecord['booking_feb'] . " </td>";
      echo "<td>" . $viewrecord['booking_march'] . " </td>";
      echo "<td>" . $viewrecord['booking_t1hols'] . " </td>";
      echo "<td>" . $viewrecord['booking_april'] . " </td>";
      echo "<td>" . $viewrecord['booking_may'] . " </td>";
      echo "<td>" . $viewrecord['booking_june'] . " </td>";
      echo "<td>" . $viewrecord['booking_t2hols'] . " </td>";
      echo "<td>" . $viewrecord['booking_july'] . " </td>";
      echo "<td>" . $viewrecord['booking_august'] . " </td>";
      echo "<td>" . $viewrecord['booking_september'] . " </td>";
      echo "<td>" . $viewrecord['booking_t3hols'] . " </td>";
      echo "<td>" . $viewrecord['booking_oct'] . " </td>";
      echo "<td>" . $viewrecord['booking_nov'] . " </td>";
      echo "<td>" . $viewrecord['booking_dec'] . " </td>";
      echo "<td>" . $viewrecord['booking_t4hols'] . " </td>";
      echo "<td>" . "<input type=submit class=form-control name=update value=update" . " </td>";
echo "</form>";
    }

    ?>
Tags:

2

Answers


  1. 0

    I don’t know exactly how you are getting the data back but here is some idea:

    Loop thru all results, get the number of the month number if this date, for each result add a tr in each tr add 12 td‘s for each month column, when $i in the loop is === to your month number, add it to $html

    See code example:

    <table class="table table-bordered table-sm small">
  <thead class="thead-dark">
  <tr>
      <th>Jan</th>
      <th>Feb</th>
      <th>Mar</th>
      <th>Apr</th>
      <th>May</th>
      <th>Jun</th>
      <th>Jul</th>
      <th>Aug</th>
      <th>Sep</th>
      <th>Oct</th>
      <th>Nov</th>
      <th>Dec</th>
  </thead>
  </tr>
<?php 
$html = "";
require 'db.php';
$sqlview="SELECT * FROM booking";
$myData = mysqli_query($con, $sqlview);
//loop thru the dates (in your case the SQL results)
while($viewrecord = mysqli_fetch_array($myData)){
    //get the month number (Jan = 1, Feb = 2 etc.)
    $monthNumber = date_parse_from_format("y-m-d", $viewrecord['date'])['month'];
    //return A tr for each result
    $html .= '<tr>';
    //a td for each month
    for($i = 0; $i < 12; $i++){
        $html .= '<td>';
        //if column is current month, display date
        $html .= ($monthNumber === ($i+1)) ? $viewrecord['date'] : "";
        $html .= '</td>';
    }
    $html .= '<tr>';
}
echo $html;
?>
<table>
    Login or Signup to reply.
  2. 0

    Assuming you are using PDO to query your data and you already selected all records,
    all you need to do is to process the main query result with PHP normally like any array like below:

    1- Your query:

    $data = $pdo->query("SELECT * FROM TABLE")->fetchAll();

    2- Creating month array (Put all the months you want):

    $myMonth = array('January' => 'Jan','February' => 'Feb','March' => 'Mar');

    3- Create arrays for each month based on $data

    $January = array_filter($data ,function($val){
    if (in_array($val["Jan"], $myMonth)) {
        return $val;
    }
});

    I didn’t actually test the code above but the important thing is that I hope you got the idea.

    After repeating that for each month, you will end up with 12 separate arrays includes all records you need for each one of them, then you can loop through them in your table into each month.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search