asked on

Add date format to database PHP + MySQL

I am trying to add a date to my database but can't get it working!

There are three dropdowns selecting the MM-DD-YYYY. I'm then trying to join them together and send them tothe database. Any ideas what's wrong here?

$bd = mysqli_real_escape_string($dbc, $trimmed['bdayday']);
$bm = mysqli_real_escape_string($dbc, $trimmed['bdaymonth']);
$by = mysqli_real_escape_string($dbc, $trimmed['bdayyear']);
$birthday = $by."//".$bm."//".$bd;

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO board (title, copy, `date`) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['copy'], "text"),
                       GetSQLValueString($_POST['$birthday'], "date"));

  mysql_select_db($database_anjoman, $anjoman);
  $Result1 = mysql_query($insertSQL, $anjoman) or die(mysql_error());

  $insertGoTo = "AdminUpdated.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_anjoman, $anjoman);
$query_Recordset1 = "SELECT id, title, copy, `date` FROM board";
$Recordset1 = mysql_query($query_Recordset1, $anjoman) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

Open in new window

wmadrid1

trye changing these lines

$birthday = $by."//".$bm."//".$bd;

for

$birthday = $by."-".$bm."-".$bd;

and this
$insertSQL = sprintf("INSERT INTO board (title, copy, `date`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['title'], "text"),
GetSQLValueString($_POST['copy'], "text"),
GetSQLValueString($_POST['$birthday'], "date"));

for

$insertSQL = sprintf("INSERT INTO board (title, copy, `date`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['title'], "text"),
GetSQLValueString($_POST['copy'], "text"),
GetSQLValueString($birthday, "date"));

Derokorian

MySQL DATE columns expect form YYYY-MM-DD:

// change this line
$birthday = $by."//".$bm."//".$bd;

// To this line
$birthday = sprintf('%04d-%02d-%02d',$by,$bm,$bd);

Open in new window

Derokorian

Also make sure and change your $insertSQL as wmadrid1 said.

BrighteyesDesign

ASKER

Cheers for that, both ways just add 00-00-0000 as the date though?

Derokorian

Can you run this query and show the results please?

SHOW CREATE TABLE `board`

wmadrid1

please after this line:

$insertSQL = sprintf("INSERT INTO board (title, copy, `date`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['title'], "text"),
GetSQLValueString($_POST['copy'], "text"),
GetSQLValueString($birthday, "date"));

put this line

echo $insertSQL;

and post the result here

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrighteyesDesign

ASKER

When I add that line the page nothing shows? if I then click the add button the info adds to the database with no results shown on the screen?

Also, in my original code (which was copies from another page) I don't think I need all of the...$bd = mysqli_real_escape_string($dbc, $trimmed['bdayday']); as I have no $dbc or $trimmed queries on the page, so that's now...$bd = 'bdayday';

I thought that might be the problem but it's not

$bd = 'bdayday';
$bm = 'bdaymonth';
$by = 'bdayyear';
$birthday = $by."-".$bm."-".$bd;


$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO board (title, copy, `date`) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['copy'], "text"),
                       GetSQLValueString($birthday, "date"));
					   
					   echo $insertSQL;
					   
					   

  mysql_select_db($database_anjoman, $anjoman);
  $Result1 = mysql_query($insertSQL, $anjoman) or die(mysql_error());

Open in new window

ASKER CERTIFIED SOLUTION

wmadrid1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial