php mysql date

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpsynner
Forum Newbie
Posts: 4
Joined: Mon Jul 21, 2008 12:32 pm

php mysql date

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: php mysql date

Post by RobertGonzalez »

Keep it on the mysql side. Make the field a datetime field and sort by the field when selecting.
phpsynner
Forum Newbie
Posts: 4
Joined: Mon Jul 21, 2008 12:32 pm

Re: php mysql date

Post by phpsynner »

thanks man
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: php mysql date

Post 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
phpsynner
Forum Newbie
Posts: 4
Joined: Mon Jul 21, 2008 12:32 pm

Re: php mysql date

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: php mysql date

Post by RobertGonzalez »

Don't wrap NOW() in quotes
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: php mysql date

Post 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'
                )";
?>
phpsynner
Forum Newbie
Posts: 4
Joined: Mon Jul 21, 2008 12:32 pm

Re: php mysql date

Post by phpsynner »

thanks looks a lot better.
manixrock
Forum Commoner
Posts: 45
Joined: Sun Jul 20, 2008 6:38 pm

Re: php mysql date

Post 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));
Post Reply