Page 1 of 1

php mysql date

Posted: Mon Jul 21, 2008 12:37 pm
by phpsynner
hey i have a basic question. i am trying to create a mysql database that contains articles for the website. what i want to do is list them on the front page by date created but i need to know that best way to keep track of the date. i am questioning if the date function should be on the php side when the article is created or on the mysql side when the data is inserted.

Re: php mysql date

Posted: Mon Jul 21, 2008 12:44 pm
by RobertGonzalez
Keep it on the mysql side. Make the field a datetime field and sort by the field when selecting.

Re: php mysql date

Posted: Mon Jul 21, 2008 1:17 pm
by phpsynner
thanks man

Re: php mysql date

Posted: Mon Jul 21, 2008 1:23 pm
by EverLearning
If you keep it on the mysql side make sure that the mysql timezone setting is the same as the php timezone setting. It is unlikely that they will differ, but it can happen :) It happened to one of the sites I worked on, where half a year after the the deployment of the site, the mysql time suddenly lagged 7 hours behind the php time(I guess it had to do with the upgrades that they did on the server, newer version of php, of mysql... and in the process skipped the "tiny" detail of synchronizing the timezones).

Before MySql 4.1.3 timezone setting was set at the start of the mysqld server, and there was no way of changing it. With 4.1.3 and after, where are several ways to set the timezone, like per-connection

Code: Select all

SET time_zone = timezone;
Take a look at the MySql manual for more information: MySQL Server Time Zone Support

Re: php mysql date

Posted: Mon Jul 21, 2008 1:55 pm
by phpsynner
ok i am having an issue with this, it does not seem to work i opted to add a field to my table that is thedate and it is a datetime field.

now i should be able to add this to the insert qurery

Code: Select all

 
$insertQuery = "INSERT INTO cmsarticles (thedate,title,tagline,section,thearticle) VALUES ("NOW().
     "'".$_POST['title']."', ".
     "'".$_POST['tagline']."', ".
     "'".$_POST['section']."', ".
     "'".$_POST['thearticle']."',
     )";
 
but i get a syntax error from mysql on line 1

Code: Select all

 
Sorry, there was an error saving to the database
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''testing', 'test', '2', 'this is to test the date function in mysql.', )' at line 1
i believe i have a quote misplaced or something anyone got an idea.

Re: php mysql date

Posted: Mon Jul 21, 2008 2:03 pm
by RobertGonzalez
Don't wrap NOW() in quotes

Re: php mysql date

Posted: Mon Jul 21, 2008 2:08 pm
by RobertGonzalez
Actually, your string concatenation was a bit off. Now I know you are going to be checking the post array values for validity and security so I would say you query could look something like this:

Code: Select all

<?php
$insertQuery = "INSERT INTO cmsarticles (
                `thedate`, `title`, `tagline`, `section`, `thearticle`
                ) VALUES (
                NOW(), '$title', '$tagline', '$section','$thearticle'
                )";
?>

Re: php mysql date

Posted: Tue Jul 22, 2008 3:47 pm
by phpsynner
thanks looks a lot better.

Re: php mysql date

Posted: Tue Jul 22, 2008 9:23 pm
by manixrock
You should always escape your mysql arguments, otherwise mysql injection attacks, or a title containing a simple ' (ex: "today's tutorial is...") will ruin your code.

Escape them like this:

Code: Select all

$query = sprintf('INSERT INTO cmsarticles (`thedate`, `title`, `tagline`, `section`, `thearticle`)
            VALUES ( NOW(), "%s", "%s", "%s", "%s" )',
            mysql_real_escape_string($title),
            mysql_real_escape_string($tagline),
            mysql_real_escape_string($section),
            mysql_real_escape_string($thearticle));