How to extract date from database using php

Question

I'm trying to create a weekly work schedule. When I try to select the shifts from the database my date value is selecting the current time but the date (Y-m-d) from the table.

This is my code:

while ($row = mysqli_fetch_array($res))
{
          $dt = new DateTime();
          $dt->setISODate($year, $week);

          $fetch_mID = $row['ID_EMPLOYEE'];
          $fetch_fn = $row['Firstname'];
          $fetch_en = $row['Lastname'];
          echo "<tr>";
            echo "<td>" . $fetch_fn . " " . $fetch_en . "</td>";

            do {
              $obj = new ReflectionObject($dt);
              $pro = $obj->getProperty('date');
              $date = $pro->getValue($dt);
              echo $date; //Output = 'Y-m-d H:m:s'

              $shift = $conn->query("SELECT ShiftDate, ShiftStart, ShiftEnd FROM shifts WHERE ID_EMPLOYEE = '$fetch_mID' AND Date(ShiftDate) = '$date'");
              $fetch = mysqli_fetch_array($shift);
              $dt->modify('+1 day');
            } while ($week == $dt->format('W'));

I would expect the $date to output Y-m-d but it outputs Y-m-d H:m:s. And the time is the current time.

can you check what is the type of $date ? using gettype($date) — Yassine CHABLI
– Yassine CHABLI, Commented Apr 5, 2019 at 17:19
Warning: You are wide open to SQL Injections and should really use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. — Dharman
– Dharman ♦, Commented Apr 5, 2019 at 17:39
So, why $dt = new DateTime(); $obj = new ReflectionObject($dt); $pro = $obj->getProperty('date'); $date = $pro->getValue($dt); echo $date; instead of just $date = $dt->format("Y-m-d");? — miken32
– miken32, Commented Apr 5, 2019 at 17:51
@miken32, It is faster than format(). Especially since it is in a loop. — Simon E
– Simon E, Commented Apr 5, 2019 at 18:16

Yassine CHABLI · Accepted Answer · 2019-04-05 17:32:41Z

1

The solution is to convert $date to date object and then format the date with the new format Y-m-d.

  $time = strtotime($date);
  $newformat = date('Y-m-d',$time);
  echo $newformat; // output 2019-04-05

You new code seems to b something like :

while ($row = mysqli_fetch_array($res))
{
          $dt = new DateTime();
          $dt->setISODate($year, $week);

          $fetch_mID = $row['ID_EMPLOYEE'];
          $fetch_fn = $row['Firstname'];
          $fetch_en = $row['Lastname'];
          echo "<tr>";
            echo "<td>" . $fetch_fn . " " . $fetch_en . "</td>";

            do {
              $obj = new ReflectionObject($dt);
              $pro = $obj->getProperty('date');
              $date = $pro->getValue($dt);
              //new code
              $newformat = date('Y-m-d',strtotime($date));
              echo $newformat; // output 2019-04-05
              // end new code
              $shift = $conn->query("SELECT ShiftDate, ShiftStart, ShiftEnd FROM shifts WHERE ID_EMPLOYEE = '$fetch_mID' AND Date(ShiftDate) = '$date'");
              $fetch = mysqli_fetch_array($shift);
              $dt->modify('+1 day');
            } while ($week == $dt->format('W'));

edited Apr 5, 2019 at 17:32

answered Apr 5, 2019 at 17:28

Yassine CHABLI

3,7822 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anuga Over a year ago

Might wanna remove the extra ; in date('Y-m-d',strtotime($date);); = date('Y-m-d',strtotime($date));

Simon E Over a year ago

Thank you. The output is now the correct format but it still doesn't select the values from the table.

Yassine CHABLI Over a year ago

what is the type of ShiftDate in the db ?

Simon E Over a year ago

Nevermind. Just had to change the $date in the select to $newformat. You have my love. Many thanks.

Dharman Over a year ago

Use DateTime class

|

Collectives™ on Stack Overflow

How to extract date from database using php

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related