Insert Date from PHP form into MySQL date column

mtnmchgrl · May 4, 2010

Hello,

I've spent at least 6 hours going back and forth changing this code and I cannot get this to process. The closest I came was that it would enter what I wanted, but the date showed up as 0000-00-00. This is the simplest form. Only 2 columns are being entered from a php input form....a name and a date. Can anyone help? I have already consulted 3 mysql/php books (and RTFM) AND googled away but i cannot find an exact situation like mine....which is hard to believe. I already have my database set for the date to be yyyy-mm-mm and i don't really care how it reads in PHP. I just want the use to be able to enter the thing (yyyy-mm-mm) is fine. MANY thanks....

<?php
// START SESSION
require_once('includes/session.inc.php');

// PROCESS FORM ON SUBMIT
//if (!isset('submit', $_POST)) 
if (array_key_exists('submit', $_POST)) {

//IF THE ARRAY ISN'T EMPTY PRINT ALL THE FIELDS OF THE PLAYER TABLE
if(!empty($_POST['tourneyName']) && !empty($_POST['gameDate'])) {

// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// QUERY TO RETRIEVE ENTRY RESULTS	
$query="insert into `tournaments` ('tourneyID', 'tourneyName', 'gameDate') values('NULL','(".$_POST['tourneyName']."')', date_format(".$_POST['gameDate']."'%Y-%m-%d') )";
mysql_query($query);

// RESET FLAGS
$success = 0;

// IF QUERY RUNS CORRECTLY
if ($query) {$success = 1;} else {$error=1;};

} else {
$success = 0;
}

}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>Admin MySQL Database For The Bomb Island Raiders</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="css/admin.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="navigation">

<?php include_once('includes/admin_nav.inc.php'); ?>

</div>

<div id="content">

<h1>Add </h1>
<br />



<!--HTML FORM-->
<form method="post" action="addTournament2.php">
<table border=1 RULES=NONE FRAME=BOX>
<tr>
<td><font>Tournament Name</td>
<td><input type="text" name="tourneyName" size="24"></td>
</tr>

<tr>
<td><font>Game Date:</td>
<td><input type="text" name="gameDate" size="24"></td>
</tr>
</table>
<br />
<input type="submit" value="Submit" name="submit">
<INPUT TYPE=RESET VALUE="Reset Values">
</form>
<br />



<?php
// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// QUERY TO SELECT ALL FROM PLAYER TABLE
$result = mysql_query("SELECT * FROM `tournaments`");

// IF THERE IS AT LEAST 1 RECORD TO DISPLAY THE LISTING 
if(mysql_num_rows($result) > 0) {

// START OF TABLE, HEADERS, ETC
echo "<table border=1>";
echo "<tr><th>TID</th><th>Name</th><th>Date</th></tr>";

//LOOP THE DATABASE TO DISPLAY THE RECORDS
while ($myrow = mysql_fetch_array($result)) {

//PRINT RESULTS
echo '<tr><td>'.$myrow["tourneyID"].'</td><td>'.$myrow["tourneyName"].'</td><td>'.$myrow["gameDate"].'</td></tr>';

} 

echo "</table>\n";

} else {	//IF NO TABLE DATA TO DISPLAY

//MESSAGE TO PRINT IF TABLE IS EMPTY
echo "Sorry, no records were found!";	
} //END ELSE STATEMENT

//IF RECORDS IS ADDED SUCCESSFULLY OR NOT THE FOLLOWING MESSAGE WILL PRINT
if ($success==0) { echo'There was an error. No record was added. Please try again.'; }
if ($success==1) { echo'The record has been added.'; }

?>


</body>
</html>

Muddy_Funster · May 4, 2010

give this a shot :

$date = new DateTime($_POST['gameDate');
$query="insert into `tournaments` ('tourneyName', 'gameDate') values('".$_POST['tourneyName']."', ".$date.")";

mtnmchgrl · May 4, 2010

give this a shot :

$date = new DateTime($_POST['gameDate');
$query="insert into `tournaments` ('tourneyName', 'gameDate') values('".$_POST['tourneyName']."', ".$date.")";

Hey Muddy,

Ok, I took out this line:

$query="insert into `tournaments` ('tourneyID', 'tourneyName', 'gameDate') values('NULL','(".$_POST['tourneyName']."')', date_format(".$_POST['gameDate']."'%Y-%m-%d') )";

and put in your suggestion and got this error:

Parse error: syntax error, unexpected ')', expecting ']' in L:\  on line 16

MatthewJ · May 4, 2010

<?php
// START SESSION
require_once('includes/session.inc.php');

// PROCESS FORM ON SUBMIT
//if (!isset('submit', $_POST)) 
if (array_key_exists('submit', $_POST)) {

//IF THE ARRAY ISN'T EMPTY PRINT ALL THE FIELDS OF THE PLAYER TABLE
if(!empty($_POST['tourneyName']) && !empty($_POST['gameDate'])) {




// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// QUERY TO RETRIEVE ENTRY RESULTS

//FORMAT DATE
$formatted_date = date('Y-m-d', strtotime($_POST['gameDate']));

$query="insert into `tournaments` ('tourneyID', 'tourneyName', 'gameDate') values('NULL','(".$_POST['tourneyName']."')', '$formatted_date' )";
mysql_query($query);

// RESET FLAGS
$success = 0;

// IF QUERY RUNS CORRECTLY
if ($query) {$success = 1;} else {$error=1;};

} else {
$success = 0;
}

}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>Admin MySQL Database For The Bomb Island Raiders</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="css/admin.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="navigation">

<?php include_once('includes/admin_nav.inc.php'); ?>

</div>

<div id="content">

<h1>Add </h1>
<br />



<!--HTML FORM-->
<form method="post" action="addTournament2.php">
<table border=1 RULES=NONE FRAME=BOX>
<tr>
<td><font>Tournament Name</td>
<td><input type="text" name="tourneyName" size="24"></td>
</tr>

<tr>
<td><font>Game Date:</td>
<td><input type="text" name="gameDate" size="24"></td>
</tr>
</table>
<br />
<input type="submit" value="Submit" name="submit">
<INPUT TYPE=RESET VALUE="Reset Values">
</form>
<br />



<?php
// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// QUERY TO SELECT ALL FROM PLAYER TABLE
$result = mysql_query("SELECT * FROM `tournaments`");

// IF THERE IS AT LEAST 1 RECORD TO DISPLAY THE LISTING 
if(mysql_num_rows($result) > 0) {

// START OF TABLE, HEADERS, ETC
echo "<table border=1>";
echo "<tr><th>TID</th><th>Name</th><th>Date</th></tr>";

//LOOP THE DATABASE TO DISPLAY THE RECORDS
while ($myrow = mysql_fetch_array($result)) {

//PRINT RESULTS
echo '<tr><td>'.$myrow["tourneyID"].'</td><td>'.$myrow["tourneyName"].'</td><td>'.$myrow["gameDate"].'</td></tr>';

} 

echo "</table>\n";

} else {



//IF NO TABLE DATA TO DISPLAY

//MESSAGE TO PRINT IF TABLE IS EMPTY
echo "Sorry, no records were found!";




} //END ELSE STATEMENT

//IF RECORDS IS ADDED SUCCESSFULLY OR NOT THE FOLLOWING MESSAGE WILL PRINT
if ($success==0) { echo'There was an error. No record was added. Please try again.'; }
if ($success==1) { echo'The record has been added.'; }

?>


</body>
</html>

siric · May 4, 2010

Whenever I get into problems with variables, I just validate them by assigning $_POST variables to a normal var and printing them.

So as Muddy_Funster has suggested, assign the POST to $date and do a print afterwards to ensure that it is correct.

Also, note that your entry field allows free text, so unless you are using this yourself, it can lead to problems, as users can enter the date in any format.

MatthewJ · May 4, 2010

Muddy's code is missing a closing ]...

PFMaBiSmAd · May 4, 2010

What format are you entering in the form? You would not necessarily use the mysql date_format() function as its' purpose is to take a DATE value and produce another format from it. Your current problem is because the first parameter in the date_format() would need to be enclosed in single-quotes if it is not a column name or another mysql function. However, you might look at the STR_TO_DATE() function if the format from the form is not yyyy-mm-dd.

Date values are strings and need to be enclosed in single-quotes in the query (unless you are using a mysql function that returns a string or a column that is a DATE type.) If the $_POST variable is in the correct format, you would just need to enclose it in single-quotes inside the query string and not use any mysql functions with it.

Eventually, you should be validating the format in the post variable and escaping it to prevent sql injection.

Muddy_Funster · May 4, 2010

Wish I had a penny for every time I'd done that :'( sorry about that!

should have been

$date = new DateTime($_POST['gameDate']);

And just for a rull of thumb - you really shouldn't declare an entry for 'NULL' in general use.

mtnmchgrl · May 4, 2010

Matthew's code....

I tried this. No parse error screen but the record isn't added. And it says it has been added....but nothing shows up. that part must have to do w/ my success/error statements though. So anyway...that didn't work.

mtnmchgrl · May 4, 2010

What format are you entering in the form? You would not necessarily use the mysql date_format() function as its' purpose is to take a DATE value and produce another format from it. Your current problem is because the first parameter in the date_format() would need to be enclosed in single-quotes if it is not a column name or another mysql function. However, you might look at the STR_TO_DATE() function if the format from the form is not yyyy-mm-dd.

Date values are strings and need to be enclosed in single-quotes in the query (unless you are using a mysql function that returns a string or a column that is a DATE type.) If the $_POST variable is in the correct format, you would just need to enclose it in single-quotes inside the query string and not use any mysql functions with it. Not sure I follow on the single quote thing.....I didn't use date_format() in my original code...?

Eventually, you should be validating the format in the post variable and escaping it to prevent sql injection.

When I have tried to test the name/date in my form I have tried entering both yyyy-mm-mm AND mm-mm-yyyy and even yyyymmmm.

mtnmchgrl · May 4, 2010

Wish I had a penny for every time I'd done that :'( sorry about that!

should have been
$date = new DateTime($_POST['gameDate']);
And just for a rull of thumb - you really shouldn't declare an entry for 'NULL' in general use.

Hey again,

I didn't originally have the NULL in there.....there are 3 columsn in the table....tourneyID is the primary key. I wasn't sure whether i had to reference that or not? Its auto increment so I thought if i left that out and added data for the other 2 fields it would automatically insert a new id#?

I tried your modified code w/ the added bracket and THIS TIME I got this error: :shrug:

Catchable fatal error: Object of class DateTime could not be converted to string in L:\addTournament2.php on line 17

I don't know what that means....but just wondering....my already existing dates in the database aren't time stamped...they are just date (yyyy-mm-dd)

I think i was putting yyyy-mm-mm on those other posts. 'Excuse. I got 3 hours sleep last night.

PFMaBiSmAd · May 4, 2010

Your INSERT query has a number of syntax problems. Your code should have basic error checking and error reporting logic to get it to at least tell you when the query fails.

The problems -

1) Single-quotes around column names make them strings, not column names.

2) Single-quotes around NULL makes it the string 'NULL' not the NULL keyword.

3) You have () around two of the values.

Assuming the format in the $_POST variable is yyyy-mm-dd and you are going to take care of validating it and escaping it before putting it into the query. This will work -

$query="insert into tournaments (tourneyID, tourneyName, gameDate)
values (NULL,'{$_POST['tourneyName']}', '{$_POST['gameDate']}')";

Muddy_Funster · May 4, 2010

try copying and pasting this section of code over what you have already (make a copy first of course) and let us know how you get on:

// QUERY TO RETRIEVE ENTRY RESULTS
$dateIn = $_POST['gameDate'];
if (substr($dateIn, 5, 1) != '-' || substr($dateIn, 8, 1) != '-'){
die ('You did not format the date correctly, please use your back button and enter in format of "YYYY-mm-dd"');
}
$date = new DateTime($dateIn);
echo '<br><br>'.$date.'<br><br>';
$query="insert into `tournaments` ('tourneyName', 'gameDate') values('".$_POST['tourneyName']."', '".$date."')";

Psycho · May 4, 2010

There were a lot of problems with your code aside from the date issues. Here is a complete rewrite in a more logical fashion. This has been tested

<?php
// START SESSION
require_once('includes/session.inc.php');

// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['tourneyName']))
{
    //Create array var to capture errors
    $errors = array();

    //Validate the submitted data
    $tourneyName = trim($_POST['tourneyName']);
    $tourneyDate = trim($_POST['gameDate']);

    if(empty($tourneyName))
    {
        $errors[] = "Tournament name is required.";
    }
    if(empty($tourneyDate))
    {
        $errors[] = "Tournament date is required.";
    }
    else if(!$tourneyDate = strtotime($tourneyDate))
    {
        $errors[] = "Tournament date is not in a valid format.";
    }

    if(count($errors)>0)
    {
        //There were validation errors, prepare error message
        $response = "<span style=\"color:red;\">";
        $response .= "The following errors occured:\n";
        $response .= "<ul>";
        $response .= '<li>' . implode('</li><li>', $errors) . '</li>';
        $response .= "</ul>\n";
        $response .= "</span>\n";
    }
    else
    {
        //No validation errors, save the data to database
        $tourneyName = mysql_real_escape_string($tourneyName);
        $tourneyDate = date('Y-m-d', $tourneyDate);

        $query="INSERT INTO `tournaments` (`tourneyName`, `gameDate`)
                 VALUES ('{$tourneyName}', '{$tourneyDate}')";
        $result = mysql_query($query);

        if (!$result)
        {
            $response = "No record was added. Please try again.";
            $response .= "<br />\n$query<br />".mysql_error();
        }
        else
        {
            $response = "The record has been added.";
        }
    }
}

//Get current records to display
$query = "SELECT * FROM `tournaments`";
$result = mysql_query($query);

//Validate there were records
if(mysql_num_rows($result) == 0)
{
    $records = "Sorry, no records were found!";
}
else
{
    // START OF TABLE, HEADERS, ETC
    $records  = "<table border=\"1\">\n";
    $records .= "<tr><th>TID</th><th>Name</th><th>Date</th></tr>\n";
    //LOOP THE DATABASE TO DISPLAY THE RECORDS
    while ($myrow = mysql_fetch_array($result))
    {
        //PRINT RESULTS
        $date = date('Y-m-d', strtotime($myrow['gameDate']));
        $records .= "<tr>";
        $records .= "<td>{$myrow['tourneyID']}</td>";
        $records .= "<td>{$myrow['tourneyName']}</td>";
        $records .= "<td>{$date}</td>";
        $records .= "</tr>\n";
    } 
    $records .= "</table>\n";
}

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>Admin MySQL Database For The Bomb Island Raiders</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="css/admin.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="navigation">
<?php include_once('includes/admin_nav.inc.php'); ?>
</div>

<div id="content">
<h1>Add </h1>
<br />
<div><?php echo $response; ?></div>
<!--HTML FORM-->
<form method="post" action="">addTournament2.php
    <table border=1 RULES=NONE FRAME=BOX>
        <tr>
            <td>Tournament Name</td>
            <td><input type="text" name="tourneyName" size="24"></td>
        </tr>
        <tr>
            <td>Game Date:</td>
            <td><input type="text" name="gameDate" size="24"></td>
        </tr>
    </table>
    <br />
    <input type="submit" value="Submit" name="submit">
    <input type="reset" value="Reset Values">
</form>

<br />
<?php echo $records; ?>

</body>
</html>

mtnmchgrl · May 4, 2010

There were a lot of problems with your code aside from the date issues. Here is a complete rewrite in a more logical fashion. This has been tested

<?php
// START SESSION
require_once('includes/session.inc.php');

// CONNECT TO DATABASE
require_once('includes/connect.inc.php');

// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['tourneyName']))
{
    //Create array var to capture errors
    $errors = array();

    //Validate the submitted data
    $tourneyName = trim($_POST['tourneyName']);
    $tourneyDate = trim($_POST['gameDate']);

    if(empty($tourneyName))
    {
        $errors[] = "Tournament name is required.";
    }
    if(empty($tourneyDate))
    {
        $errors[] = "Tournament date is required.";
    }
    else if(!$tourneyDate = strtotime($tourneyDate))
    {
        $errors[] = "Tournament date is not in a valid format.";
    }

    if(count($errors)>0)
    {
        //There were validation errors, prepare error message
        $response = "<span style=\"color:red;\">";
        $response .= "The following errors occured:\n";
        $response .= "<ul>";
        $response .= '<li>' . implode('</li><li>', $errors) . '</li>';
        $response .= "</ul>\n";
        $response .= "</span>\n";
    }
    else
    {
        //No validation errors, save the data to database
        $tourneyName = mysql_real_escape_string($tourneyName);
        $tourneyDate = date('Y-m-d', $tourneyDate);

        $query="INSERT INTO `tournaments` (`tourneyName`, `gameDate`)
                 VALUES ('{$tourneyName}', '{$tourneyDate}')";
        $result = mysql_query($query);

        if (!$result)
        {
            $response = "No record was added. Please try again.";
            $response .= "<br />\n$query<br />".mysql_error();
        }
        else
        {
            $response = "The record has been added.";
        }
    }
}

//Get current records to display
$query = "SELECT * FROM `tournaments`";
$result = mysql_query($query);

//Validate there were records
if(mysql_num_rows($result) == 0)
{
    $records = "Sorry, no records were found!";
}
else
{
    // START OF TABLE, HEADERS, ETC
    $records  = "<table border=\"1\">\n";
    $records .= "<tr><th>TID</th><th>Name</th><th>Date</th></tr>\n";
    //LOOP THE DATABASE TO DISPLAY THE RECORDS
    while ($myrow = mysql_fetch_array($result))
    {
        //PRINT RESULTS
        $date = date('Y-m-d', strtotime($myrow['gameDate']));
        $records .= "<tr>";
        $records .= "<td>{$myrow['tourneyID']}</td>";
        $records .= "<td>{$myrow['tourneyName']}</td>";
        $records .= "<td>{$date}</td>";
        $records .= "</tr>\n";
    } 
    $records .= "</table>\n";
}

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>Admin MySQL Database For The Bomb Island Raiders</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="css/admin.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="navigation">
<?php include_once('includes/admin_nav.inc.php'); ?>
</div>

<div id="content">
<h1>Add </h1>
<br />
<div><?php echo $response; ?></div>
<!--HTML FORM-->
<form method="post" action="">addTournament2.php
    <table border=1 RULES=NONE FRAME=BOX>
        <tr>
            <td>Tournament Name</td>
            <td><input type="text" name="tourneyName" size="24"></td>
        </tr>
        <tr>
            <td>Game Date:</td>
            <td><input type="text" name="gameDate" size="24"></td>
        </tr>
    </table>
    <br />
    <input type="submit" value="Submit" name="submit">
    <input type="reset" value="Reset Values">
</form>

<br />
<?php echo $records; ?>

</body>
</html>

Dear mjdamato,

Oh, how I love thee. Let me count the ways. That works perfectly!!! Thank you SOOOOO much. I would have spent another 6 hours trying to go around my you know what to get to my elbow. Thank you, thank you, thank you!

I'm not sure how to do this but this thread has been solved! THANK YOU!!!!!!! WOOHOOO!!!!!

PS. mjdamato is the bomb.

mtnmchgrl · May 4, 2010

There were a lot of problems with your code aside from the date issues. Here is a complete rewrite in a more logical fashion. This has been tested

One quick thing I don't understand.....below where the HTML form comment is and there is a form method post statement....

<form method="post" action="">addTournament2.php

How come the .php file is outside of the form method brackets?? How does that work? Everything else for the most part makes sense and its definitely in a hell of a lot more organized fashion. Perhaps as I become better at this I will be able to produce something similiar. Sigh.

PFMaBiSmAd · May 4, 2010

That's a typo.

An empty action="" attribute refers to the same page, so it does not matter. You should remove the addTournament2.php that is outside of the <form > tag.

mtnmchgrl · May 4, 2010

That's a typo.

An empty action="" attribute refers to the same page, so it does not matter. You should remove the addTournament2.php that is outside of the <form > tag.

ok Cool. And that empty action="" refers to this at the very top right?

// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['tourneyName']))

PFMaBiSmAd · May 4, 2010

<form method="post" action="">

Psycho · May 4, 2010

There were a lot of problems with your code aside from the date issues. Here is a complete rewrite in a more logical fashion. This has been tested

One quick thing I don't understand.....below where the HTML form comment is and there is a form method post statement....
<form method="post" action="">addTournament2.php
How come the .php file is outside of the form method brackets?? How does that work? Everything else for the most part makes sense and its definitely in a hell of a lot more organized fashion. Perhaps as I become better at this I will be able to produce something similiar. Sigh.

Sorry, bout that. I removed the file name from the action so I could have the page POST to itself for testing purposes. You can replace that value or leave it blank if the page is supposed top post to itself.

// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['tourneyName']))

No, no relation. In that code, I simply define $respons as an empty string in case it doesn't get defined later. The IF statement is there to process the data only if data was posted.

mtnmchgrl · May 4, 2010

There were a lot of problems with your code aside from the date issues. Here is a complete rewrite in a more logical fashion. This has been tested

One quick thing I don't understand.....below where the HTML form comment is and there is a form method post statement....
<form method="post" action="">addTournament2.php
How come the .php file is outside of the form method brackets?? How does that work? Everything else for the most part makes sense and its definitely in a hell of a lot more organized fashion. Perhaps as I become better at this I will be able to produce something similiar. Sigh.
Sorry, bout that. I removed the file name from the action so I could have the page POST to itself for testing purposes. You can replace that value or leave it blank if the page is supposed top post to itself.
// PROCESS FORM ON SUBMIT
$response = '';
if (isset($_POST['tourneyName']))
No, no relation. In that code, I simply define $respons as an empty string in case it doesn't get defined later. The IF statement is there to process the data only if data was posted.

Ok, thank you for that. It helps me better understand so I can try and correct in the future.....Now I have taken what you did and tried to apply it to another form.....its slightly different but should have almost the basic set up. I might post a new thread if I can't get it working! THANKS AGAIN!

Sign In

Insert Date from PHP form into MySQL date column

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information