LOAD a .sql FILE

[inspire]

Is there a way using php to load a .sql file and have it execute?

ie. I have a automatically created .sql based off of .txt file.

What I would like to do is after the file has been formatted load it into the database.

Unfortunately I have been unsuccessful doing this so far.

Any help appreciated!

Cheers,

John

[sourcecode]

Hi John,

You can open the file and then insert all data into Mysql directly. The only problem is that you won't be allowed to have multiple lines commands(sure you can have them, if you do some parsing, etc...).

[[deleted]]

You cannot proccess a .sql file using the PHP database functions. You will have to use your database's commandline interface though the exec() or system() functions.

[inspire]

so something like...

exec("mysql -e \"source file.sql" db_name");

I think I get a general idea of what you are saying..

Would you say it would be better to forgo the formatting to .sql and keep the comma delimited text file and load it into the database using LOAD DATA?

If that is the case what would be the best way to go about doing this?

My text file is in this format
A,000037 ,7398,MICROSOFT PRESS SPECIAL ,INTERNET INFORMATION SVCS ,5.0 DOCUMENTATION ,0000000000000049.99,0-7356-0652-8 ,000003.34,0790145065285,0009.25,0007.50,0002.00, ,0000000000000023.94,S,N, ,N,NMS ,BOOK,4035,Y,N,

A,000038 ,7398,MICROSOFT PRESS SPECIAL ,PROGRAMMING ADO , ,0000000000000049.99,0-7356-0764-8 ,000001.83,0790145076489,0009.25,0007.50,0001.25, ,0000000000000023.94,S,N, ,N,NMS ,BOOK,4045,Y,N,

---

would something like

$load_file = "LOAD DATA INFILE 'file.txt' INTO TABLE table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";

work for what I am trying to do?

Thanks for input guys.. really appreciate it!

Cheers,

John

[[deleted]]

Something like that would work.
LOAD DATGA INFILE is bound to be faster than running the .SQL file (fewer queries to run)

[inspire]

Well I did get it working, I did figure out however that either way I had to format the txt file in order to load it into the database. So couldnt really cut any corners.

Thanks Vincent and Leonardo for your suggestions!

Cheers,

John

[tasistro]

I guess this should work too in case you can't or shouln't shell with your web server

$sql_cmds=@file($sql_file);
if ($sql_cmds===false)
{
//error
}
else
{
for ($counter=0;$counter<count($sql_cmds);$counter++)
{
if (!_check_for_evil_sql($sql_cmds[$counter])
{
$sql_result=mysql_query($sql_cmds[$counter],$db);
}
}
}

Saludos
Gerardo

[inspire]

would it be wise you think to do this?

while (list ($line_num, $line) = each ($fcontents)) {

$without_space = trim ($line);
$ln = explode ($seperator, $without_space);

$sqlquery = 'INSERT INTO ' . $dbname . ' VALUES (';
if ($ln[0]) { $sqlquery .= "'" . $ln[0] . "'"; }

for($i = 1; $i < count($ln); $i++){
$sqlquery .= ",'" . $ln[$i] . "'";
}
$sqlquery .= ');';
$sqlquery .= "\n";

$result = mysql_query($sqlquery, $db);

havnt tried it.. not sure if it would speed things up at all.

thanks for help,

john

[tasistro]

I am asuming the sql file contains data in the following form

INSERT INTO clavesconcepto VALUES ( '1', '1', 'Sueldos captura', '', '1', '1', '2001-09-19 18:54:59');
INSERT INTO clavesconcepto VALUES ( '2', '1', 'Sueldos evento', '', '1', '1', '2001-09-19 18:55:33');
INSERT INTO clavesconcepto VALUES ( '3', '2', 'Larga distancia nacional', '', '1', '1', '2001-09-19 18:59:12');
INSERT INTO clavesconcepto VALUES ( '4', '2', 'Servicio medido', '', '1', '1', '2001-09-19 18:59:31');
INSERT INTO clavesconcepto VALUES ( '5', '4', 'Compra', '', '1', '1', '0000-00-00 00:00:00');

So all you need to do pump each row as a sql and you're done. Obviously if the data is separated by comas or something like it you'll need to use your approach. Let me look at it a little further and I'll get back to you.

Saludos
Gerardo