create database from php script

[how2]

...is what I'm having trouble with.
Is it that we can ONLY create databases by using cpanel here at bluehost?
I have been trying to do this:

<?php /*** mysql-create_db.php ***/
$user = "site_dbusername";
$userpass = "password";
$newdbname = "test20080716";

$con = mysql_connect("localhost", $user, $userpass)
or die("Connection failure <br/>". mysql_error() );

mysql_query("CREATE DATABASE $newdbname")
or die("creating database fails... <br />". mysql_error() );

mysql_close($con);
?>
I GET THIS ERROR:

creating database fails...
Access denied for user 'site_dbusername'@'localhost' to database 'test20080716'

...of course PHPMyAdmin shows no new databases...

PHPMyAdmin does show a little inactive link thingy which says:

Create new database:
No Privileges

I found this which seems to say it's possible:
http://helpdesk.bluehost.com/kb/index.php?x=&mod_id=2&id=6

To create the Database, you would go to your cpanel

1. Log into the cpanel
2. Click the icon for MySQL
3. Create a user (make it short best result)
4. Create a DB (make it short best result)
5. Most important, Click Add User to DB button (authentication will not work if you do not click this button)

After doing all these steps, you can create DB either phpmyadmin or use an online php script or perl script.
...but I think that may be a misstype ... 'DB' might supposed to be 'tables'...

I also find this interesting:
http://dev.mysql.com/doc/refman/5.0/en/create-database.html

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database.
... which looks like it would be a daemon configuration directive... which might not be set in the cpanel setup, hmm.... that's what I'm thinking...
So I'm wondering "what's the deal!" ...thanks...

[felgall]

You CAN create databases by following those instructions in cPanel. The database creates done through cPanel are done using a different access which does have permission to create databases and where the code is set up to limit your choice of database and user names so that they can't clash with other accounts on the same server. That protection can't be enforced outside of cPanel and so only the special user that the cPanel database create option uses has the ability to create databases and BlueHost have security in place to prevent databases being created any other way.

[Early Out]

...but I think that may be a misstype ... 'DB' might supposed to be 'tables'...And yes, I think you're right about that - I believe that last sentence should read, "After doing all these steps, you can populate the DB either with phpmyadmin or by using an online php script or perl script."

[areidmtm]

I just recently wanted to do the same thing so I wrote this to do exactly that:



include('curl.class.php');
$curl = new Curl();

//cPanel Info
$box_num = 000;
$cPanel_user = 'CPANEL_USERNAME';
$cPanel_pass = 'CPANEL_PASSWORD';

//Database to create
$db = 'NEW_DB';
$db_user = 'DB_USER';
$db_pass = 'DB_PASS';

//Permission must stay in uppercase. This will give the user full permissions. Remove the ones that you don't want.
$permissions = array('SELECT' => 'SELECT', 'INSERT' => 'INSERT', 'UPDATE' => 'UPDATE', 'DELETE' => 'DELETE',
'CREATE' => 'CREATE', 'ALTER' => 'ALTER', 'DROP' => 'DROP', 'LOCKTABLES' => 'LOCK',
'INDEX' => 'INDEX', 'REFERENCES' => 'REFERENCES', 'CREATETEMPORARYTABLES' => 'TEMPORARY',
'CREATEROUTINE' => 'CREATEROUTINE');


//Do It
$host = 'http://' . $cPanel_user . ':' . $cPanel_pass . '@box' . $box_num . '.bluehost.com';
$url = $host . ':2082/frontend/bluehost/sql/addb.html';
$data = array('db' => $db);
$results = $curl->post($url,$data);
if (empty($results['error'])) {
echo 'Database created<br/>';
$url = $host . ':2082/frontend/bluehost/sql/adduser.html';
$data = array('user' => $db_user, 'pass' => $db_pass, 'pass2' => $db_pass);
$results = $curl->post($url,$data);
if (empty($results['error'])) {
echo 'User created<br/>';
$url = $host . ':2082/frontend/bluehost/sql/addusertodb.html';
$data_user = array('user' => $cPanel_user . '_' . $db_user,'db' => $cPanel_user . '_' . $db);
$data = array_merge($data_user,$permissions);
$results = $curl->post($url,$data);
if (empty($results['error'])) {
echo 'User added to database';
}
else {
die('There was and error adding the user to the database');
}
}
else {
die('There was and error creating the user');
}
}
else {
die('There was and error creating the database');
}


You will also need this Curl class:

class Curl {
public $m_caseless;
public $m_handle;
public $m_header;
public $m_options;
public $m_status;
public $m_followed;

public function __construct() {}

public function setCurl($theURL=null) {
if (!function_exists('curl_init')) {
trigger_error('PHP was not built with --with-curl, rebuild PHP to use the curl class.', E_USER_ERROR);
}

$this->m_handle = curl_init();

$this->m_caseless = null;
$this->m_header = null;
$this->m_options = null;
$this->m_status = null;
$this->m_followed = null;

if (!empty($theURL)) {
$this->setopt(CURLOPT_URL, $theURL);
}

$this->setopt(CURLOPT_HEADER, false);
$this->setopt(CURLOPT_RETURNTRANSFER, true);
}

public function post($url,$post) {
$this->setCurl($url);

$this->setopt(CURLOPT_FOLLOWLOCATION, true);
$this->setopt(CURLOPT_POST, true);
$this->setopt(CURLOPT_POSTFIELDS, $this->asPostString($post));

$results = array();
$results['exec'] = $this->exec();
$results['error'] = $this->hasError();

$this->close();

return $results;
}

function get($url,$get) {
$this->setCurl(urldecode($url . '?' . $this->asPostString($get)));

$this->setopt(CURLOPT_FOLLOWLOCATION, true);
$this->setopt(CURLOPT_HTTPHEADER, array("Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5",
"Accept-Language: ru-ru,ru;q=0.7,en-us;q=0.5,en;q=0.3",
"Accept-Charset: windows-1251,utf-8;q=0.7,*;q=0.7",
"Keep-Alive: 300")
);
$this->setopt(CURLOPT_REFERER, $_SERVER['HTTP_REFERER']);
$this->setopt(CURLOPT_USERAGENT, $_SERVER['HTTP_USER_AGENT']);

$results = array();
$results['exec'] = $this->exec();
$results['error'] = $this->hasError();
$this->close();

return $results;
}

public function close() {
curl_close($this->m_handle);
$this->m_handle = null;
}

public function exec() {
$theReturnValue = curl_exec($this->m_handle);

$this->m_status = curl_getinfo($this->m_handle);
$this->m_status['errno'] = curl_errno($this->m_handle);
$this->m_status['error'] = curl_error($this->m_handle);

$this->m_header = null;

if ($this->m_status['errno']) {
return '';
}

if ($this->getOption(CURLOPT_HEADER)) {

$this->m_followed = array();
$rv = $theReturnValue;

while (count($this->m_followed) <= $this->m_status['redirect_count']) {
$theArray = preg_split("/(\r\n){2,2}/", $rv, 2);
$this->m_followed[] = $theArray[0];
$rv = $theArray[1];
}

$this->parseHeader($theArray[0]);

return $theArray[1];
}
else {
return $theReturnValue;
}
}

public function getHeader($theHeader=null) {
if (empty($this->m_header)) {
return false;
}

if (empty($theHeader)) {
return $this->m_header;
}
else {
$theHeader = strtoupper($theHeader);
if (isset($this->m_caseless[$theHeader])) {
return $this->m_header[$this->m_caseless[$theHeader]];
}
else {
return false;
}
}
}

public function getOption($theOption) {
if (isset($this->m_options[$theOption])) {
return $this->m_options[$theOption];
}

return null;
}

public function hasError() {
if (isset($this->m_status['error'])) {
return (empty($this->m_status['error']) ? false : $this->m_status['error']);
}
else {
return false;
}
}

public function parseHeader($theHeader) {
$this->m_caseless = array();

$theArray = preg_split("/(\r\n)+/", $theHeader);

if (preg_match('/^HTTP/', $theArray[0])) {
$theArray = array_slice($theArray, 1);
}

foreach ($theArray as $theHeaderString) {
$theHeaderStringArray = preg_split("/\s*:\s*/", $theHeaderString, 2);

$theCaselessTag = strtoupper($theHeaderStringArray[0]);

if (!isset($this->m_caseless[$theCaselessTag])) {
$this->m_caseless[$theCaselessTag] = $theHeaderStringArray[0];
}

$this->m_header[$this->m_caseless[$theCaselessTag]][] = $theHeaderStringArray[1];
}
}

public function getStatus($theField=null) {
if (empty($theField)) {
return $this->m_status;
}
else {
if (isset($this->m_status[$theField])) {
return $this->m_status[$theField];
}
else {
return false;
}
}
}

public function setopt($theOption, $theValue) {
curl_setopt($this->m_handle, $theOption, $theValue);
$this->m_options[$theOption] = $theValue;
}

public function &fromPostString(&$thePostString) {
$return = array();
$fields = explode('&', $thePostString);
foreach($fields as $aField) {
$xxx = explode('=', $aField);
$return[$xxx[0]] = urldecode($xxx[1]);
}

return $return;
}

public function &asPostString(&$theData, $theName = NULL) {
$thePostString = '';
$thePrefix = $theName;

if (is_array($theData)) {
foreach ($theData as $theKey => $theValue) {
if ($thePrefix === NULL) {
$thePostString .= '&' . curl::asPostString($theValue, $theKey);
}
else {
$thePostString .= '&' . curl::asPostString($theValue, $thePrefix . '[' . $theKey . ']');
}
}
}
else {
$thePostString .= '&' . urlencode((string)$thePrefix) . '=' . urlencode($theData);
}

$xxx =& substr($thePostString, 1);

return $xxx;
}

public function getFollowedHeaders() {
$theHeaders = array();
if ($this->m_followed) {
foreach ( $this->m_followed as $aHeader) {
$theHeaders[] = explode( "\r\n", $aHeader);
}
return $theHeaders;
}

return $theHeaders;
}
}

[how2]

Excellent. I have a better picture now.
Yeah , cPanel works well enough, but I am just beginning to learn both php and mysql and was just goofing around.
I don't have much experience with OO but that code gives me an excuse to add some of that to the mix now too!
We'll see what happens with that ...hours of fun!
...Thanks guys , Howard++;

[Canada4Gold]

I just use cPanel when I need to create a database ... although doing it using PHP / cURL does seem like a good exercise if you're learning.

https://www.mysoftwareproject.net/MSP/logo.jpg

[felgall]

Since two databases are about as necessary as two universes having the ability to create databases dynamically is somewhat redundant. and only really needed once you exceed the limit for the number of tables and data that your first database can hold (some time after the computers running your database finish filling up the second universe). You would of course then need a third universe to hold the second database.

The whole concept of A database is that ALL the data is stored in one place and people only have access to those parts of the data that they are supposed to be able to see. If you want to duplicate data unnecessarily then using flat files is an easier way to do it than creating a second database.

The only real point in multiple databases on web hosting is so that the tables associated with a given site that could potentially be moved to completely different hosting can be kept completely separate from the completely unrelated tables belonging to a different site.

Of course if you were to install 1,000,000 copies of phpBB into the same database then it might start to get a little difficult to remember which tables belong to which copy of phpBB but if you set up separate users for each that only have access to the tables for that copy then you don't need to remember which is which.

For dynamic database creation to be meaningful you'd need dynamic domain creation to go with it in the same script so that the person creating the database can create the domain they are going to use it with at the same time. If they actually have to purchase the domain from a registrar first and then configure the domain through cpanel then being able to dynamically generate the database for the domain rather than just creating it though cpanel as well is a but pointless.

I am also wondering what you could do that would be actually useful that you would learn how to do by using this pointless example that would not be better learnt by doing something that was actually useful.

[areidmtm]

I am also wondering what you could do that would be actually useful that you would learn how to do by using this pointless example that would not be better learnt by doing something that was actually useful.

Wow, Felgall, I knew you were an advocate to having all data in one table, but jeez! I wonder what you could have been doing instead of writing that pointless reply, something useful maybe? ;)

I use this script because I have a development sandbox at home. I created a script that adds sites to my framework more easily. That way I don't have to manually create each new site. The script also creates the database and common tables. Then the script I posted will then create the same database and tables on the bluehost server, which is my live server. This why I'm not creating things twice.

I like to keep my tables separate from others. Why? Because why would I want to have one login table for all my sites? This is just not organized nor optimal. If I have a small site that requires a login that may only have 5 users on it, the script would have to look through that huge table and though all of the other sites users to find them. Now if you're table is properly indexed, you shouldn't have a problem, Now whereas bluehost is a shared server and we have limited CPU, I don't want to put unnecessary strain on the box.

Or I could have multiple login tables. But still, that would be unorganized. Lets say I have 100 sites that all require logins. I don't want to have 100 login tables and all other accompanying tables in the same space. That just gets hard to manage. Also, sometimes users may only have access to certain parts. I don't have to give them access to a huge database that has other information that they don't need. And since in cPanel, you can't specify what users have access to what tables, it now becomes a security concern.

It doesn't hurt the server, (CPU wise) to have multiple databases. If you like being disorganized, then keep all you data and tables in one place. Good luck trying to move that data elsewhere someday and trying to manage all of that.

If you want things to be more organized and a lot more flexible for the future, then I say it's ok to have multiple databases or multiple database for each website.

But hey, that's just me. Now what could I have been doing that wasn't pointless....

[Early Out]

Mod fight! Mod fight!!! :D

[felgall]

Wow, Felgall, I knew you were an advocate to having all data in one table, but jeez! I wonder what you could have been doing instead of writing that pointless reply, something useful maybe? ;)


I never said put everything in one table - in fact the example I mentioned would have more tables than anyone would be likely to ever need.

People keep confusing tables (of which you probably will have quite a few) with databases (where generally you need only one).

Tables in a database can either be separated using a different prefix for each application or site or the better way would be to have the same table name but with different database logins associated with each so that each different database login has its own set of tables it can access.

I am quite regularly changing the table structure on my databases (on one of my test servers) and also copying the data between servers as well but just set up the database once at the start on each server. I do actually have 4 databases set up because I have a couple of sites on my account that I am looking after for someone else and so if their site needs to be moved their data needs to go with it without affecting my data and the separate database means I don't need to pull tables out of a database for the move to be able to take place.

The one situation where I can see that you would need to be creating new databases on a regular basis is if you are developing new web sites for clients and you need to create a new database for each client so as to keep their data separate from all your other clients. A script such as yours would then be useful for implementing their database onto their hosting where ever that might be after you finish testing on your own server. Unless you are doing huge numbers of rollouts of sites for people it probably wouldn't take significantly longer to just log into their control panel and create the database that way though - you still need all the same access details to be able to create a database through the script as through the control panel and so the few seconds it would save you on each implementation means that you could have rolled out dozens of databases in the time taken to write a short reply in the forum here and will need to roll out millions before you recover the time it took to write the script.

The PURPOSE of a database is to organise ALL of your data in the one place. That's what databases were invented to do in the first place.

[areidmtm]

The one situation where I can see that you would need to be creating new databases on a regular basis is if you are developing new web sites for clients and you need to create a new database for each client so as to keep their data separate from all your other clients. A script such as yours would then be useful for implementing their database onto their hosting where ever that might be after you finish testing on your own server. Unless you are doing huge numbers of rollouts of sites for people it probably wouldn't take significantly longer to just log into their control panel and create the database that way though - you still need all the same access details to be able to create a database through the script as through the control panel and so the few seconds it would save you on each implementation means that you could have rolled out dozens of databases in the time taken to write a short reply in the forum here and will need to roll out millions before you recover the time it took to write the script.

This is exactly what I was taking about. And yes it does save me time, lots of it. I can create the needed databases and tables and populate them on both servers with ONE click of a button. Having to manually login to cPanel to create every database takes alot longwe then it does to click a button. I only have to enter in the login credentials ONCE, not every time I want to create a database. All of my clients are hosted on my accounts with bluehost and I have the script automatically determine what box they need to be setup on. Again, one click of a button and it's done instead of several clicks and data entry.

Of course that script save me time. I don't have to manually create the DB in two places. It only took me about 5 minuets to write that script, so yes Felgall, it save me oodles of time the in the long run.

[felgall]

Almost all of the really BIG companies I have worked for have only ever had ONE database - and some were big enough to talk about their disk storage in terms of the many acres of land that the drives occupied.

The one database often held hundreds of tables with the same table name - one table for production, one for quality assurance testing, one for system testing, several for sub-system testing and many for unit testing. Which version of the table you could see when running an application would depend on which group the username you used to log into the database belongs to. That way the same program can run against the same database for unit testing, sub-system testing, system testing, quality assurance testing and then in the live environment with only the user name that you used to log in to the system changing between the different environments and with the program and the database and table names that it is referencing remaining the same.

None of those companies were running systems for other companies at the time I was there but presumably since those companies generally only had one or two mainframe computers that were being used as servers to run everything if they were to do work for another company they would have just defined new user groups so as to put all of the additional data into the same database as everything else (since separate user groups gives all the benefits of a separate database without the overhead of actually running multiple databases).

Things are a bit different when you have an environment running 1000 small servers that have only a fraction of the capacity between them that the one or two servers used by one of the big companies has. With a web hosting environment it does make sense to split the data into separate 'databases' by which of your clients the data belongs to. The way that mySQL is normally run there is only really one true database per server in any case and what the tools call a 'database' is really only a named collection of data within that database. To really run multiple databases you would need to have multiple instances of the mySQL service running at the same time.

Creating a 'database' would still be something that would need to be done only once per client.

[askervco]

I have been trying to use the code suggested by areidmtm to create a bluehost db from php code. I assume that I should modify the code for the following values:
//cPanel Info
$box_num = 000;
$cPanel_user = 'CPANEL_USERNAME';
$cPanel_pass = 'CPANEL_PASSWORD';

//Database to create
$db = 'NEW_DB';
$db_user = 'DB_USER';
$db_pass = 'DB_PASS';

If so, what do I use for $cPanel_user. It seems that this should be my domain name but then that doesn't work. The statement
$host = 'http://' . $cPanel_user . ':' . $cPanel_pass . '@box' . $box_num . '.bluehost.com';
seems to want this plus some more.
Can someone please help me.
Thanks

[housey]

Hi,

I'm trying to to do this and tried using your script areidmtm, I changed the variables to suit and its says its created the database and added a user when it runs but when I look in CPanel or PHPMyAdmin there is no db or user.

Should this still work?

[kellid]

does this only work on blue host
how ca i customize for a different host

[redsox9]

You should check with your hosting company to get this answer.