Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jul 2002
    Location
    GA, US
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    array -- parse and insert into mysql db

    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_j...p_no_desig.php

    The second example precedes the data by the index of the array return (I think) http://www.coastaloutdoors.net/dev_j...mber_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_j..._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_j...sig_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 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$matchesPREG_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

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

    PHP Code:
    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

    PHP Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •