Initializing a Database Using PHP - Should I use exec()?

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
akreider
Forum Commoner
Posts: 46
Joined: Mon May 22, 2006 3:54 pm
Location: USA

Initializing a Database Using PHP - Should I use exec()?

Post by akreider »

I want to initialize a database using PHP, create 70 tables and populate two of them with data (about 9mb of data) as part of a software installation process.

Note: I'm writing software that is intended for other people to be installing.

What is the best way to do this?

So far, I'm considering using exec() to run mysql and dump the contents of the sql file into the database. And then to ensure security deleting or changing the permissions to the install file once it is done. Is this a good method?

Aaron
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Initializing a Database Using PHP - Should I use exec()?

Post by RobertGonzalez »

akreider wrote:I want to initialize a database using PHP, create 70 tables and populate two of them with data (about 9mb of data) as part of a software installation process.

Note: I'm writing software that is intended for other people to be installing.

What is the best way to do this?

So far, I'm considering using exec() to run mysql and dump the contents of the sql file into the database. And then to ensure security deleting or changing the permissions to the install file once it is done. Is this a good method?

Aaron
Are you going to be including MySQL with your app, or are you going to rely on the user having it? I only ask that because if the app will rely on the user having the database then you can use a simple form submit by the user to grab the database connection details for the purpose of initializing your database connection.

Another thing to consider is that MySQL by default has a max_allowed_packet size setting of 1MB, so unless you provide the MySQL server and provide the changes to the server vars, you will need to also develop a way to break your inserts into smaller chunks to allow the data to be installed properly.
akreider
Forum Commoner
Posts: 46
Joined: Mon May 22, 2006 3:54 pm
Location: USA

Post by akreider »

I'm not including mysql in my app.

My target audience is people with cheap shared hosting plans =)

Do I get around the 1mb max for max_allowed_packet size if I use exec()?
(It worked using exec on my host - took about 30 seconds. I'm not sure what my host's mysql settings are.)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I would say that most shared hosting accounts have a somewhat strict set of parameters on their setups. I know that all of the shared hosts I have ever used have alway used the default 1MB max_allowed_packet setting. There are ways around this, running several staggered quieries for example.

As far as instantiating a database connection, that is something that can be done through a form (similar to how phpBB handles installation) or through a user modified file in your package (like how phpMyAdmin or WordPress) do it. Either way is viable, the latter being the easier of the two for you, the developer (less code to write).
Post Reply