Php script to insert into MySQL
Posted by: MajorGeek
Posted on: 2006-12-20 15:18:00
I'm still at this project where I screen scrape water quality data from some BOR urls to plot with my MySQL data. I've produced some nice looking charts but when I try multiple y-axis charts, the run time is a bit slow because I have to wait for the BOR site for each water quality parameter. (http://ykfp.org/php/BOR/proflowtempchart.php). So I thought I might speed things up if I had a cron run once a day after the BOR updates their database to bring the data I want into local MySQL tables.
I thought that mysqli_stmt_bind_param method would work well for me, since I could prepare the statement outside the loop reading in lines and put just the ->execute inside the loop. I set up my MySQL table ProTemp2 with columns Date(timestamp), WZ(double), SITE(char). But when I run this script, I only get one record in the MySQL table with date = 12/20, today's date, null WZ, SITE = "YRPW". The print_r lines show that the date and temperature arrays are getting filled in the loop. Why not the MySQL table?
<?php
//getProTemp2.php
//get Prosser temperatures from BOR website, put into MySQL table on my webhost
// Paul Huffman, 12-20-2006
require_once("../ChartDirector/lib/phpchartdir.php");
//retrieve temperature data from BOR database
$theurl="http://www.usbr.gov/pn-bin/yak/arc3.pl"
."?station=YRPW&year=2006&month=4&day=13&year=2006&month=12&day=31&pcode=WZ";
$ch = curl_init();
$timeout = 5; // set to zero for no timeout
curl_setopt ($ch, CURLOPT_URL, $theurl);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
$contents = curl_exec($ch);
curl_close($ch);
// display file
echo $contents;
// The feed appears to have useful parsing point identifiers.
// I just used them to get everything between them (including flags)
$cStartStr = "BEGIN DATA";
$cEndStr = "END DATA";
$cPageTail = stristr($contents, $cStartStr);
$nUsefulDataEndPos = strpos($cPageTail, $cEndStr);
$cUsefulData = substr($cPageTail, 0, $nUsefulDataEndPos);
// explode the content using newlines as delimeters
$aContents = explode(chr(10), $cUsefulData);
// i'll be putting the line items into an array. Two array types are used, choose one according to your preference
// No, in this case, I'll try to read in values, then stick into database without declaring arrays
//$aDateQD1 = array();
//$aDateQD2 = array();
// Two new arrays initialized to empty
$myDates = array();
$myTemps = array();
// Set up and test database connection
@ $db = new mysqli('bor.ykfp.org', 'xxxxxxx', 'xxxxxxx', 'bor');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
// skip the leading and trailing junk
// Prolly don't want to do all these assignments in the loop. They're just used for readability
$query = "insert into ProTemp2 values (?,?,?)";
$stmt = $db->prepare($query);
$site = "YRPW";
$stmt->bind_param("ids",$myDates,$myTemps,$site);
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);
$myTemps[] = $nQDVal;
$stmt->execute();
// echo $myDates,$myTemps;
}
//peep scene
echo('<pre>');
print_r($myTemps);
print_r($myDates);
echo('</pre>');
$db->close();
?>
This signature line intentionally blank.