Create and import mysql database on shared host in php

Question

I'm trying to write php script that would:

• connect to mysql
• create database
• create user and password
• add user to database
• import prepared sql file to database

The thing is that it'll be on shared host (and I'm trying to make it universal and work with different hosts). I guess database and db user will be prefixed with my account name (what I use for ftp, to login to control panel, etc) on shared hosts? Something like mylogin*_*dbname and mylogin*_*dbuser. It's visible for example in cpanel - when I add database I enter its name and after it's created cpanel shows it as mylogin*_*somedb. How do I make my script work with this on multiple different shared hosts - add my prefix automatically depending on my main login?

Now working with such code (don't have a clue if it works, that's just what came to my mind):

<?php
mysql_connect("host", "user", "password"); // Connection to MySQL

$query = "CREATE DATABASE somedb;
USE somedb;
SOURCE path/to/sqlfile.sql;
CREATE USER someuser IDENTIFIED BY PASSWORD 'somepass';
GRANT SELECT,INSERT,UPDATE,DELETE ON somedb.* TO 'someuser'@'host';";

$arr= explode( ';', $query );
foreach( $arr as $command )
{
mysql_query( $command );
}  
mysql_close(); // Disconnection from MySQL
?>

Answer 1

The fact that this is on a shared host shouldn't make that much of a difference in my opinion. Whatever your environment, you'll have to use your username and either the database name gets prefixed with that or it doesn't according to hosting setup. That said it is probably smart to adopt a naming convention with your username as a prefix or some other prefix naming convention.

As to what you're trying to do I have some suggestions:

1. You should run each statement separately (as you do) but add error checking between each step so you don't attempt to source the file if the database wasn't created for instance.
2. Maybe create separate functions for each kind of operation and send in parameters for the values. This would clean up the code in the longer run.
3. If you have a situation where something is automatically prefixed to the name you provide and you have to detect the final name, you can probably do a "show databases;" query and search for the name you gave in the results. Then use the complete name for the resulting queries.

Answer 2

Use mysql_error() http://us3.php.net/manual/en/function.mysql-error.php

So you can see what went wrong.

eg:

if (mysql_query( $command ) === false) {
  echo mysql_error();
  die;
}

The same for mysql_connect()

if (($link = mysql_connect("host", "user", "password")) === false) {
  echo mysql_error();
  die;
}

You have to make sure the user you are using has privileges to create databases.

For CPanel I believe you have to use your account username and pass. Then like you said prefix your db name with your username, or it will fail to create.

Answer 3

KISS principle: just use phpMyAdmin? It's almost certainly installed. If it's not, install it.

Its import capability is magnificent. If your database is by any chance too big, gzip it. If it's still to big, try splitting it up in a few pieces. I doubt you need to transfer it as a single big transaction. Do you?

---

After the explanation in first comment, well, here goes. This is my very simplistic script which does what you want. Except it doesn't take a look at the separators: one query == one line.

<link rel="stylesheet" href="style/contents.css"/>
<?

function timesanitize($v) {
    if ($v > 0)
        return round($v, 4);
    else
        return 0;
}

$startmt = microtime();
include_once 'include/db.php';
$f = fopen("db.sql","r");
echo dbGetEngine() . "<br>";
echo "<ul>";
do {
    $l = rtrim(fgets($f));
    if (strlen($l) == 0)
        continue;
    if (substr($l, 0, 1) == '#')
        continue;
    $l = str_replace(
        array("\\n"),
        array("\n"),
        $l);
    if (dbGetEngine() == "pgsql")
        $l = str_replace(
            array("IF NOT EXISTS", "LONGBLOB"),
            array("", "TEXT"),
             $l);
    try {
        echo "<li>".nl2br(htmlspecialchars($l));
        $mt = microtime();
        $db->query($l);
        echo "<ul><li>ok - " . timesanitize(microtime() - $mt) . "</ul>";
    } catch (PDOException $e) {
        echo "<ul><li>".$e->getMessage() . "</ul>";
    }
} while (!feof($f));
fclose($f);

echo 'total: ' . timesanitize(microtime() - $startmt);
?>

It also outputs a small statistic of how long each query took. It's based around PDO; I believe PDO was introduced in PHP5.1 or PHP5.2. I think it should be trivial to modify it to work directly with mysql_*() functions, if for some reason you prefer that.

And once again: yes, I know it sucks. But as long as it Works For Me (tm), and possibly You... :-)

---

To complete the code, here are include/db.php and a sample include/config.php:

include/db.php:

<?
include_once 'include/config.php';

try {

        $attribs =  
                array(
                        PDO::ATTR_PERSISTENT => $config['db']['persistent'],
                        PDO::ATTR_ERRMODE => $config['db']['errormode']
                );


        $db = new PDO(
                $config['db']['uri'],
                $config['db']['user'],
                $config['db']['pass'],
                $attribs
        );
        $db->query("SET NAMES 'utf8'");
        $db->query("SET CHARACTER SET 'utf8'");

} catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
}

function dbGetEngine() {
        global $config;
        return substr($config['db']['uri'], 0, strpos($config['db']['uri'], ':'));
}
?>

include/config.php:

<?

//$config['db']['uri'] = 'sqlite:' . realpath('.') . '/site.db'; // PDO's database access URI
$config['db']['uri'] = 'mysql:host=localhost;dbname=sitedb'; // server should be : 195.78.32.7
//$config['db']['uri'] = 'pgsql:host=localhost;dbname=sitedb';
$config['db']['user'] = 'user_goes_here'; // database username
$config['db']['pass'] = 'pass_goes_here'; // database password
$config['db']['persistent'] = false; // should the connection be persistent
$config['db']['errormode'] = PDO::ERRMODE_EXCEPTION; // PDO's error mode

?>

Included are sample connection strings for SQLite, MySQL and PostgreSQL.