PHP-MySQL: Date Time Formatting Issue

G

guzmanistan

Weaksauce
Joined
Jun 3, 2005
Messages
92
Hi all,

Thanks for the help ahead of time. So, I'm working on this site and am basically trying to work a php script to upload a CSV to a MySQL database. It works fine except for the timestamp (column = cdatetime). I know that the format in the DB is "YYYY-MM-DD HH:MM:SS", and have changed the format in the CSV accordingly. I've tried a crap ton of different ways to format it to read it in thinking the DB might be converting the numbers over, but its just not reading in. I'm getting this error when performing the operation:
Could not update the values! 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 '18:01:19, 54.255, 57.376, 56.208)' at line 2
Click to expand...
This makes me think its something with my code since its not uploading the datetime value (the first value before the comma) before the spacer. Does anyone have any ideas? Is there some wierd formatting thing I'm missing out on?
Thanks again - Guzmanistan

Relevant code:
Code: 
while (!feof($file_handle) ) {
  $data = fgetcsv($file_handle, 1024);
  echo $data[0].'<br>';
  echo $data[1].'<br>';
  echo $data[2].'<br>';
  echo $data[3].'<br>';
  // Trying out a bunch of different date format techniques
  echo $datetime = explode(' ',$data[0]);
  echo $date = explode('-',$datetime[0]);
  echo $time = explode(':',$datetime[1]);
  $year = $date[0];
  echo $year.'<br>';
  $month = $date[1];
  echo $month.'<br>';
  $day = $date[2];
  echo $day.'<br>';
  $hour = $time[0];
  echo $hour.'<br>';
  $min = $time[1];
  echo $min.'<br>';
  $sec = $time[2];
  echo $sec.'<br>';
//
  $datetime_fin = date("Y-m-d H:i:s", mktime($hour, $min, $sec, $month, $day, $year));
  $datetime2 = mktime($hour, $min, $sec, $month, $day, $year);
  $mysqldate = date("Y-m-d H:i:s", $data[0]);
  echo 'date fxn'.$mysqldate.'<br>';
  echo 'date fxn:'.$datetime_fin.'<br>';
  echo 'mktime fxn:'.$datetime2.'<br>';
  //echo strtotime($data[0]);
  //$datetime = date('Y-m-d H:i:s',$date[0]);
  //$datetime = strtotime($data[0]);
  //
//
  $n_id=1;
  $query = "INSERT INTO nodedata (nodeid, cdatetime, tempsoilx, tempairx, tempwaterx) 
    VALUES ($n_id, $datetime_fin, $data[1], $data[3], $data[2])";
  $result = mysql_query($query);
  if($result) {
    echo'<p style="color:green;">All values were calculated and updated in the table</p>';
    }
  else {
    echo'<p style="color:red;">Could not update the values! '.mysql_error();
    echo'</p>';
    }
} //closing WHILE LOOP
fclose($file_handle);
 
You're not quoting the date string.

This whole script is pretty dangerous, none of your values are sanitized or even quoted in the query. You should really be using a database library with paramaterized execution, it will solve all of these problems and give you a much nicer interface than any other solution, along with some other benefits. I think such a library has *finally* made it into PHP, but I can't remember what it's called.
 
You mean like the actual raw numbers? This:
1, 2009-10-09 18:16:19, 54.169, 57.42, 56.208
Click to expand...

It reads all of the CSVs except for "2009-10-09" when it shows the error text.
 
I meant if you echoed it what did the variable contain. A bit irrelevent now because of what keenen pointed out though.
 
You need to quote it as mentioned, the error it see's is due to the space between the date and the time.
 
I'm a bit new to this - should quoting the data string solve the problem?
What exactly is my data string? Should I be putting quotations around the VALUES part of this?
$query = "INSERT INTO nodedata (nodeid, cdatetime, tempsoilx, tempairx, tempwaterx) VALUES ($n_id, $datetime_fin, $data[1], $data[3], $data[2])";
Click to expand...

Still a little lost: this is what $query echoes (if this can help anyone):
INSERT INTO nodedata (nodeid, cdatetime, tempsoilx, tempairx, tempwaterx) VALUES (1, 2009-10-09 18:01:19, 54.255, 57.376, 56.208)
Click to expand...

Thanks again,
Guzmanistan
 
Last edited:
Look again at Toytown's comment... Though keenan provided the better solution.

Edit: Actually, keenan is more specific on the cause of the error.
 
Last edited:
guzmanistan said:
What exactly is my data string? Should I be putting quotations around the VALUES part of this?
Click to expand...

*date* string. Any string (ie. anything that's not a number) should be quoted with ' around it; spaces and special characters will be interpreted as part of the query and not part of the data. You should also make sure that the strings don't contain any 'special' characters that may cause undesired behaviour of the query.

The real solution to this that I mentioned would be to use a library that handles all of the automagically for you. The one I was thinking of in PHP is PDO.

Instead of writing this (which is ugly, hard to read, and error prone):
Code: 
$query = "INSERT INTO foo VALUES ('" . mysql_real_escape_string($val[0]) . "', '" . mysql_real_escape_string($val[1]) . "'";
mysql_query($query);

You write this (and PDO is really not as clean as it could be...):

Code: 
$params = array($val[0], $val[1])
$query = $db->prepare("INSERT INTO foo VALUES (?,?)");
$query->execute($params);

And you can reuse the query object with different paramaters (ie. it can be created outside the loop), and some database engines provide some performance optimization when you do this as well.

A couple other potential issues I see - is $n_id supposed to be unique, because you just set it to 1 and do nothing else with it - seems kinda pointless ;). Also how do you handle errors? Currently all the added rows will remain in the database after an error, potentially resulting in lots of duplicate rows if the user tries again. You probably want to use a transaction so the whole shebang gets rolled back if one query fails.
 
Hi all, thanks for all of the help!

I finally figured it out...I went the lazy way around fixing the problem, quoting all of the variables for VALUES (if this helps anyone else):
$query = "INSERT INTO nodedata (nodeid,cdatetime,tempsoilx,tempairx,tempwaterx)
VALUES ('$n_id', '$datetime_fin', '$data[1]', '$data[3]', '$data[2]')";
Click to expand...

I really should spend more time figuring out php and MySQL - I thought that putting quotes around the variables would make them text (ex: $n_id would be sent to the DB instead of the value it represents) - just found a nice writeup distinguishing the two: http://v1.jeroenmulder.com/weblog/2005/04/php_single_and_double_quotes.php..

Again...thanks Keenan, PTNL, Toytown, and Sgraffite for all of your help. This page will be redone soon and I will definitely take a closer look at coding better with library and PDO.
 
guzmanistan said:
I really should spend more time figuring out php and MySQL - I thought that putting quotes around the variables would make them text (ex: $n_id would be sent to the DB instead of the value it represents)
Click to expand...

Yeah, this is why my example used all those ugly concatenations to get the variables in there properly. You have to be careful even inside double quotes that your variables are expanded properly.

Also, I strongly urge you to run your input through mysql_real_escape_string() (or whatever the recommended function is called now...), or at the very least addslashes(). If a single quote appears anywhere in your input it's going to blow up.
 
Another method of concatenating in php which I think looks a bit cleaner:
Code: 
$var1 = 'abc';
$var2 = 'def';
$sql = "select * from table1 where col1 = '{mysql_real_escape_string($var1)}' and col2='{mysql_real_escape_string($var2)}'";

FYI you can't insert constants like this for some reason.
 
You must log in or register to reply here.
Back
Top