[SOLVED] convert date format

saltedm8 · #1 Oct 6th, 2007, 01:03

i need To convert between my 06th November 2007 format and the YYYY-MM-DD format when inserting them into the database from a form and retreiving them from the database...

could someone give me a hand with this one, thanks

Rakuli · #2 Oct 6th, 2007, 04:22

You could use the strtotime and strftime functions like this:

PHP: Select all




$current = '06th November 2007';



// convert to Mysql

$mysql  = strftime ('%Y-%m-%d',strtotime ($current));



echo $mysql;



//convert back



echo strftime('%d %B %Y', strtotime($mysql));

Check the php website for the date format parameters.

Hope that helps,

Cheers.

saltedm8 · #3 Oct 6th, 2007, 08:37

i have this ( form process )

PHP: Select all


<?php error_reporting(E_ALL );?>
<?php include 'config.php';?>
<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
function filterThis($string) {
$string = htmlentities($string);
$string = mysql_real_escape_string($string);
return $string; }
$title = '$string';
$date = '$string' ;
$subject = '$string';
$blog_mes = '$string';
$string = filterThis($title);
$string = filterThis($date);
$string = filterThis($subject);
$string = filterThis($blog_mes);
$query = sprintf("SELECT * FROM form WHERE title='%s' AND date='%s' AND subject='%s' AND blog_mes='%s'",
mysql_real_escape_string($title),
mysql_real_escape_string($date),
mysql_real_escape_string($subject),
mysql_real_escape_string($blog_mes));
 
if (isset($_POST['submit'])) {
  $title = $_POST['title'];
  $date = $_POST['date'];
  $subject = $_POST['subject'];
  $blog_mes = $_POST['blog_mes'];
  $sql="INSERT INTO form (title, date, subject, blog_mes)
VALUES ('$title', '$date', '$subject', '$blog_mes')";
mysql_select_db("$dbname",$conn);
mysql_query($sql) or die (mysql_error());
}
?>

and this ( the form itself )

PHP: Select all


<script language="javascript" type="text/javascript" src="../jscripts/tiny_mce/tiny_mce.js"></script>
<script language="javascript" type="text/javascript">
tinyMCE.init({
 mode : "textareas"
});
</script>
<?php include 'config.php';?>
<?php $d = date ("dS F Y" );?>
<?php include("form.php"); ?> 
<div align="center">
  <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
    <p><input name="title" type="text" value="TITLE" size="100">
    </p>
    <table width="600" height="39" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td width="270"><input name="date" type="text" value="<?php echo($d); ?> "45"></td>
        <td width="58">&nbsp;</td>
        <td width="136">
          <label></label>
          <div align="center"><strong>SUBJECT:</strong></div></td>
        <td width="136"><select name="subject" size="1" id="subject">
<option>choose...</option>
<option>website design tips</option>
                        </select></td>
      </tr>
    </table>
    <p>
      <?php include ("created.php");?>
    </p>
    <label></label>
    <p>
      <textarea name="blog_mes" cols="100" rows="15">BLOG MESSAGE</textarea>
    </p>
    <table width="632" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="494">&nbsp;</td>
        <td width="138"><div align="right">
          <input type="submit" name="submit" value="SUBMIT BLOG">
        </div></td>
      </tr>
    </table>
  </form>
</div>
</body>
</html>

i cannot work out where it needs to be, what i am trying to achieve is the order which the blog is displayed, at the moment if i made blog 1, blog 2 and blog 3 the order is

blog1
blog2
blog3

i need it to be latest first

blog3
blog2
blog1

from what i understand it is the time that is the problem, because

PHP: Select all


"SELECT * FROM form ORDER BY date DESC LIMIT "

is right and if i swap it to ASC it still displayes in that order ( simply because the N comes before O in the alphabet )

but i do need the form itself the display this format

PHP: Select all


("dS F Y" );?>

Rakuli · #4 Oct 6th, 2007, 08:44

Okay, I find that for something as simple as a blog, it is best to have that column as an integer and add a unix timestamp to it. This allows for ease of sorting ASC DESC when pulling from the database.

If the column is set as a mySQL datestamp, it will also word the same way because of the way the date is formatted YYYY-MM-DD.

I assume the way you now have it is that the column has a value of something like 6th November 2006 etc?

saltedm8 · #5 Oct 6th, 2007, 08:48

( install script )

PHP: Select all


<?php error_reporting(E_ALL);?>
<?php include 'config.php';?>
<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$db  = 'CREATE DATABASE '.$dbname;
mysql_select_db($db);
$result = mysql_query($db);
 $db = mysql_select_db($dbname) or die('Cannot select database');
// Create a MySQL table in the selected database
       $db="CREATE TABLE form (
        title TEXT NOT NULL ,
        date VARCHAR(19) NOT NULL, 
        subject VARCHAR(30) NOT NULL, 
        blog_mes TEXT NOT NULL ) ";
 
  mysql_query($db) or die (mysql_error());
mysql_close($conn);
?>

this is everything that even mentions the date

Rakuli · #6 Oct 6th, 2007, 08:54

Okay, you could change this

PHP: Select all


// Create a MySQL table in the selected database       

$db="CREATE TABLE form (        title TEXT NOT NULL ,        date INT(16) NOT NULL,         subject VARCHAR(30) NOT NULL,         blog_mes TEXT NOT NULL ) ";

That way when you insert your blog

PHP: Select all


 $sql="INSERT INTO form (title, date, subject, blog_mes)VALUES ('$title', " . time() .", '$subject', '$blog_mes')";

This will insert a unix timestamp that you can compare easily with ASC DESC when selecting and also perform the myriad of date functions on with PHP

saltedm8 · #7 Oct 6th, 2007, 09:07

that worked, thank you - i have been up most of the night trying to deal with that one, the only problem now is the date once submitted to the blog is displaying like

Quote:

1191661576

Rakuli · #8 Oct 6th, 2007, 09:10

No problems

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
convert dates in Unix time format	ktsirig	PHP Forum	1	Feb 11th, 2007 14:01
Convert date to MySQl format	AdRock	PHP Forum	4	Jan 15th, 2007 09:41
Changing date format in date picker	AdRock	JavaScript Forum	1	Aug 1st, 2006 17:16
convert string to datetime format ...	jeffmarsh	Other Programming Languages	0	Jul 2nd, 2006 06:36
date format help please	charter	Databases	3	Jun 14th, 2004 20:09

		Webforumz.com > Main Forums > Program Your Website > PHP Forum
[SOLVED] convert date format