Create/Configure MYSQL database from php

I am writing some software that I may want to distribute and am not clear how I might include the setup of a mysql database as part of the install script. Is there some way to write a php script that will create a database and set up the tables in that database.

I have installed software myself that does this (like Wordpress software) but have never been able to figure out from the code how this is done.

Can anyone give me some help or pointers for doing this or pointers for where I can look to learn how to do this?

Thanks,

–Kenoli

I’m not sure you can create a database from PHP - it will depend on your hosting package but you can create tables and populate them with CREATE TABLE and INSERT.

1 Like

You’ll have to create an installation script, which first checks the connectivity and permissions of the database user (the credentials) given by the installing person (your customer). If the database user has the proper permissions, the installation script should then create the necessary schema and add any data needed for the application to work.

Scott

Doesn’t WordPress require that you create the empty Database first and then you provide it the Connection credentials and it fills it with tables & data? Other CMSs I have used work this way as well.

Russ

The same things occurred to me, though I think I did install Wordpress on my web server without setting up a database. In retrospect, I’ll bet the web server set up the software on their end so Wordpress had permissions to create the database.

Otherwise, I guess I can use PDO or a similar PHP mechanism for creating and populating tables.

The issue for me here is that I am writing some php software to generate online gallery web pages for artists and I’m sure most artists have no idea how to set up a database. I suppose I could set up a web interface for them but that means hosting their sites which I have no interest or capacity to do. I’m just a guy writing php scripts, not a company with resources or an IT entrepreneur with investors.

I’ve generated some templates and scripts to drive an online gallery and know a lot of artists need this and thought I would try to create an installation package that would make it easy to use.

The script lets an artist customize a template in various ways and then initiate the site by uploading images of artwork along with descriptions and the like. They can group the art into “galleries” and display it on the page in various ways and set styling parameters. It’s pretty rudimentary but a project to stretch my coding skills a bit, one of the challenges being how to make it as self installing as possible.

Thanks for your replies. I think, more than the coding, the real challenge is permissions and how various servers deal with permissions and databases.

–Kenoli

Usually, a web space will offer some sort of interface to create one or more MYSQL databases along with the user for that database, which is also fairly simple and straightforward to use (usually). Running a web site is naturally something that needs to be learned too. :slight_smile:

The user would then enter these database user credentials (possibly along with the database name, but isn’t always necessary) into your script and your script should be checking if it can do the operations it needs to install the application, before it does it. Like creating a temp database and a temp table and dropping them. If all that works, your installation script can continue. That is what I meant with checking permissions.

Scott

Thanks, Scott. I think I need to do some research into web servers. There are a couple of front ends that a lot of them are using and there might be some standard interface.

I appreciate your admonition about checking to see if my functionality works before going on with an install script.

The script I am writing has an admin interface so the user doesn’t have to know anything about databases once it is set up. I have some cool jquery functionality that with ajax lets an artist reorder images in a gallery or move between galleries simply by dragging them where they want them.

Functionality is mushrooming, sometimes almost too much. On the one hand, I love ajax, on the other, I hate the way one thing after another pops up when I drag my mouse around a site. Too much of a good thing has always been too much of a good thing.

Thanks all,

–Kenoli

1 Like

This may do what you want,
http://stackoverflow.com/questions/2583707/can-i-create-a-database-using-pdo-in-php

You will need to log in with a username and password but is doable.

You certainly can. :smile: I’ve been creating databases with PHPMyAdmin for years.:slight_smile: It all depends on the database user you have at your disposal and the permissions that user is given.

Scott

From PHPMyAdmins, yes, but that’s not the same thing as creating a MySQL database from within a PHP script.

Why isn’t it the same? Whether you as a user enter the necessary data in a form and click on the proper command or PHP does it itself with given data, say from an XML file, manipulating all parts of a database will work the same both ways for sure.

Scott

Or are you talking about creating a database server?

Scott

You’re right. I guess if PHPMyAdmin can do it, then you can write a script to create a database.

1 Like

I’ve installed a lot of CMS and forum apps over the years, Not all that many, but more than a few.

They created and populated tables, but I can’t recall even one that I didn’t need to set up the database first.

On the other hand, both phpMyAdmin and MySQL Workbench can create databases, (though I often find it easier to use the CLI)

I think the key factor here is that the app needs to be able to run MySQL as Admin - as in OS security restrictions.

Yes, php can create a database, script included below. I think the challenge here is going to be with the web server and how they configure their MYSQL server and establish privileges.

The user may need to do something to get a mysql server running, which a web service might not have set up out of the box. Once that is done, php should be able to create a database if it has the right privileges. If the user has started the mysql server, they will have a login and password and should be able to enter it into a script like the one below and allow php to create the database and set up the tables. If a person is using something like Godaddy, they will probably have to pay Godaddy something just to set up the server. That service seems to charge for everything. Other servers are probably going to have some security restrictions. Who wants a random script to run a php program and set up their own database.

Thanks to all for taking time to respond.

–Kenoli

here’s the script:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Hmm. I just tried that exact code on localhost and got

Connection failed: Access denied for user ‘username’@‘localhost’ (using password: YES)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.