• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

Dreamweaver Update Record Server Behaviour & Date Format

Hi,

I've created a PHP front-end for an MySQL database in Dreamweaver.  I've used the Update Record Server Behavior.  This retrieves the data for a record fine and I've even displayed the record's date in the format I want it by using <?php echo date("Y F j", strtotime ($row_rsGNupdate['date'])); ?>

This results in the date being displayed as '1785 January 8'.  Is it possible for me to update the record in this format and somehow submit it back to MySQL in the correct format?  The date field in MySQL is of type 'date'.  

Any help is greatly appreciated.

Kind Regards

J
0
xtbarton
Asked:
xtbarton
  • 5
  • 4
1 Solution
 
Ovid BurkeCreative DirectorCommented:
I would propose a simple solution, but it would require you to change you date display slightly, by including a coma (,) after the year as in 1785, January 8.

Here is the adjustment for that:
<?php echo date("Y, F j", strtotime ($row_rsGNupdate['date'])); ?>

Open in new window


Then You can create a variable like this which you will pass to your update query:
<?php $new_date = date("Y-m-d", strtotime($_POST['date_field_name'])); ?>

Open in new window


Alternatively, you can write a function that inserts the comma during processing.
0
 
xtbartonAuthor Commented:
Thanks for the speedy response.  I'm a little new to PHP/MySQL, so could I possibly ask for some help as to where to edit my code?  My update statement currently looks like:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form2")) {
  $updateSQL = sprintf("UPDATE gemnews_tab SET category=%s, `language`=%s, newspaper=%s, `date`=%s, issue=%s, works=%s, entry=%s, comments=%s WHERE id=%s",
                       GetSQLValueString($_POST['category3'], "text"),
                       GetSQLValueString($_POST['language2'], "text"),
                       GetSQLValueString($_POST['newspaper'], "text"),
                       GetSQLValueString($_POST['date'], "date"),
                       GetSQLValueString($_POST['issue'], "text"),
                       GetSQLValueString($_POST['works2'], "text"),
                       GetSQLValueString($_POST['entry2'], "text"),
                       GetSQLValueString($_POST['comments'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

Open in new window


Thanks.
0
 
Ovid BurkeCreative DirectorCommented:
Try this:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form2")) {
  $updateSQL = sprintf("UPDATE gemnews_tab SET category=%s, `language`=%s, newspaper=%s, `date`=%s, issue=%s, works=%s, entry=%s, comments=%s WHERE id=%s",
                       GetSQLValueString($_POST['category3'], "text"),
                       GetSQLValueString($_POST['language2'], "text"),
                       GetSQLValueString($_POST['newspaper'], "text"),
                       GetSQLValueString(date("Y-m-d", strtotime($_POST['date'])), "date"),
                       GetSQLValueString($_POST['issue'], "text"),
                       GetSQLValueString($_POST['works2'], "text"),
                       GetSQLValueString($_POST['entry2'], "text"),
                       GetSQLValueString($_POST['comments'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
xtbartonAuthor Commented:
Thanks; the only problem with that is that it delivers the date to MySQL as 1970-01-01.  
0
 
xtbartonAuthor Commented:
Actually, it added 2011, June 5.  
0
 
Ovid BurkeCreative DirectorCommented:
Hello,

Sorry. I was out since my last post. What exactly is the data type for you date field in MySQL (date, datetime, or timestamp)?

Can you explain exactly how you want the dates to look in MySQL?
0
 
xtbartonAuthor Commented:
Hi,

The data type for the date field is DATE with no collation (is this correct?).  Through PHPMyAdmin I can see the date stored as 2011-06-05.  It's strange because the field that recalls the data on my update page translates the YYYY-MM-DD date into '1785 January 6' format fine and I can update the month and the day fine, but not the year.  This always returns 2011.  

My PHP for displaying the records current date is:

<?php echo date("Y, F j", strtotime ($row_rsGNupdate['date'])); ?>

Open in new window


And my UPDATE statement is:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form2")) {
  $updateSQL = sprintf("UPDATE gemnews_tab SET category=%s, `language`=%s, newspaper=%s, `date`=%s, issue=%s, works=%s, entry=%s, comments=%s WHERE id=%s",
                       GetSQLValueString($_POST['category3'], "text"),
                       GetSQLValueString($_POST['language2'], "text"),
                       GetSQLValueString($_POST['newspaper'], "text"),
                       GetSQLValueString(date("Y-m-d", strtotime($_POST['date'])), "date"),
                       GetSQLValueString($_POST['issue'], "text"),
                       GetSQLValueString($_POST['works2'], "text"),
                       GetSQLValueString($_POST['entry2'], "text"),
                       GetSQLValueString($_POST['comments'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

Open in new window


Thanks for the help.  I'm only just starting with PHP.  

J


0
 
Ovid BurkeCreative DirectorCommented:
OK, I used the current date in my test, so I was unaware that it did not recognize an earlier year.

I have written a small function to handle that, I hope it works for you.

// function to make date string
function make_date_string($date) {
	preg_match("/\d{4}/i", $date, $match);
	$_D = $match[0] . "-";
	$_D .= date("m-d", strtotime(substr($date, 6)));
	return $_D;
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form2")) {
  $updateSQL = sprintf("UPDATE gemnews_tab SET category=%s, `language`=%s, newspaper=%s, `date`=%s, issue=%s, works=%s, entry=%s, comments=%s WHERE id=%s",
                       GetSQLValueString($_POST['category3'], "text"),
                       GetSQLValueString($_POST['language2'], "text"),
                       GetSQLValueString($_POST['newspaper'], "text"),
                       GetSQLValueString(make_date_string($_POST['date']), "date"),
                       GetSQLValueString($_POST['issue'], "text"),
                       GetSQLValueString($_POST['works2'], "text"),
                       GetSQLValueString($_POST['entry2'], "text"),
                       GetSQLValueString($_POST['comments'], "text"),
                       GetSQLValueString($_POST['id'], "int"));

Open in new window

0
 
xtbartonAuthor Commented:
Brilliant solution.  Thank you so much for your help madaboutasp.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now