How to input a date into mysql database using PHP

Question

Ok so i already have a few insert scripts working, however when i try and use this one nothing happens. I dont get an error or anything, it simply doesnt insert into the database. I have a feeling its the date parameter, however im unsure how to make it recognizable?

<?php //SETTING SESSION VARIABLES FOR ROUND 1 TEAMS AND SCORE

session_start();

  if (isset($_POST['go1'])) { // MATCHUP 1

    include_once 'dbcon.php';

    $_SESSION['t_team1'] = $_POST['team-1'];
    $_SESSION['t_team2'] = $_POST['team-2'];
    $_SESSION['s_score1'] = $_POST['score-1'];
    $_SESSION['s_score2'] = $_POST['score-2'];

    $team1winner = mysqli_real_escape_string($conn, $_POST['team-1']);
    $team2winner = mysqli_real_escape_string($conn, $_POST['team-2']);
    $date1 = mysqli_real_escape_string($conn, $_POST['date-1']);

    $sql = "INSERT INTO knockout (knockout_team1, knockout_team2, knockout_date)
    VALUES ('$team1winner', '$team2winner', '$date1');";

    header("Location: ../tables.php?tables=winner");
}

//date html

<input type="date" name"date-1" value="date" class="date">

What is $_POST['date-1'] and what is the column type of knockout_date. — AbraCadaver
– AbraCadaver, Commented Apr 1, 2019 at 15:59
just added what post date 1 is, and the type is date in my database — breadley
– breadley, Commented Apr 1, 2019 at 16:01
You create the sql string, but you never actually insert it into the database. — aynber
– aynber, Commented Apr 1, 2019 at 16:05
You are wide open for SQL injection. Since you're using mysqli, take advantage of prepared statements and bind_param. This will take care of any pesky quoting issues that may occur. It's safer than using mysqli_real_escape_string — aynber
– aynber, Commented Apr 1, 2019 at 16:06
As @aynber said, you never perform the insert. You should use MySQL's CURDATE() if all you want to do is insert the date when the data is inserted. — Jay Blanchard
– Jay Blanchard, Commented Apr 1, 2019 at 16:23

Mousa Alfhaily · Accepted Answer · 2019-04-01 17:58:22Z

First of all, you are putting the SQL command in a string but you are not actually executing the command.

Second of all, if you are executing the command but you are not showing it here and your dbcon.php file is working properly, then it is more likely a date format issue.

Finally, you need to execute all of your commands especially INSERT commands in prepared statements to prevent SQL injections winch is VERY important.

Here how your code should look like :

<?php //SETTING SESSION VARIABLES FOR ROUND 1 TEAMS AND SCORE

    session_start();

    if (isset($_POST['go1'])) { // MATCHUP 1

    include_once 'dbcon.php';

    $_SESSION['t_team1'] = $_POST['team-1'];
    $_SESSION['t_team2'] = $_POST['team-2'];
    $_SESSION['s_score1'] = $_POST['score-1'];
    $_SESSION['s_score2'] = $_POST['score-2'];

    $team1winner = mysqli_real_escape_string($conn, $_POST['team-1']);
    $team2winner = mysqli_real_escape_string($conn, $_POST['team-2']);
    $date1 = mysqli_real_escape_string($conn, $_POST['date-1']);

    $TeamsStat = $conn->prepare("INSERT INTO knockout (knockout_team1, knockout_team2, knockout_date) VALUES (?, ?, ?)");

    $TeamsStat->bind_param("sss", $team1winner, $team2winner, $date1);   

    $TeamsStat->execute();
    $TeamsStat->close();

    header("Location: ../tables.php?tables=winner");
}

Where $conn is the object of your database connection.

Since prepared statements doesn't support date type and since the date is not a free input value, the knockout_date column should be a string and the variable $date1 should also be a string.

Hope that helped you.

i appreciate the great explanation, however i believe there may be a syntax error somewhere in this line $conn->prepare("INSERT INTO knockout (knockout_team1, knockout_team2) VALUES (?, ?)";
still got nothing going in database though, despite not showing any errors
@breadley Yes there was an error in my code... I mode some modifications, please try it now.
@breadley The only reason that it is not inserting even though there is no errors is because you are not executing the command.
still syntax issues with this one line $TeamsStat = $conn->prepare("INSERT INTO knockout (knockout_team1, knockout_team2) VALUES (?, ?)";

AgentP · Accepted Answer · 2019-04-01 16:08:43Z

0

The date format in HTML is of type dd-mm-yyyy but while inserting it into database you need to make sure its YYYY-mm-dd type. So make sure you are converting it Before inserting it

Ex :

  $originalDate = $yourDateVariable;
  $newDate = date("d-m-Y", strtotime($originalDate));

Or Go through manual and find alternatives functions to convert it

answered Apr 1, 2019 at 16:08

AgentP

7,3594 gold badges39 silver badges59 bronze badges

5 Comments

breadley Over a year ago

still not inserting into db

AgentP Over a year ago

Are you sure that date is converted ? try to echo it and one more thing where are actually executing that query ? i.e are you missing mysqli_query($conn,$sql); or do you performing it any other script

breadley Over a year ago

i was missing that script, however now ive added it all inputs are inserted as 0 in my database despite them not being this

Jay Blanchard Over a year ago

Now that you are inserting have you checked the error logs on the server?

AgentP Over a year ago

also make sure if you are using $_SESSION variables call session_start() in each page separately

Collectives™ on Stack Overflow

How to input a date into mysql database using PHP

2 Answers 2

11 Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

5 Comments

Related