Parameter Time format handling and mysql query

[Gargalash]

Hello!

I know very little about php and I have problem which leaves me stuck. I actually don't know what terms to use in searching the web and forums... I have looked around quite a bit unsuccessfully.

 

Here's the thing: I have a php script that is called by the billing company after someone successfully made a payment. When the script is called parameters are passed. Some of these parameters are then used to insert information in a DB that contains the members access code and password. One of the parameters is an expiration date: "EXPIRE".

 

If I have my script mail me the value of the EXPIRE parameter when I make a test payment, I get something in this format: 13/06/2010_17:37:49

 

If I make a test payment, the script will also mail me there if there is an error inserting the new members data in the DB ( I'm unable to make the error reporting more precise). I suspect this is caused by time and date formating. So EXPIRE is in this format 13/06/2010_17:37:49 and my mySQL query is:

$query = "INSERT INTO mytable (username,password,expiration) VALUES ('$pinCode','$pinCode',FROM_UNIXTIME($expirationDate))";

 

That script used to work fine with another billing company using the same method of invoking a script with some parameters after a successful payment. The difference was that they were using a UNIX Timestamp as the expiration date.

 

Also, if I invoke my script in a browser and add the parameter myself, it will work fine:

thescript.php?CODE=289374893&RENEW=0&EXPIRE=34908157

With this EXPIRE value, the insertion in the DB will run ok and EXPIRE will insert 1971-02-08 19:42:37 in the "expiration" field of the table.

 

So, I am assuming that the time format is causing problem. Can I manipulate it to suit my needs?

Or maybe it's the type of the field "expiration" in the table that needs to be modified? It is currently set as "datetime".

 

Thanks you very much in advance for any help!

[Gargalash]

Went on with search, more concluding this time.

 

Ok, I have tried this to make my date string a timestamp:

$expirationDate = strtotime($_GET['EXPIRE']);

and had the script mail me the value of $expirationDate and I got emptiness.

 

I have also noticed that this format: 13/06/2010_17:37:49 is day/month/year. So strtotime() can't use it as it is not the american-style dates (month/day/year), even if I could manage to remove the "_" between the year and the hour. The billing company is actually french, and they mix english and french in the way they make business, obviously they blew it on choosing the datetime format....

 

Any ideas as to how to convert that weird format to a timestamp?

 

Thanks!

[kenrbnsn]

Something like this should work:

<?php
$ts = '13/06/2010_17:37:49';
list($p1,$p2) = explode('_',$ts);
list($d,$m,$y) = explode('/',$p1);
$new_ts = "$y-$m-$d $p2";
echo $new_ts;
?>

 

Ken

[Gargalash]

I have now forced a date to make a test:

$expirationDate = strtotime("22 Dec. 1979 17:30");

 

This created the correct timestamp and the script went through and the info was correctly inserted in the DB.

 

The problem is now confirmed to be the date-time format, and it's conversion...

 

kenrbnsn, I just read your reply, thanks for this!

I don't understand at the moment, re-reading it will help, and I will adapt it.

I'll get back with results!

[Gargalash]

kenrbnsn,

Sorry for the late reply.

 

So, your little script did the work just right, but you probably already know this

It was very interesting for me to learn from this, thanks a lot!