Convert user input date in PHP form to MySQL acceptable format

Question

So, I know this type of question has been asked a lot, but I'm super new to MySql and PHP, so forgive me for asking for details.

I'm working on learning them, and I'm modifying a walkthrough I found on YouTube to change it from inputting pizza types, to creating a online event schedule page, where you can both submit new panels into a MySql database, as well as list all the event already in the database.

The submission page is a PHP form, which is connected to the DB. I'm struggling with three of the columns. Panel Date, Panel Start Time, and Panel End Time. Date is a DATE formart in the DB, and the two time columns have a TIME format in the database.

I know the stardard for MySQL is YYYY-MM-DD for DATE, and HH:MM:SS in 24 hour format for time. I have multiple questions about best way/how should this be done, so forgive me for the long list?

1: Would it be better to have the user manually input the date and times, and just use a REGEX to confirm it follows one specific format? I'm assuming just force them to input the date and time following the MySQL format, but I have no clue how to write the RegEx to make sure it's in the correct format, or

2: Should I make it so there is a calendar button they can click on for the date to select the date from a calendar, and drop down lists for the time. I have no clue how to make either of these things take the place of the text box that's already there for input.

On the flip side, I have a couple questions about changing how the date shows on the page where it displays all the panels that are currently in the DB.

3: What kind of statement would I use to convert the date from YYYY-MM-DD to MM-DD-YYYY before it's displayed, and do the same kind of this for the times (convert the from 24 hour to 12 hour with AM or PM after them.

I'm going to include the code I have so far. Like I said before, I'm sure there's a lot of things I could clean up in it, but for right now I'm just trying to get this Date/Time thing fixed.

Here is the home page/where the data that is currently in the database is displayed, and where question 3 applies.

<?php 

  $conn = mysqli_connect('localhost', 'eventeny', 'test1234', 'test_event');

  if(!$conn){
    echo 'Connection error: '. mysqli_connect_error();
  }

  $sql = 'SELECT panelName, panelDescription, panelDate, panelStartTime, panelEndTime, panelModerator, panelist FROM schedule ORDER BY panelDate, panelStartTime, panelName';

  $result = mysqli_query($conn, $sql);

  $fullSchedule = mysqli_fetch_all($result, MYSQLI_ASSOC);

  mysqli_free_result($result);

  mysqli_close($conn);

?>

<!DOCTYPE html>
<html>
  
  <head>
  <title>Test Event 2022</title>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
  <style type="text/css">
    .brand{
      background: #cbb09c !important;
    }
    .brand-text{
      color: #cbb09c !important;
    }
    form{
      max-width: 460px;
      margin: 20px auto;
      padding: 20px;
    }
  </style>
</head>

<body class="grey lighten-4">
  <nav class="white z-depth-0">
    <div class="container">
      <a href="index.php" class="brand-logo brand-text">Event Schedule</a>
      <ul id="nav-mobile" class="right hide-on-small-and-down">
        <li><a href="add.php" class="btn brand z-depth-0">Add a panel</a></li>
      </ul>
    </div>
  </nav>

  <h4 class="center grey-text">Schedule</h4>

  <div class="container">
    <div class="row">

      <?php foreach($fullSchedule as $schedule): ?>

        <div class="col s6 m4">
          <div class="card z-depth-0">
            <div class="card-content center">
              <h6><?php echo htmlspecialchars($schedule['panelName']); ?></h6>
              <div><?php echo htmlspecialchars($schedule['panelDate']); ?></div>
              <div><?php echo htmlspecialchars($schedule['panelStartTime']); ?> - <?php echo htmlspecialchars($schedule['panelEndTime']); ?></div>
            </div>
            <div class="card-action right-align">
              <a class="brand-text" href="details.php?id=<?php echo $pizza['id'] ?>">more info</a>
            </div>
          </div>
        </div>

      <?php endforeach; ?>

    </div>
  </div>

  <footer class="section">
    <div class="center grey-text">&copy; Copyright 2021</div>
  </footer>
</body>

</html>

And here is the page where they will be able input new events into the database. This is where I need the date and time to get looked at, make sure it's in the correct format (whatever that may be), and then save it into the database

<?php

    $conn = mysqli_connect('localhost', 'eventeny', 'test1234', 'test_event');

  if(!$conn){
    echo 'Connection error: '. mysqli_connect_error();
  }

    $panelName = $panelDescription = $panelDate = $panelStartTime = $panelEndTime = $panelModerator = $panelist = '';
    
    $errors = array('panelName'=>'', 'panelDescription'=>'', 'panelDate'=>'', 'panelStartTime'=>'', 'panelEndTime'=> '', 'panelModerator'=>'', 'panelist'=>'');

        if(isset($_POST['submit'])){

        if(empty($_POST['panelName'])){
            $errors['panelName'] = 'A panel name is required.';
        } else{
            $panelName = $_POST['panelName'];
            }

        if(empty($_POST['panelDescription'])){
            $errors['panelDescription'] = 'A panel description is required.';
        } else{
            $panelDescription = $_POST['panelDescription'];
            }

        if(empty($_POST['panelDate'])){
            $errors['panelDate'] = 'The date of the panel is required.';
        } else{
            $panelDate = $_POST['panelDate'];
            }

        if(empty($_POST['panelStartTime'])){
            $errors['panelStartTime'] = 'The starting time of the panel is required.';
        } else{
            $panelStartTime = $_POST['panelStartTime'];
            }

        if(empty($_POST['panelEndTime'])){
            $errors['panelEndTime'] = 'The ending of the panel is required.';
        } else{
            $panelEndTime = $_POST['panelEndTime'];
            }

        if(empty($_POST['panelModerator'])){
            $errors['panelModerator'] = 'The name of the panel moderator is required.';
        } else{
            $panelModerator = $_POST['panelModerator'];
            }

        if(empty($_POST['panelist'])){
            $errors['panelist'] = 'The name(s) of the panelist(s) are required.';
        } else{
            $panelDate = $_POST['panelist'];
            }

        if(!array_filter($errors)){
            $panelName = mysqli_real_escape_string($conn, $_POST['panelName']);
            $panelDescription = mysqli_real_escape_string($conn, $_POST['panelDescription']);
            $panelDate = mysqli_real_escape_string($conn, $_POST['panelDate']);
            $panelStartTime = mysqli_real_escape_string($conn, $_POST['panelStartTime']);
            $panelEndTime = mysqli_real_escape_string($conn, $_POST['panelEndTime']);
            $panelModerator = mysqli_real_escape_string($conn, $_POST['panelModerator']);
            $panelist = mysqli_real_escape_string($conn, $_POST['panelist']);

            $sql = "INSERT INTO schedule(panelName,panelDescription,panelDate,panelStartTime,panelEndTime,panelModerator,panelist) VALUES('$panelName','$panelDescription','$panelDate'.'$panelStartTime','$panelEndTime','$panelModerator','$panelist')";

            if(mysqli_query($conn, $sql)){
                header('Location: add.php');
            } else {
                echo 'query error: '. mysqli_error($conn);
            }

            
        }

    }

?>

<!DOCTYPE html>
<html>
  
  <head>
  <title>Test Event 2022</title>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
  <style type="text/css">
    .brand{
      background: #cbb09c !important;
    }
    .brand-text{
      color: #cbb09c !important;
    }
    form{
      max-width: 460px;
      margin: 20px auto;
      padding: 20px;
    }
  </style>
</head>

<body class="grey lighten-4">
  <nav class="white z-depth-0">
    <div class="container">
      <a href="index.php" class="brand-logo brand-text">Event Schedule</a>
      <ul id="nav-mobile" class="right hide-on-small-and-down">
        <li><a href="add.php" class="btn brand z-depth-0">Add a panel</a></li>
      </ul>
    </div>
  </nav>

    <section class="container grey-text">
        <h4 class="center">Add a Panel</h4>
        <form class="white" action="add.php" method="POST">
            <label>Panel Name</label>
            <input type="text" name="panelName" value="<?php echo htmlspecialchars($panelName) ?>">
            <div class="red-text"><?php echo $errors['panelName']; ?></div>
            <label>Panel Description</label>
            <input type="text" name="panelDescription" value="<?php echo htmlspecialchars($panelDescription) ?>">
            <div class="red-text"><?php echo $errors['panelDescription']; ?></div>
            <label>Panel Date (Must be in YYYY-MM-DD format)</label>
            <input type="text" name="panelDate" value="<?php echo htmlspecialchars($panelDate) ?>">
            <div class="red-text"><?php echo $errors['panelDate']; ?></div>
            <label>Panel Start Time (Must be in 24 hour HH:MM:SS format</label>
            <input type="text" name="panelStartTime" value="<?php echo htmlspecialchars($panelStartTime) ?>">
            <div class="red-text"><?php echo $errors['panelStartTime']; ?></div>
            <label>Panel End Time</label>
            <input type="text" name="panelEndTime" value="<?php echo htmlspecialchars($panelEndTime) ?>">
            <div class="red-text"><?php echo $errors['panelEndTime']; ?></div>
            <label>Panel Moderator</label>
            <input type="text" name="panelModerator" value="<?php echo htmlspecialchars($panelModerator) ?>">
            <div class="red-text"><?php echo $errors['panelModerator']; ?></div>
            <label>Panelist(s)</label>
            <input type="text" name="panelist" value="<?php echo htmlspecialchars($panelist) ?>">
            <div class="red-text"><?php echo $errors['panelist']; ?></div>
            <div class="center">
                <input type="submit" name="submit" value="Submit" class="btn brand z-depth-0">
            </div>
        </form>
    </section>

    <footer class="section">
        <div class="center grey-text">&copy; Copyright 2021</div>
    </footer>
</body>

</html>

If anyone can give me suggestions on how to do this, and hopefully walk me through it at least a little, I would greatly appreciate it. This is the last part I'm struggling with, and I just can't get it to work.

You must provide definite parameters format to MySQL query. It doesn't matter what it will be precisely. If it is not standard YYYY-MM-DD hh:mm:ss then you may apply STR_TO_DATE() to the provided value simply. If the input format is "free" then you may use regex for to check does the value is legal, and if true then convert it to definite format that is expected to be received by the query. When you retrieve the value from MySQL you may format the value into needed format in the query using DATE_FORMAT() function. — Akina
– Akina, Commented Dec 7, 2021 at 5:57

Nipun · Accepted Answer · 2021-12-07 06:02:50Z

2

Basically, users prefer to have ease interface, so I would suggest to provide calender to pick up date.

For the confirmation of date-time format, you can do the following-

$date = mysql_real_escape_string($_POST['intake_date']);

For the DATE type-

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

For DATETIME type-

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

I have replaced '/' , because sometimes it seems that date might contain that slash. So I preferred to replace it by '-'

answered Dec 7, 2021 at 6:02

Nipun

1575 bronze badges

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

1 Comment

Brian Cook Over a year ago

It looks like the calendar converts perfectly, so there is no need to the strtotime command, but thank you for the suggestion.

Ahmad · Accepted Answer · 2021-12-07 06:04:24Z

1

Please ask more concise questions without copying and paste the entire code, if you want people to pay attention and encourage them to answer.

Use html <input type="date" > to solve the problem of date format. The browser will automatically display a calendar for the user to pick the date. You can read more on that here
No need for a calendar button, since the input itself will display a built-in calendar
in PHP you can use functions to format a date object like

    echo $myDateVariable->format('Y-m-d H:i:s');  // YYYY-MM-DD HH:MM:SS

1 Comment

Brian Cook Over a year ago

The <input type="date"> seems to work perfectly.

Collectives™ on Stack Overflow

Convert user input date in PHP form to MySQL acceptable format

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related