php mysql date
Moderator: General Moderators
php mysql date
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: php mysql date
Keep it on the mysql side. Make the field a datetime field and sort by the field when selecting.
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: php mysql date
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
Take a look at the MySql manual for more information: MySQL Server Time Zone Support
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;Re: php mysql date
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
but i get a syntax error from mysql on line 1
i believe i have a quote misplaced or something anyone got an idea.
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']."',
)";
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- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: php mysql date
Don't wrap NOW() in quotes
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: php mysql date
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
thanks looks a lot better.
Re: php mysql date
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:
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));