...

View Full Version : array -- parse and insert into mysql db



reeljustice
07-17-2002, 01:32 AM
I've been working on a script to read text pages, parse them into various fields, then populate a db with them. I have received help here and read "take items from array and into db" thread below .

I now have the script reading the page, parsing (3 different ways), looping through the files, and echoing.

I am unable to get the array inserted into my db.

I've read the thread below "take items from array and into db" and tried adapting Silky Smooth's recommendation to the script. Only problem is the erro returned says the array is empty after the explode.

Here are the 3 examples of the script working through 6 text files. The information is for testing and is the same for each file, except the file number.

The first example echos the data I want to insert into my db http://www.coastaloutdoors.net/dev_joel/reports/loop_test/read_loop_no_desig.php

The second example precedes the data by the index of the array return (I think) http://www.coastaloutdoors.net/dev_joel/reports/loop_test/read_number_desig.php

The 3rd example precedes the data by the name of the field I have parsed which is the same name as the field name in the db I want to populate. http://www.coastaloutdoors.net/dev_joel/reports/loop_test/read_loop_var_title.php

I tried several different ways of inserting the array data into the db before trying to adapt Silky's code to the Example 1 above. This is the URL http://www.coastaloutdoors.net/dev_joel/reports/loop_test/read_loop_number_desig_insert.php

This is the return from running the script:

0 Delaware Bay, Delaware
1 Bo French (ip 208.0.47.37)
2 7/14/2002
3 7/13/02
4 .
5 1- 2'
6 Calm
7 Overcast then clear with a light SE breeze.
8 Flounder
9 Caitlin
10 Bo French
11 Fishing with Matt and Joanna French. Started at "D" buoy about 1000 and made 3 drifts. Caught 2 flounder and kept 1 (18.5 " and 2.4 #). About 1200 moved to "F" buoy and made 2 drifts with the end the of the incoming tide. Picked up 4 more flounder and kept 2 (17.5 -18"). Also, caught and released 3 croakers and 1 blue. To add a remark to this report click here.
You successfully connected to the database server.

You successfully connected to the database coastal_outdoors.

Error adding submitted data from 46182You have an error in your SQL syntax near '
11 Fishing with Matt and Joanna French. Started at "D" buoy about 1000 and ' at line 1 SQL: INSERT INTO report_data (subject, submitted_by, fished_on, report_received, water_temp, water_clarity,seas, weather, fishing_for, boat, captain, report) VALUES (
11 Fishing with Matt and Joanna French. Started at "D" buoy about 1000 and made 3 drifts. Caught 2 flounder and kept 1 (18.5 " and 2.4 #). About 1200 moved to "F" buoy and made 2 drifts with the end the of the incoming tide. Picked up 4 more flounder and kept 2 (17.5 -18"). Also, caught and released 3 croakers and 1 blue. To add a remark to this report click here. )


0 Barnegat, New Jersey
1 Fred (ip 205.188.199.152)
2 7/13/2002
3 7/13/02
4 70s
5 clear
6 2to3
7 .
8 fluke
9 Rock&roll4
10 Fred
11 Went out 5:30am back at 11:30am no doormats all the fish were between 17 20in To add a remark to this report click here.
You successfully connected to the database server.

You successfully connected to the database coastal_outdoors.

Error adding submitted data from 46183You have an error in your SQL syntax near '
11 Went out 5:30am back at 11:30am no doormats all the fish were between 17' at line 1 SQL: INSERT INTO report_data (subject, submitted_by, fished_on, report_received, water_temp, water_clarity,seas, weather, fishing_for, boat, captain, report) VALUES (
11 Went out 5:30am back at 11:30am no doormats all the fish were between 17 20in To add a remark to this report click here. )

The script appears to be connecting to the db and table and appears to be looping through the files but is giving errors as you can see. I suspect there is something wrong with my SQL statement, or the way in which I am trying to populate.

These are the fields in the db:

record_ID
subject = Shellman Bluff, GA
submitted_by = Joel Williams
fished_on = 7/11/2002
report_received
water_temp
water_clarity
seas
weather
fishing_for
boat
captain
report

I would appreciate anyone's help. This is the code:




<?php

$vars = array(
'Subject:',
'Submitted by',
'Fished on',
'Report received:',
'Water Temperature:',
'Water Clarity:',
'Seas:',
'Weather:',
'Fishing_for:',
'Boat:',
'Captain:',
'Report:'
);

$ends = array('<!-- the following post must point at the correct url and ini-->');

//set the variable for the starting webpage to pull the data from
// for($p = 46182; $p < 46188; $p++)
// {

for($p = 46182; $p < 46188; $p++)
{
$page = ("http://www.wmi.org/saltfish/reports/".$p.".htm");

// Define all variables you want to search for

// read the file as an array and join the lines; now it is one string
$filename = ($page);
$file = join( '', file( $filename ));

// replace each var with a token, using a character that
// normally does not appear, like (ascii 178)
foreach( $vars as $key => $val ) {
$file = preg_replace( "/$val/i", "".$key."", $file );
}

// mark the end of the last item
foreach( $ends as $key => $val ) {
$file = preg_replace( "/$val/i", "".$key."", $file );
}


// now you can easily extract each variable
if( preg_match_all( "/(\d+)([^]+)/s", $file, $matches, PREG_SET_ORDER )) {
foreach( $matches as $match ) {
//echo "<br>$match[1] " . strip_tags($match[2]);
//$result = (strip tags($match[2]));
//if($result)
//{
//echo ($result);
//}
$result = "<br>$match[1] " . strip_tags($match[2]);
echo ($result);
}
}
//begin db connect code and database insert code
// Connect to the database
$dbcnx = @mysql_connect( //use the @ symbol here so that we can print a nicer error message
"server-16.mcc-sys.com", "xcoastaloutdoors", "reel663960");

if(!$dbcnx){
echo("<p>Unable to connect to the" .
"database server at this time.<?P>");
exit();
}else{
echo("<P>You successfully connected to the database server.</P>");
}

//Select the coastal_outdoors database
if(!@mysql_select_db("coastal_outdoors")){
echo("<P>Unable to locate the coastal_outdoors" .
"database at this time.<?P>");
exit();
}else{
echo("<P>You successfully connected to the database coastal_outdoors.</P>");
}

//If we have a returned array,
//add it to the database.
if($result){
$sql="INSERT INTO report_data (subject, submitted_by, fished_on, report_received, water_temp, water_clarity," .
"seas, weather, fishing_for, boat, captain, report) VALUES ($result)";
if(mysql_query($sql)){
echo("<P>Your data for record $p has been added to the database table report_data.</P>");
}else{
echo("<p>Error adding submitted data from $p".
mysql_error(). " SQL: $sql</P>");
}
}
//end db connect code and database insert code

} //this brace closes out the opening brace on the if statement
?>



The file(s) being read are similar to the following, in
pertinent part of reports_25000:


<!--APPEND COMMENTS HERE -->
Subject: Edisto, St. Helena, South Carolina<p>
Submitted by <a href=mailto:mmiller193@aol.com> m miller <font size=-1>(ip 152.163.204.66)</font></a> <ul><li>Fished on 5/2/99<p>
<li>Report received: 12/11/99</ul><p>
Water Temperature: 68<br>Water Clarity: murky<br>Seas: rough<br>Weather: cold and turbid<br>Fishing_for: flounder/cobia<br>Boat: sea hunt<br>captain: t palmer<br><p>Report:<br> no luck anywhere, but weathers real bad as are seas. Does anyone have any word on Cobia, or Spadefish?? They here yet?? Heard the Cobia are biting in the Broad River at the bridge, but seems a bit early. Guess they are hitting crabs and eels? Would deeply appreciate any kind advice, and maybe I can return the favor.<p>
<p><p>

<P>
<!-- the following post must point at the correct url and ini-->
<p></p>
<HR WIDTH=80% SIZE=5 color=navy>
<a href="http://www.coastaloutdoors.com/saltfish/advertising.html">Advertising/Sponsor Information</a><p></p>
<a href="http://www.coastaloutdoors.com/saltfish/index.html">Return to the Saltwater Fishing Home

mordred
07-17-2002, 10:06 AM
Yep, your SQL syntax seems definitely broken.
You have to use single or double quotes to enclose string contents for any field you want to insert.

Also, this line...



foreach( $matches as $match ) {
//echo "<br>$match[1] " . strip_tags($match[2]);
//$result = (strip tags($match[2]));
//if($result)
//{
//echo ($result);
//}
$result = "<br>$match[1] " . strip_tags($match[2]);
echo ($result);
}



might have a flaw in it. You always assign a new value to $result instead of appending the values of $match[1] and $match[2]. Perhaps you rather wanted something like



foreach( $matches as $match ) {
$result .= "'" . addslashes(strip_tags($match[2])) . "', ";
echo ($result);
}

$result = substr($result, -3);


Note that I stripped the last three characters from the end result of $result, because without that you have a query that ends with " ', " which would result in a new SQL error.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum