Problem of storing time in mysql

[Ahmad_Sqalli]

Hello
First, sorry for the English if it’s bad …
I m working in localhost ( XAMPP ) php version is 5.4.4, and PHPmyAdmin v3.5.2
this problem is complicated, is about storing time in SQL table, here is the php code that I use to store time

  if (mysql_query('insert into users (username, password, email, regdate) values ("'.$username.'", "'.$password.'", "'.$email.'", "'.time().'")')){
    echo 'SQL Inserted !';
}
else { echo 'Error'; }  

and this is the SQL table

CREATE TABLE `users` (
      `id` bigint(20) NOT NULL auto_increment,
      `username` varchar(256) NOT NULL,
      `password` varchar(256) NOT NULL,
      `email`  varchar(256) NOT NULL,
      `regdate` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and finally

$user_req = mysql_query("SELECT * FROM users WHERE id='$user_id'");
if(mysql_num_rows($user_req)>0){
    $user_info = mysql_fetch_array($user_req);
    echo 'username : ' . $user_info['username'] . '<br/>';
    echo 'User since : ' . date('d/m/Y H:i a',$user_info['regdate']) . '<br/>';
}

the problem here is that if the registration time is 30/07/2015 01:04 am
The output goes like this 30/07/2015 03:04 am
I don’t know from where the 2 hours came from, any way I tried in another script, I’ve even reinstall XAMPP, but didn’t work.
I also tried to use ’ timestamp ’ and ’ datetime ’ but the sql value store like this " 0000-00-00 00:00:00 " that’s why I use bigint()
I don’t know what is wrong with my code, does any one know ??

[SpacePhoenix]

Have you checked what timezone the server is using?

btw you’ve got a bigger problem then that, the old mysql_* extension which your code uses is depreceated in version 5.5 of PHP and is being removed in version 7 of PHP. You really need to be migrating your codebase over to either the mysqli_* extension or to PDO (PDO is better then the mysqli_* extension is it’s database server independent - apart from when you use any sql syntax specific to a particular database server - and it allows for named parameters.

Get into the habit once you’ve migrated over to either rthe mysqli_* extension or PDO of always using prepared statements when dealing with any user submitted data to prevent SQL Injection attacks. All user submitted data needs to be validated before it’s let anywhere near to the database.

[Ahmad_Sqalli]

I’ve checked the php.ini file and I found this :: date.timezone = Africa/Casablanca , where I m living
and is it important to change Mysqli or PDO ?

[SpacePhoenix]

@Ahmad_Sqalli

and is it important to change Mysqli or PDO ?

It is, if you’re still using the old mysql_* extension when your host upgrades the server to PHP version 7 your site will instantly break and will be broken until you migrate over to either the mysqli_* extension or to PDO

Have a read of this article http://www.sitepoint.com/migrate-from-the-mysql-extension-to-pdo/ it goes through the basics of migrating over to PDO

[Ahmad_Sqalli]

Thanks man

[Ahmad_Sqalli]

and is PDO is better than mysqli ?