Inserting date value into mysql database from a Date Selection List

[terungwa]

I have this php script below that produces a date selection list. Considering that this form has three date field components, that is year, month and day. I am at a loss as to how to insert the three variables into one variable for inclusion into the database.

<?php
 $monthName = array(1 => "January", "February", "March",
                         "April", "May", "June", "July",
                         "August", "September", "October",
                         "November", "December");
 $today = time();                   //stores today's date
 $f_today = date("M-d-Y",$today);   //formats today's date
$startyear = date("Y")-60;
 echo "<div style = 'text-align: center'>\n";
 echo "<form action='$_SERVER[PHP_SELF]' method='POST'>\n";

 /* build selection list for the month */
 $todayMO = date("n",$today);  //get the month from $today
 echo "<select name='month'>\n";
 for ($n=1;$n<=12;$n++) 
 {
   echo " <option value=$n";
   if ($todayMO == $n)
   {
     echo " selected";
   }
   echo " > $monthName[$n]\n\n</option>";
 }
 echo "</select>\n";

 /* build selection list for the day */
 $todayDay= date("d",$today);    //get the day from $today
 echo "<select name='day'>\n";
 for ($n=1;$n<=31;$n++)  
 {
   echo " <option value=$n";
   if ($todayDay == $n ) 
   {
     echo " selected";
   }
   echo " > $n</option>\n";
 }
 echo "</select>\n";

 /* build selection list for the year */
 echo "<select name='year'>\n";
 for ($n=$startyear;$n<=$startyear+200;$n++)
 {
   echo " <option value=$n";
   if ($startyear == $n )
   {
     echo " selected";
   }
   echo " > $n</option>\n";
 }
 echo "</select>\n";
 echo "<p>
<input type=\"submit\" name=\"insert\" value=\"Insert New Entry\" id=\"insert\">
</p></form></div>\n";
?>

I tried insert SQL query to post into the mysql database but it did not work:

<?php
$errors = array();
if (isset($_POST['insert'])) {
$month=$_POST['month'];
$day=$_POST['day'];
$year=$_POST['year'];
$date_value="$year-$month-$day";
echo "YYYY-mm-dd format :$date_value<br>";
  require_once('./includes/connection.inc.php');
  // initialize flag
  $OK = false;
  // create database connection
  $conn = dbConnect('read');
  // initialize prepared statement
  $stmt = $conn->stmt_init();
  // create SQL
  $sql = 'INSERT INTO date (
			id,
			month,
			day,
			year,
			date_value
			)
		VALUES(?, ?, ?, ?, ?)';
  if ($stmt->prepare($sql)) {
	// bind parameters and execute statement
	$stmt->bind_param('issss', $_POST['id'], $_POST['month'], , $_POST['day'], , $_POST['year'], , $_POST['date_value']);
    // execute and get number of affected rows
	$stmt->execute();
	if ($stmt->affected_rows > 0) {
	  $OK = true;
	}
  }

but how do i structure the insert SQL query?

 

Thanks.

[Barand]

I wouldn't store month, day and year when they are already part of the stored date. Your bind parameter should be $date_value, not $_POST['date-value'] as that doesn't exist. Where is $_POST['id'] from, I didn't spot it in the form?

[terungwa]

I wouldn't store month, day and year when they are already part of the stored date. Your bind parameter should be $date_value, not $_POST['date-value'] as that doesn't exist. Where is $_POST['id'] from, I didn't spot it in the form?

Thank you Barand, this is my final functional insert SQL query:

<?php
$errors = array();
if (isset($_POST['insert'])) {
$month=$_POST['month'];
$day=$_POST['day'];
$year=$_POST['year'];
$date_value="$year-$month-$day";
  require_once('./includes/connection.inc.php');
  // initialize flag
  $OK = false;
  // create database connection
  $conn = dbConnect('read');
  // initialize prepared statement
  $stmt = $conn->stmt_init();
  // create SQL
  $sql = 'INSERT INTO date (
			date_value
			)
		VALUES(?)';
  if ($stmt->prepare($sql)) {
	// bind parameters and execute statement
	$stmt->bind_param('s', $date_value);
    // execute and get number of affected rows
	$stmt->execute();
	if ($stmt->affected_rows > 0) {
	  $OK = true;
	}
  }