Professional Web Applications Themes

help : LOAD DATA LOCAL INFILE - PHP Development

I'm trying to load a local file in to a database and have had no luck. I have downloaded the table contents (1row) using phpAdmin and changed the id# and one other field before trying to upload. To test out the script. That file looks like this (dbdata.txt) "2","public","company2","contact","street","suite" ,"city","province","postcode","countr y","phone","fax","region","service","first name","surname","create date" The script i'm trying to use looks like this. $loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"'"; $result = mysql_query($loadfile) or die (" Invalid DATA LOAD query"); Both files are in the same directory and I am able ...

  1. #1

    Default help : LOAD DATA LOCAL INFILE


    I'm trying to load a local file in to a database and have had no luck.


    I have downloaded the table contents (1row) using phpAdmin and changed the id# and one
    other field before trying to upload. To test out the script.

    That file looks like this (dbdata.txt)

    "2","public","company2","contact","street","suite" ,"city","province","postcode","countr
    y","phone","fax","region","service","first name","surname","create date"


    The script i'm trying to use looks like this.

    $loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS TERMINATED
    BY ',' ENCLOSED BY '\"'";
    $result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");

    Both files are in the same directory and I am able to connect to the table and query it
    within the rest of the script.

    Any help you can give on this is appreciated.

    Thanks,




    Dave Guest

  2. #2

    Default Re: help : LOAD DATA LOCAL INFILE

    Dave wrote:
    > I'm trying to load a local file in to a database and have had no luck.

    Try asking the MySQL server first - it should help point you in the right
    direction.

    $result = mysql_query($loadfile)
    or die ("Error ".mysql_errno() ." : ".mysql_error() ."");


    > I have downloaded the table contents (1row) using phpAdmin and
    > changed the id# and one other field before trying to upload. To test
    > out the script.
    >
    > That file looks like this (dbdata.txt)
    >
    >
    "2","public","company2","contact","street","suite" ,"city","province","postco
    de","countr
    > y","phone","fax","region","service","first name","surname","create
    > date"
    >
    >
    > The script i'm trying to use looks like this.
    >
    > $loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename
    > FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
    > $result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");

    This is what phpMyAdmin generated for a similar csv file:

    $loadfile=
    'LOAD DATA LOCAL INFILE
    \'/path-to-your-server/dbdata.csv\' INTO TABLE
    `tablename`
    FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\'
    ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';

    // cue the result or tell us why not..
    $result = mysql_query($loadfile)
    or die ("Error ".mysql_errno() ." : ".mysql_error() ."");


    Note the path-to-your-server may be something like
    /home/htdocs/xx/dbdata.txt


    Hope that helps

    --
    Tony


    Tony Guest

  3. #3

    Default Re: help : LOAD DATA LOCAL INFILE

    On Mon, 04 Oct 2004 14:42:57 -0500, [email]mehere.com[/email] (Dave) wrote:
    >I'm trying to load a local file in to a database and have had no luck.
    >
    >
    >I have downloaded the table contents (1row) using phpAdmin and changed the id# and one
    >other field before trying to upload. To test out the script.
    >
    >That file looks like this (dbdata.txt)
    >
    >"2","public","company2","contact","street","suite ","city","province","postcode","countr
    >y","phone","fax","region","service","first name","surname","create date"
    >
    >
    >The script i'm trying to use looks like this.
    >
    >$loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS TERMINATED
    >BY ',' ENCLOSED BY '\"'";
    >$result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");
    >
    >Both files are in the same directory and I am able to connect to the table and query it
    >within the rest of the script.
    You haven't said what problems you're having. Use mysql_error() to get an
    error message, and post this message.

    Actually - put the error message into Google first - you are likely hitting
    the security constraints placed on LOAD DATA LOCAL INFILE by MySQL itself.

    --
    Andy Hassall / <andyandyh.co.uk> / <http://www.andyh.co.uk>
    <http://www.andyhsoftware.co.uk/space> Space: disk usage ysis tool
    Andy Hassall Guest

  4. #4

    Default Re: help : LOAD DATA LOCAL INFILE

    In article <rkk3m05f2m77m86jhfoiphgn21gbvj1bcg4ax.com>, [email]andyandyh.co.uk[/email]
    says...
    >
    >On Mon, 04 Oct 2004 14:42:57 -0500, [email]mehere.com[/email] (Dave) wrote:
    >
    >>I'm trying to load a local file in to a database and have had no luck.
    >>
    >>
    >>I have downloaded the table contents (1row) using phpAdmin and changed the
    id# and one
    >>other field before trying to upload. To test out the script.
    >>
    >>That file looks like this (dbdata.txt)
    >>
    >>"2","public","company2","contact","street","suit e","city","province","postco
    de","countr
    >>y","phone","fax","region","service","first name","surname","create date"
    >>
    >>
    >>The script i'm trying to use looks like this.
    >>
    >>$loadfile = "LOAD DATA LOCAL INFILE 'dbdata.txt' INTO TABLE tablename FIELDS
    TERMINATED
    >>BY ',' ENCLOSED BY '\"'";
    >>$result = mysql_query($loadfile) or die (" Invalid DATA LOAD query");
    >>
    >>Both files are in the same directory and I am able to connect to the table
    and query it
    >>within the rest of the script.
    >
    > You haven't said what problems you're having. Use mysql_error() to get an
    >error message, and post this message.
    >
    > Actually - put the error message into Google first - you are likely hitting
    >the security constraints placed on LOAD DATA LOCAL INFILE by MySQL itself.
    >
    >--
    >Andy Hassall / <andyandyh.co.uk> / <http://www.andyh.co.uk>
    ><http://www.andyhsoftware.co.uk/space> Space: disk usage ysis tool


    I added the script above and the error i'm getting is.

    Error 1148 : The used command is not allowed with this MySQL version

    Thanks again


    Dave Guest

Similar Threads

  1. LOAD DATA INFILE
    By Luca in forum MySQL
    Replies: 4
    Last Post: July 11th, 12:35 PM
  2. LOAD DATA INFILE problem
    By howachen@gmail.com in forum MySQL
    Replies: 3
    Last Post: June 10th, 05:56 PM
  3. Complex LOAD DATA INFILE
    By stefaan.lhermitte@agr.kuleuven.ac.be in forum MySQL
    Replies: 6
    Last Post: May 18th, 06:34 PM
  4. Load Data Infile question
    By bradfordh@gmail.com in forum MySQL
    Replies: 3
    Last Post: March 23rd, 04:53 AM
  5. LOAD DATA LOCAL INFILE and Perl DBI/DBD
    By Jami Bradley in forum PERL Modules
    Replies: 1
    Last Post: June 25th, 03:08 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139