execute sql file from php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

execute sql file from php

Post by Pyrite »

What is the SQL syntax to import an sql file through PHP? I am using ADOdb and my user has the FILE privaledge (at least i think that is what is needed).

So do I do like?

Code: Select all

$db->Execute(&quote;FILE '/path/to/file.sql'&quote;);
SBro
Forum Commoner
Posts: 98
Joined: Tue Sep 30, 2003 10:06 pm

Post by SBro »

I would think that you would need to read the contents of the sql file into a string using file_get_contents() and then execute it like you said eg.

Code: Select all

$sql = file_get_contents('path/to/file.sql');

$db->Execute($sql);
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

That works for now thanks. Only thing is, it doesn't if there is more than one SQL query in the file, but in my case, there isn't. I just thought there was a way to read an SQL file from SQL, Like LOAD DATA INFILE, but that is only for like comma delimited files I guess. I'm not sure how phpMyAdmin does it, but I thought it would be easier to ask rather than dig through their code. Thanks.
SBro
Forum Commoner
Posts: 98
Joined: Tue Sep 30, 2003 10:06 pm

Post by SBro »

You can use a LOAD DATA INFILE query if you wanted to, I was under the impression the sql file you wanted to load just contained some query and not data, my mistake:

Code: Select all

$db->Execute("LOAD DATA INFILE "/path/to/file.sql" INTO TABLE tableName");
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

It does contain queries, not data, so no, you were right. I don't think i can use LoAD DATA INFILE with a file that has SQL queries though.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

It might be worth taking a look at how phpMyAdmin do this?

I guess it's somewhere in the readdump.php file ;-)
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Pyrite wrote:Only thing is, it doesn't if there is more than one SQL query in the file, but in my case, there isn't.
This is done on purpose to stop sql injection attacks. You will need to break up the queries in some way and feed them into mysql_execute one at a time.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

the issue is that the INFILE is MySQL specific code... Therefore i think that the code below is a more portable solution: (untested)

Code: Select all

<?php
$fp = fopen('instructions.sql', 'r');
while (!feof($fp)) 
{
  $line = fread($fp, 8192);
  $db->Execute($line);
}
fclose($fp);
?>
Post Reply