Loading SQL dump through PHP form

[Anon]

I'm after a way to load a mySQL database through a PHP/HTML form, uploading an SQL dump file then running it through a script to execute all the SQL commands in the file.

I know phpmyadmin can do it, but this needs to be a standalone solution for client's uploads to a single table.

The phpmyadmin code is way, way too complicated to follow, and I can't seem to extract the section that deals with this.

I've checked the manuals for LOAD FILE, but AFAIK that only deals with CSV files, and they aren't an option in this particular case. (Client's demands - go figure...)

Any pointers?

[[deleted]]

LOAD FILE can be used on any formatted plaintext file.
CSV is just one of those formats.

Check out the manual about the "handling file uploads"
and the exec() and system() functions.

[Anon]

Thanks for the response, but I believe you misunderstood. I've checked the manual already; when I said CSV I meant plain text files. The file format in question is a SQL dump file:

---

#

Structure

#

DROP TABLE IF EXISTS demo;
CREATE TABLE demo (
id tinyint(2) unsigned zerofill NOT NULL auto_increment,
name char(50) NOT NULL,
logo_url char(50) NOT NULL,
page_colour char(6) DEFAULT 'FFFFFF' NOT NULL,
last_order datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (id),
KEY id (id),
UNIQUE id_2 (id)
);

#

Data

#

INSERT INTO demo (id, name, logo_url, page_colour, last_order) VALUES ( '01', 'Company', 'company.gif', '99CCFF', '2001-04-06 13:21:31');

---

LOAD FILE won't handle that, as it's not plaintext; it's actually a series of SQL statements.

I know mysql itself will handle this from the command line, but I've got to do it through a form.

I've checked manual, mailing list archives, script stores, web serach engines, and can find nothing that will allow me to load an SQL file in without going through the command line except phpmyadmin, and that is not suitable in this case.

The exec and system commands look like they might be useful here, but I'm a bit in the dark as how this works with permissions; surely the "web server user" won't be able to do this?

Any chance of some examples as to how these might help?

I find it hard to believe I'm the only person who wants to do this without CSV or plaintext files. (I agree CSV files would be easy, but client's will be client's...)

Thanks again for the quick response; the exec function at least gives me new options to explore.

[[deleted]]

Check up on "SUDO", that lets you runs programs under a different user ID.
You can yse the file-uploading feature to get the file to your server, and then use EXEC() and SUDO to run mysql with the commands.

mysql database_name < uploaded_file.txt

[Anon]

Ah, light at the end of the tunnel.

Thanks for the pointers; that should be sufficient to get it working.

Back to the codeface...