Executing SQL files from PHP (MySQL)

Anon

I am beating my head against the wall because this seems like it should be such a simple thing to do and I know I am missing the obvious.

I am trying to have a system where a table can be recreated on the fly...

The following code works on some systems, but not others (for obvious security reasons):

$v_Command = "mysql -u$g_DBUserName -p$g_DBPassword < $g_SystemPath/sql/$p_FileName";
exec ($v_Command);

So, I have tried the following:

$v_TheSQL = "SOURCE $g_SystemPath/sql/$p_FileName;";
doQuery ($v_TheSQL);

(where "doQuery" is my standard wrapper function for mysql_query and which works fine for all my other mysql queries)

This gives me an SQL error.

I have also tried:
function getDocument ($p_URL) {

	$v_FilePointer = fopen ($p_URL, "r");
	$v_TheDocument = fread ($v_FilePointer, 128000);

	return $v_TheDocument;

}

$v_TheSQL = getDocument ("$g_SystemPath/sql/$p_FileName");
doQuery ($v_TheSQL);

Still no success.

Any suggestions?

Thanks!

[deleted]

Q: why would you weant to do this in the first place?

Anon

Because I'm building a portal toolkit and I store a lot of values such as page color, text color, logo etc. in a configuration table. To make it easy for webmasters to get up and running I provide a set of prefab "looks" or color schemes which are simply a SQL files that drop the configuration table, re-build it, then populates it with a whole slew of values.

It's fast and easy, and can be generated from the table after you've tweaked a bunch of settings so that you can save a configuration.

I don't know - it just made sense to me. :-)

[deleted]

It's a method, but a bit 'over the top' I think.

Instead, how about just creating one large table that contains all the prefab-scheme-data, just like in the user's tables, but with one extra field to define which prefab scheme it belongs to.

Then when a user want's to use a scheme, simply do a 'TRUNCATE' or 'DELETE FROM' on his sscheme table, and use something like

INSERT INTO userschemetable SELECT fields FROM prefabschemetable WHERE prefabschemeid=X;

You can do all this from inside PHP without running any shell scripts.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

Well, perhaps a bit over the top, but...

The next step I want to take is to let the webmaster push a button and have the system build/install itself from scratch.

I am trying to avoid resorting to shell execution.

It just seemed to me there ought to be a way to let PHP tell MySQL to load/excecute an SQL file. Otherwise how does one ever bootstrap a system without shell access?

[deleted]

You can do just about every SQL query/command from inside PHP.
If you want to load an SQL file, you'll need an interpreter for that file, and PHP can't do that by default.

When you say 'from scratch', how ib gis that scratch? Are you thinking of installing a webserver on a clean OS, or adding a new server on an existing webserver?

What areth thou plans?

Anon

OK, by scratch I mean you have Apache, PHP, and MySQL all happily running. You upload (or untar) my system, which starts out as basically a generic portal site in a box.

So, along with a whole bunch of HTML and PHP files there are a couple dozen SQL files. Here's a short example of what I mean by an SQL file:

USE Molly;

DROP TABLE IF EXISTS config;
CREATE TABLE config (
configID mediumint(9) NOT NULL auto_increment,
description varchar(255) NOT NULL default '',
setting varchar(255) NOT NULL default '',
value varchar(255) default NULL,
whichPicker varchar(255) default 'color',
PRIMARY KEY (configID)
) TYPE=MyISAM;

Default data for table `config`

INSERT INTO config VALUES (1, 'Window Title', 'g_PageTitle', 'LinuxMolly', '');
INSERT INTO config VALUES (2, 'Company Name', 'g_InstitutionName', 'InterPersonalNet, L.L.C.', '');
INSERT INTO config VALUES (3, 'Abbreviated Company Name', 'g_InstitutionAbbrev', 'IPN', '');

etc. etc.

Now if you have access to the command line it's no big deal to run MySQL and source these files. In fact I have one file that creates the database Molly and sources the rest, so it's really just one command to create the entire database and populate it for the system.

EXCEPT that I can't figure out how to make MySQL do that from within PHP.

Whether I have a good reason or not, it bothers me that I seem to be able to do any other SQL from PHP except this one thing.

And without this one thing it's rather a pain to install a complex web site on a system where the service provider does not give you access to the command line. (Or where the webmaster really does not want to venture into the world of the unix command.)

So... if anyone actually does know how, I'd appreciate a heads up. :-)

[deleted]

"Whether I have a good reason or not, it bothers me that I seem to be able to do any other SQL from PHP except this one thing."

it's a good reason alright, it's just that 'running an sql file' is not post of the SQL functionality.

What it actually does is just send the content of the file to the SQL interpreted, whcih will then recognise the different queries and execute them.

PHP can do everything that the SQL file can, but you have to feed it one query at a time.

The real problem here is not that PHP can't do something, it's the fact that what you want to do is not standard SQL functionality.