Inserting a large array into a DB

[PHP_apprentice]

I have a project where I am reading a comma delimited file into an array and then trying to insert those fields into a DB. I now that i can do a query for each element of the array but i was hoping that someone new of a way to read the whole array in to thge DB with one call.

I appreciate any advice you can give me on this.

[LordShryku]

You can insert multiple rows into the database but seperating them with commas. For instance

$sql = "INSERT INTO table 
           (field1, field2, field3)
        VALUES 
           ('".$value[0]."', '".$value[1]."', '".$value[2]."'),
           ('".$value[3]."', '".$value[4]."', '".$value[5]."'),
           ('".$value[6]."', '".$value[7]."', '".$value[8]."')";

mysql_query($sql);

So you could probably loop through your array or implode to build your query, then use one command to execute it. Though, this does cause trouble when troubleshooting.

[PHP_apprentice]

Thanks that is the way I thought i would have to do it. I was hoping to avoid having to go through all the fileds cause there is 55 of them.

[LordShryku]

55 ain't bad. I do a CSV file daily of around 25000-30000, and it only takes a couple second to read and insert all of the data. I'm sure you could just insert the whole array into one field, but that seems rather pointless.

[PHP_apprentice]

The array is being built from an individual text file which in turn will be a single record in the database. When my warehouse recieves the item and scans it after processing each record is timestamped @ completion. This can range from 5000 to 40000 a day. I may just have to keep looking for a way to make it better and more efficient. I guess that is what keeps us going in the end though.

[Drakla]

MySQL - if that's what you're using has this:

6.4.9 LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.)

[PHP_apprentice]

Thanks I will give that a shot!!! It sounds like the perfect solution.

[sneakyimp]

ok this thread is a year old...

if you don't specify LOCAL, where does mysql look for the file?

if you do specify local, where does mySQL look for the file?

EDIT:
Also, is there any way to exclude columns?

I will be doing this from the command line (or might have to write a php script).

I have some text files as large as 15 Megabytes and phpMyAdmin can only handle up to 2 Megs.

[sneakyimp]

ok....been rtfm....found this:

When locating files on the server host, the server uses the following rules:

If an absolute pathname is given, the server uses the pathname as is.
If a relative pathname with one or more leading components is given, the server searches for the file relative to the server's data directory.
If a filename with no leading components is given, the server looks for the file in the database directory of the default database.
Note that these rules mean that a file named as ./myfile.txt' is read from the server's data directory, whereas the same file named asmyfile.txt' is read from the database directory of the default database. For example, the following LOAD DATA statement reads the file `data.txt' from the database directory for db1 because db1 is the current database, even though the statement explicitly loads the file into a table in the db2 database:

mysql> USE db1;
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Where is the server's data directory? how do i find that?

also where is the database directory?

[PHP_apprentice]

usually the data directory for MySQL is located at

/usr/local/mysql/data

[tsinka]

On some recent Linux distributions the directory is /var/lib/mysql
and the database directories are
/var/lib/mysql/dbname

Thomas

[sneakyimp]

you guys rock. thx!

[sneakyimp]

another question:

do i need to define the table and all of its fields before i do the load command? it would be nice if i didn't have to but i'm guessing i probably will.