Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    10

    Unanswered: query to upload csv file to table error

    I am using a webpage that simply runs this query (so client can use it).
    It is supposed to empty the table (the theory being that it resets the auto-incrementing ID to 1 again - which it doesn't!).

    Then it should take the file off the clients desktop, upload it to the server and put it into the table. There's something wrong with the syntax of the second query yet it is EXACTLY the same, apart from the file location, as what DOES work when you use phpmyadmin.

    Tired......look forward to your help. Thanks. Incidentally probably don't need the TRUNCATE query if I use REPLACED in the second one.

    Error given is ....unexpected T_CONSTANT_ENCAPSED_STRING

    {
    // Run query
    mysql_query("TRUNCATE TABLE `books`", $eg_objConn1);
    mysql_query("LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Jon/Desktop/anita.csv' REPLACE INTO TABLE 'books' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 lines", $eg_objConn1);
    }

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    In - LOAD DATA LOCAL INFILE - the local reference is with respect to the PHP script on the server. If does not mean a remotely located file where the browser is that is viewing the web page.

    To do this you must upload the file to the web server.

    The reason for the error message is most likely due to the un-escaped double-quote in the ENCLOSED BY '"' clause. When having errors due to a query string, always form the string in a variable and echo it to make sure that it contains the expected contents. Also, having the table name 'books' in single-quotes instead of without anything or in back-ticks `books` is likely a problem.

    Edit: To avoid possible debate. The "client" reference in the mysql manual referrers to the client that is making the call to the mysql server, in the case of a PHP script, the client is the PHP script/php_mysql.dll library.
    Last edited by dbmab; 07-12-06 at 20:07.

  3. #3
    Join Date
    Jul 2006
    Posts
    10
    error message is most likely due to the un-escaped double-quote in the ENCLOSED BY '"'
    jon> As I copied it from phpadmins own query when I asked it to upload the file - I'm surprised...but you're right. Putting '\"' works (I think - no error anyway!). Thanks!

    So how can I get it to drag the file from clients desktop - as indeed phpadmin does, presumably store it temporarily on the server, use it then delete?

    I simply saved the phpmyadmin page, 'hid' all the fields and put it in my own web page.
    The only thing wrong with that was that when finished I couldn't see how to stop it going on to where it wanted to (structure) to where I wanted it to (my home page) to hide it's workings from the client.

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The easiest way would be if the web page your client accesses to run this script is a file upload form http://www.php.net/manual/en/features.file-upload.php that accepts the file name, uploads it, then runs the query(s) to empty the table and load the data. There are some file size limits on upload forms. Note that this page should have some authentication in place to prevent anyone from emptying the table and/or loading their own data into the database.

    There are some alternatives. If the mysql server allows remote access, you can use the mysql administrator program http://www.mysql.com/products/tools/administrator/ or a copy of phpmyadmin running on a different server/client computer and there are some PHP scripts that will allow backup/restore operations (some of these can use a helper script on the web server if the mysql server does not allow remote access.)

    An alternative manual method would be to FTP the file onto the server, then browse to the web page that empties the table and loads the data from the file that has been FTP'ed to the site.

  5. #5
    Join Date
    Jul 2006
    Posts
    10
    Thanks for your help.
    The upload script you showed me sends the file to an unknown place (Files will, by default be stored in the server's default temporary directory) not anywhere in my space anyway - so I cant direct the next page to grab it.

    Is there no way to tell it where to send the file - so I can grab it and load the data, please?

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The script can copy the uploaded file from the temporary location to the location you choose, using the move_uploaded_file(...) function.

  7. #7
    Join Date
    Jul 2006
    Posts
    10
    OK - I get irritated by arrogant people who demand that you tell them to do the most complicated things without any effort on their part. I admit I am getting out of my depth spending hours making each step of the way work!
    So - cap in hand and feeling like one of those people...

    I inserted that line of code, as it says:
    bool move_uploaded_file ( string filename, string destination )
    so I wrote:
    bool move_uploaded_file ("anita.csv", "/www/anita.csv" )
    with and without the quotes
    and got
    Parse error: syntax error, unexpected T_STRING in /home/anita/public_html/admin-upload-books1.php on line 3

    Help, please!! (again)

  8. #8
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    This is from the example in the PHP manual -
    PHP Code:
    $uploaddir '/var/www/uploads/'// this is the directory path you want to put the file into - change it to suit your server and needs
    $uploadfile $uploaddir basename($_FILES['userfile']['name']); // this forms the directory/filename where the file will be moved to

    // the following tests if the names/file/upload was valid and moves the file from the temp location to your directory/filename
    if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) {
       echo 
    "File is valid, and was successfully uploaded.\n";
    } else {
       echo 
    "Possible file upload attack!\n";


  9. #9
    Join Date
    Jul 2006
    Posts
    10

    Many thanks

    Thanks for your patience!

  10. #10
    Join Date
    Jul 2006
    Posts
    10

    file MIME type changes

    OK - I have learnt at your feet - thanks again!
    Everything worked fine - all tested and hunkydory til I turn up at the clients place.

    The MIME type I have written in, is text/plain because it is a csv file.
    When I did it from the clients place it failed and listed the file failure as application/octet.

    Where did it get that from? Admittedly I tested it with half a meg files or less, and at the clients it was 4mb. Does php or mysql decide on its own what the file is - and change it from text/plain? Surely not?

  11. #11
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The MIME type I have written in, is text/plain because it is a csv file.
    I am not sure what this means with respect to a file upload form. Can you post code showing what you mean.

    If this is the enctype statement in the upload form, in order for it to work, it must be the following and nothing else - enctype="multipart/form-data"

  12. #12
    Join Date
    Jul 2006
    Posts
    10
    Heres the bit of code. 5 lines down is one MIME reference
    Later on it checks the MIME type - problem being when I changed it to text/plain, it worked yet on clients computer it failed (see bottom of script wth type as application/octet

    How much would you charge to write one mysql query that has me stumped (oevrwriting part if a atble as an update?)

    <?php
    $pw = '******';
    $dir = "./images/"; //Change this to the correct dir RELATIVE TO WHERE THIS SCRIPT IS, or /full/path/
    //MIME types to allow, Gif, jpeg, zip ::Edit this to your liking
    $types = array("text/plain","image/gif","image/jpeg");
    // Nothing to edit below here.
    //Function to do a directory listing
    function scandir($dirstr) {
    echo "<pre>\n";
    passthru("ls -l -F $dirstr 2>&1 ");
    echo "</pre>\n";
    }

    //Check to determine if the submit button has been pressed
    if((isset($_POST['submit'])) and ($_POST['PW'] == $pw)){

    //Shorten Variables
    $tmp_name = $_FILES['upload']['tmp_name'];
    $new_name = $_FILES['upload']['name'];
    $path = $_POST['subdir'];
    $fullpath = "$dir$path/";
    $fullpath = str_replace("..", "", str_replace("\.", "", str_replace("//", "/", $fullpath)));
    $clean_name = ereg_replace("[^a-z0-9._]", "", str_replace(" ", "_", str_replace("%20", "_", strtolower($new_name) ) ) );

    // lets see if we are uploading a file or doing a dir listing
    if(isset($_POST['Dir'])){
    echo "Directory listing for $fullpath\n";
    scandir("$fullpath");
    }else{

    //Check MIME Type
    if (in_array($_FILES['upload']['type'], $types)){

    // create a sub-directory if required
    if (!is_dir($fullpath)){
    mkdir("$fullpath", 0755);
    }
    //Move file from tmp dir to new location
    move_uploaded_file($tmp_name,$fullpath . $clean_name);
    echo "<h6>$clean_name of {$_FILES['upload']['size']} bytes was uploaded sucessfully!</h6><br><br>Check the <a href='index.php'><strong>Index page</strong></a>?<br><br>Back to the <a href='admin.php'><strong>Admin page?</a></strong><br><br>";

    //trial bit
    include ("include-db-connect.html");
    mysql_query('TRUNCATE books');
    ## CSV file to read in ##
    $CSVFile = 'anita.csv';
    mysql_query('LOAD DATA LOCAL INFILE "images/anita.csv" replace INTO TABLE books FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error());




    }else{

    //Print Error Message
    echo "File <strong>{$_FILES['upload']['name']}</strong> Was Not Uploaded - bit of a problem - ask jon<br />";
    //Debug
    $name = $_FILES['upload']['name'];
    $type = $_FILES['upload']['type'];
    $size = $_FILES['upload']['size'];
    $tmp = $_FILES['upload']['name'];

    echo "Name: $name<br />Type: $type<br />Size: $size<br />Tmp: $tmp";

    }

    }
    } else {
    echo 'Ready to upload your file';
    } ?>

    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">

  13. #13
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The only thing I can tell you is that the type reported might be operating system/web server dependent.

    Echo the $_FILES['upload']['type'] to see what it is and/or eliminate the type checking.

  14. #14
    Join Date
    Jul 2006
    Posts
    10
    Thanks - trying that!

Posting Permissions

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