Insert into MySQL with PHP help

janey

I have a large text file of data I need to insert into a mysql database.

I know this probably isn't the best way to do it, but I cannot use phpmyadmin or telnet, so I was wondering...can I use php to add the records?

I know that with phpmyadmin I could create a file like

INSERT INTO table name VALUES
(1, 'data', 'data', 'data' );

and upload it. How can I do that with php?

I tried just adding a php database connection above the insert command and saving the file as php, but that didn't work. As you can see, I'm a newbie, so if anyone has a simple solution, please help. I do plan to learn how to do it the "proper" way, but I am just trying to help a non-profit dog shelter get something up before I go on vacation. Thanks!!!

edwardsbc

Is this data file comma delimited type rows of data? If so look at the php functions file() and explode(). With these you can load a CSV file into an array and then access the individual fields in the rows.

By looping through that array you can generate the SQL you need for inserting the records.

janey

Yes, I think this is a comma delimited file.

Basically, it's:

INSERT INTO table name VALUES
(1, 'data', 'data', 'data' );

for every row in the table.

I think the CSV file stuff may be over my head, but I will look at it. I'm really just looking for the quickest/easiest way to insert this data into the mysql db with no telnet access, and I thought php might work.

edwardsbc

If every row of the file is actually a SQL statement like so:

INSERT INTO table name VALUES (1, 'data', 'data', 'data' );
INSERT INTO table name VALUES (2, 'data', 'data', 'data' );
INSERT INTO table name VALUES (3, 'data', 'data', 'data' );
...

The all you need to do is load the file into an array with file() and then loop over the array executing each line as a query.

$f=$file('filelocation');
while(list($key,$value)=each($f)){
  $result=odbc_exec($dbhandle,$value);
  if(!$result){die("query failed: $value");}
  }

I used an ODBC query, you may have another DB system. Depending on the rows, you may have to "clean" them up some as well (ie remove the semi colon).

If this file is, for example, a mySQL dump file, you could load the file much faster by using the mySQL client.