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 3 of 3
  1. #1
    Regular Coder
    Join Date
    May 2004
    Location
    Hudson Valley, NY
    Posts
    147
    Thanks
    6
    Thanked 0 Times in 0 Posts

    upload csv file and import into mysql

    Me again!
    I have this code to import a csv file into mysql:
    PHP Code:
    <?php
    include "includes/stconfig.php";
    if(isset(
    $_POST['submit']))
       {
         
    $filename=$_POST['filename'];
         
    $handle fopen("$filename""r");
         while ((
    $data fgetcsv($handle1000",")) !== FALSE)
         {
        
           
    $import="INSERT into table1(test1,test2,test3) values('$data[0]','$data[1]','$data[2]')";
           
    mysql_query($import) or die(mysql_error());
         }
         
    fclose($handle);
         print 
    "Import done";
     
       }
       else
       {
     
          print 
    "<form action='csv_importer.php' method='post'>";
          print 
    "Type file name to import:<br>";
          print 
    "<input type='file' name='filename' size='20'><br>";
          print 
    "<input type='submit' name='submit' value='submit'></form>";
       }
    ?>
    What I need is to be able to select a LOCAL file, have it uploaded to a temp directory and imported into mysql, all from a web page. I don't dare turn this loose with myadmin, as the users will be nubes of the most extreme!
    Anyway, is there a simple way to get what I have working?
    Andy
    (Management+Coworkers)<>Logic

  • #2
    New to the CF scene
    Join Date
    Apr 2007
    Location
    Cannon Falls, Minnesota, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upload and parse file for mysql database insert

    The php manual pages provide examples of what you need. Check the page for is_uploaded_file():

    http://us2.php.net/manual/en/functio...oaded-file.php

    and confinue from there to the link on handling file uploads (chapter 38) for complete examples on getting to the uploaded file. Since you're dealing with noobs of the most extreme, you really need to do some serious validation of the incoming data before slamming it into your database. There is a PEAR module for dealing with incoming CSV data. At a minimum, run that data through the mysql escape function after replacing left angle bracket '<' with the html entity '&lt;'. (Don't know if they get stripped out of this posting.)

    If you write your code assuming some hacker will use this web page to attack your database, you won't be disappointed! Clean and validate your data prior to insertion.

    Ed Barnard

  • #3
    Regular Coder
    Join Date
    May 2004
    Location
    Hudson Valley, NY
    Posts
    147
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hey Ed,
    yeah, I know it needs to be validated to death; I'm just starting this whole mess. I fixed the upload so I can select the local file, but there are still issues:
    [PHP<?php
    include "includes/stconfig.php";
    if(isset($_POST['submit']))
    {
    if ($_FILES["filename"]["error"] > 0)
    {
    echo "Error: " . $_FILES["filename"]["error"] . "<br />";
    }
    else
    {
    echo "Upload: " . $_FILES["filename"]["name"] . "<br />";
    echo "Type: " . $_FILES["filename"]["type"] . "<br />";
    echo "Size: " . ($_FILES["filename"]["size"] / 1024) . " Kb<br />";
    echo "Stored in: " . $_FILES["filename"]["tmp_name"];

    $length = 1000;
    $array = $_FILES["filename"]["tmp_name"];
    for($i=0;$i<count($array);$i++)
    {
    if ($length < strlen($array[$i]))
    {
    $length = strlen($array[$i]);
    }
    }
    unset($array);

    $handle = fopen("$filename", "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

    {
    $import="INSERT into table1(test1,test2,test3,test4,test5) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
    mysql_query($import) or die(mysql_error());


    }
    }
    fclose($handle);

    print "Import done";
    echo $filename;

    }
    else
    {

    print "<form enctype='multipart/form-data' action='csv_importer2.php' method='post'>";
    print "Type file name to import:<br>";
    print "<input type='file' name='filename' size='20'><br>";
    print "<input type='submit' name='submit' value='submit'></form>";
    }
    ?>[/PHP]
    This will allow the local file to be uploaded and inserted.
    NOW...
    some of the array rows will be actual HTML code, and I would like to store them as-is. Plus, all of the array fields should be escaped. isn't there a way to do this using array_walk or something like that?
    Last edited by steamngn; 06-11-2007 at 06:15 PM. Reason: fixed one problem...
    (Management+Coworkers)<>Logic


  •  

    Posting Permissions

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