PHP + MySQL question (inserting datetime?)

[CTho9305]

I'm having trouble inserting dates into the db... the field is of type datetime. Here is the system. The problem is with adding drives - specifically the purchase date. Here is the source for the page to add drives, and this is the page that does the listing. Anyway, the problem is that the date the user entered is generally either ignored or otherwise messed up.

(my apologies for any foul language in debug code / comments 😉)

edit: I don't verify users right now, and most of the data there is obviously junk.

 

[HJB417]

Maybe you should have a dropdown menu to select a date and use php's function checkdate() to make sure the entered a vaild date.
Like, Entering Feb 31, 2003 would return false in checkdate.

 

[CTho9305]

That isn't the problem. I am entering valid dates. For now I don't care about input validation - I just need it to work with known-good data.

 

[eklass]

you need to manipulate the strings in php to get them in the correct format for mysql

this should help http://www.mysql.com/doc/en/DATETIME.html

i tend to prefer timestamp over datetime

it's easier and offers more flexability if i ever decided to conpress or expand how definite i want it (to the day in TIMSTAMP(8) or to the second in TIMESTAMP(14))

 

[HJB417]

Try copy pasting the below code after you echo crap php crap:

$tmpdate = $year . "-" . $month . "-01" . " 01:01:01";
$tmpdate = "\"" . addslashes($date) . "\"";
$owner = "\"" . addslashes($owner) . "\"";
$manufacturer = "\"" . addslashes($manufacturer) . "\"";
$model = "\"" . addslashes($model) . "\"";
$size = "\"" . addslashes($size) . "\"";
$rpm = "\"" . addslashes($rpm) . "\"";
$load = "\"" . addslashes($load) . "\"";
$comments = "\"" . addslashes($comments) . "\"";

$sql = "INSERT INTO t_drive (user_id, manufacturer_id, model, size, rpm_id, purchase_date, load_id, comment, failure_date) VALUES($owner, $manufacturer, $model, $size, $rpm, $tmpdate, $load, $comments, NULL)";
@$res = mysql_query($sql);
if(!$res)
{
//error occured while performing mysql query.
}
if(mysql_affected_rows() == 0)
{
//data was not inserted into database.
}

 

[CTho9305]

same problem 🙁

 

[Gaunt]

In the case where you're going to try timestamp as Eklass said:

Careful with the timestamp data type in MySQL, it has some automatic functionality included with it that can cause problems if you're not aware of it. Check the MySQL Data type documentation.

Usually when I'm inserting dates into the table (using the datetime data type), I use something like the following:

$Month = $_GET['month'];
$Year = $_GET['year'];
$DateValue = date("Y-m-dH:i:s", mktime(0,0,0,$Month, 1, $Year));

Then use your insert query, almost identically as you had before, replace $tmpdate with $DateValue, and all should be good. Why your current code isn't working, I'm not quite sure yet. 🙂 The fact that you have -1 for the day is a little confusing... does mysql support that?

 

[CTho9305]

It was a combination of a bug inserting and a bug selecting. Got both fixed with HJB's help 🙂

 

[eklass]

if gpc_magic_quotes is set to on, you're doing extra work not needed to be done (the addslashes() around everything)

sure people say it's bad, but it helps to heep code clean

 

[Barnaby W. Füi]

Originally posted by: eklass
if gpc_magic_quotes is set to on, you're doing extra work not needed to be done (the addslashes() around everything)

sure people say it's bad, but it helps to heep code clean

Bah, that thing needed to be looped anyways.

$tmpdate = "\"" . addslashes($date) . "\"";
$owner = "\"" . addslashes($owner) . "\"";
$manufacturer = "\"" . addslashes($manufacturer) . "\"";
$model = "\"" . addslashes($model) . "\"";
$size = "\"" . addslashes($size) . "\"";
$rpm = "\"" . addslashes($rpm) . "\"";
$load = "\"" . addslashes($load) . "\"";
$comments = "\"" . addslashes($comments) . "\"";

$fields = array("tmpdate", "owner", "manufacturer", "model", "size", "rpm", "load", "comments");
foreach($fields as $name=>$text)
$GLOBALS[$name] = sprintf('"%s"', addslashes($text));

Not quite so dirty 🙂