Php script to insert into MySQL

software development

#1

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[i],$myTemps[i],$site); for ($i=3; $iexecute(); // echo $myDates,$myTemps; } //peep scene echo('
');
print_r($myTemps);
print_r($myDates);
echo('
'); $db->close(); ?>

This signature line intentionally blank.


#2

I don’t know if this will help or not, but I don’t see anything where you’re adding slashes or anything. Is it possible they’ve got some bad html or something in there that’s tanking the code?

I know sometimes the simplest things will trip me up…I hope this is the case for you.

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


#3

??? I’m not trying to write any html. I just want to scrape a page and shove the results into a MySQL table.

This signature line intentionally blank.


#4

I don’t really know anything about mysqli yet, so I can’t offer much help there. This sounds a little bit like there is a possibility that your inserts aren’t getting committed, which would result in the transaction rolling back. Not sure why the last insert succeeds, but maybe the close operation explicitly commits that one or something. You may want to take a look and see if auto_commit is enabled.

It seems to me that making this thing work is more important than the exact method used. What about using the arrays you generated with relevent data to build a multiple record insert statement, and just executing an old fashioned insert statement?

INSERT INTO ziplonglat (zipID,zipCode,zipLongitude,zipLatitude,zipRLatitude,zipRLongitude,zipcodetest) values (5,'99995',31.510439999999999,106.14532,0.54996092678200004,1.85258531968,99995), (6,'99996',31.510439999999999,106.14532,0.54996092678200004,1.85258531968,99996), (7,'99997',31.510439999999999,106.14532,0.54996092678200004,1.85258531968,99997), (8,'99998',31.510439999999999,106.14532,0.54996092678200004,1.85258531968,99998), (9,'99999',31.510439999999999,106.14532,0.54996092678200004,1.85258531968,99999)


#5

“What about using the arrays you generated with relevent data to build a multiple record insert statement, and just executing an old fashioned insert statement?”

But can I do that in a loop? Maybe that’s my problem. I can’t find an example of an insert inside a loop. I’ve also tried the following approach, but again only get one record in the table.

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;
$query = “insert into ProTemp values
(’$myDates’, ‘$nQDVal’, ‘YRPW’)”;
// (’".$myDates."’, ‘".$nQDVal."’, ‘".YRPW."’)";
$result = $db->query($query);
//echo $myDates,$myTemps;
}

This signature line intentionally blank.


#6

“You may want to take a look and see if auto_commit is enabled.”

I inserted the line
$db->autocommit(TRUE);

after opening the connection $db, but got the same results.

This signature line intentionally blank.


#7

I’ve also tried the following code where I screen scrape the data into two arrays, the arrays print_r correctly, so then I go into a loop to try to insert the array contents into the MySQL table, one record per loop, but I still end up with only the last record in the table, the same as before:

echo(’

’);
print_r($myTemps);
print_r($myDates);
echo(’
’);

$site = “YRPW”;
for ($i=0; $i<count($myTemps)-1; $i++) {
$query = “insert into ProTemp2 values ($myDates[i],$myTemps[i],$site)”;

}
$db->close();

So it must have something to do with looping, not the msqli_prepare stuff I started with. I can’t seem to find any php example that inserts more than one record in a table.

This signature line intentionally blank.


#8

I’ve been barking up the wrong tree all this time.

There’s no problem with mysqli, no problem with inserts in the loop. I tried a lot of crazy things. I tried the mysql methods rather than mysqli. I tried many combinations of the query statement, with and without []‘s on the arrays, without mentioning arrays, "like insert into ProTemp values (’".$myDates[$i]."’, ‘".$nQDVal."’, ‘".YRPW."’)"; and like “insert into ProTemp2 values ($myDates[i],$myTemps[i],$site)” . I had so many debugging print statements it was hard to find them to turn them all off. What was happening was I had created a MySQL table with DATE(typed as date or timestamp), WZ(float), and SITE(varchar) with DATE as a primary key and with default value for date as 0000-00-00 00:00:00. I don’t have the date string stuff figured out right, so the date value from $myDates[] = strtotime($cDateStr); wasn’t going into the DATE field at all. So the default value for DATE was inserted. Next time through the loop, MySQL told my script, “Already have a record with the value for your Primary key DATE as 0000-00-00 00:00:00, can’t take another one.” I found this out by printing out the value of $query in the loop and pasting it into a MySQL monitor. When I created a new table with DATE as an Int, a version of my script was able to fill up the table. Now I have to figure out if storing the datestamp as an integer is going to work for plotting or figure out how to correctly type the field.

This signature line intentionally blank.