Timestamp problems again with PHP, MySQL

software development

#1

I’ve been mislead by PHP error messages again, but now I see what my real problem is. I’m screen scraping some river flow data and associated dates into a MySQL table. I thought I was converting the date to Unix Time Stamp format and saving in the MySQL Table with code like:
for ($i=3; $i<count($aContents)-1; $i++) {

// Dates are formatted as 10 characters
$cDateStr = substr($aContents[$i],0,10);
#convert date string (a date in human language) to a date in programming language
$myDates = strtotime($cDateStr);

// QD is everything in the trimmed value after the last space
$nQDVal = substr($aContents[$i], strrpos(trim($aContents[$i]), chr(32))+1);
$query = “insert into Flow values (’”.$myDates."’, ‘".$nQDVal."’, ‘".YRPW."’)";
$result = $db->query($query);
}

My table gets filled up with some nice integers that look like datestamps. But when I try to call them back for plotting with a SQL statement like “Select UNIX_Timestamp(DATE), QD From Flow WHERE Site = “RBDW””, I only get 0’s back for the first parameter in the row. What am I not understanding?

This signature line intentionally blank.


#2

Why are you bothering to format this into a Unix timestamp? Save it as a mysql date and reformat when you pull the data out.

FYI, you should do your count($aContents) outside of your for loop, which will cause it to execute once. Putting that inside the for loop means PHP has to run the calculation each time the loop begins. It’s more efficient to do it outside the loop.

================================
Angela Gann
CrimsonDryad Web Design Services
Web Design, Custom Software Development
http://www.crimsondryad.com


#3

I ended up saving the date as an integer item in mysql, then retrieving it with syntax like Select DATE, QD From Flow WHERE DATE > unix_timestamp(“2006-04-13”) and DATE < unix_timestamp(“2006-07-31”)

Results: http://ykfp.org/php/BOR/rozadbflowtempchart3.php

I appreciate your idea on the count. I’ll have to try it.

This signature line intentionally blank.