Writing A Script To Create Database

[solspace]

Not sure if anyone has done anything like this.

 

But from some computers, I can't access phpmyadmin.

So, for those times when I can't, I wanted a way to add databases or tables to existing databases.

 

I have a script that I am using. It displays all my databases and the tables with no problem.

 

But, when I try to add a database, i get an error that says

 

Error creating database: Access denied for user: 'user@localhost' to database 'database'

 

 

 

My question is what is the username that is used for that type of access? Isn't it the same username that you log into phpmyadmin with and that password?

 

I used that username and password and it didn't work.

 

On the phpmyadmin, when you create a new database, what username is it using?

 

Does that make any sense what I am asking?

 

This is the part of my script that is supposed to create the database

 

if (mysql_query("CREATE DATABASE $db")) {

echo "Database created successfully\n";

} else {

echo 'Error creating database: ' . mysql_error() . "\n";

}

 

And before that, my connection information looks like this.

 

$user= username

$conn = mysql_connect( "localhost", $user, "password" )

or die ("Sorry - coul dnot connect to MySQL");

 

I used the username that I log into phpmyadmin with and my cpanel.

 

Thanks in advance for any help.

 

Oh, and just incase any staff sees this and cringes at the possibilities, let me say this.. The script will be just used for me. It is in a directory I have password protected and accessible just by me.

 

It won't be in a place left wide open for anyone to access and start wracking havoc on the server.

 

I'd hate to have the staff upset at me because of possibilities of abuse if I didn't take precautions.

[TCH-Don]

I wonder if for the script

the user should be cpanelname_databasename ?

[solspace]

I wonder if for the script

the user should be cpanelname_databasename ?

I tried that as well. Before the mysql_query(Create Database) line

 

I used $db = "cpanelname_".$db

 

so that way the variable db is equal to cpanelname_database

 

Its driving me nuts because it should work.

 

the same connection string allows me to list all the databases and tables I have already created.

 

Its almost like a permissions problem or something not allowing me to create a new database.

 

Okay.. and now I just confused myself further.

 

I used the following code, and it worked with no problem.

 

$db = "cpanelname_". $db;

if (mysql_query("DROP DATABASE $db")) {

echo "Database deleted successfully\n";

} else {

echo 'Error deleting database: ' . mysql_error() . "\n";

}

 

Same connection string and everything.

 

It deleted my database.

 

So, it can't be premissions because if it was and it wasn't allowing me to create a database, I'm sure that it wouldn't allow me to delete one either.

 

*goes back to look at code*

 

And to add to further confusion, I didn;t change anyhting for my add part of the script and I just tried it again, and it worked.

 

Color me completly confused.

 

Spoke too soon... the way it works is this.

 

I can create the database in the sql manager from cpanel.

 

I can delete it using my script.

I can then add it back using my script.

 

But if I try to add a database that never existed, I get the same error message as in my first post.

 

So, still confused.

[TCH-Don]

Sometimes you have to hit it

[solspace]

Spoke too soon... the way it works is this.

 

I can create the database in the sql manager from cpanel.

 

I can delete it using my script.

I can then add it back using my script.

 

But if I try to add a database that never existed, I get the same error message as in my first post.

 

So, still confused.

[TCH-Dick]

Permissions is not the problem. I can create a db using the following script.

><?php
mysql_connect("localhost","cpnlname_dbuser","dbpass");
mysql_query("CREATE DATABASE cpnlname_dbname") or die (mysql_error());
echo "Your database should be created now.";
?>

Can we see the rest of your code?

[borfast]

Humm... that's strange. As far as I know, you shouldn't be able to create a database with your regular user unless you go through MySQL manager. Not even delete it. Unless something has changed on CPanel during the last few updates...

[solspace]

Permissions is not the problem. I can create a db using the following script.

><?php
mysql_connect("localhost","cpnlname_dbuser","dbpass");
mysql_query("CREATE DATABASE cpnlname_dbname") or die (mysql_error());
echo "Your database should be created now.";
?>

Can we see the rest of your code?

 

I tried just your example in a file, substituting proper values for the variables and got the same message. My entire php file was just the above code you posted. It worked if I used the name of a database that existed at one point. But if I tried a brand new database name, error message.

 

 

Here is the function I have to create a database. It does work and create a database, if the database at one time existed but was deleted. It will not create a brand new database though.

 

Connection part of page

 

I used variables for my connection string because they are in another file.

$host = localhost

$user = cpanel_username

$password = cpanel_username_password

 

>include('connection_info');
$conn = mysql_connect( $host, $user, $password )
or die ("Sorry - could not connect to MySQL");

 

>function create_database($db)
{
$db = "cpanelname_". $db;
if (mysql_query("CREATE DATABASE $db")) {
  echo "Database created successfully\n";
} else {
  echo 'Error creating database: ' . mysql_error() . "\n";
}

return $db;

}

 

And here is where the function is called.

 

>Case 'add':
    print '<form action="' . $PHP_SELF . '?choice=add_db" method="post">';
    print 'Current databases: '; echo($db_list);  
    print ' <hr>';
    print '<br>';
    print 'Name:<input type="text" name="db">';
    print '<input type="submit" value="Create Database">';
    print '</form>';
    break;
  Case 'add_db':
    create_database($db);
    break;  

 

The page that I have created uses links to do things like list databases, list the tables in each database, create a database, delete a database, and create a table in an existing database.

 

When a link is clicked, the link uses $PHP_SELF and adds in a variable to be used in the case statement.

So, for the add database link, it uses $PHP_SELF?choice=add

Then goes into the add case and creates a form to get the name of the database to create(variable is db) and submits it using $PHP_SELF?choice=add_db

to fall into the next case where the create_database($db) function is called.

 

If this post doesn't help, I can paste in my entire page and move it to a location that isn't password protected, for the moment, so that it can be viewed.

 

Thanks so far for all the replies

[solspace]

Permissions is not the problem. I can create a db using the following script.

><?php
mysql_connect("localhost","cpnlname_dbuser","dbpass");
mysql_query("CREATE DATABASE cpnlname_dbname") or die (mysql_error());
echo "Your database should be created now.";
?>

Can we see the rest of your code?

I tried using the username cpanelname_cpanelname and the password I log in with and that gave an error because the mysql user with that name didn't exist.

I created it and gave it all permissions, but when I tried to list all my databases, none showed up.

 

So, I used the name cpanelname and that lists all my databases.

 

I even tried other users that I have setup as mysql usernames, none of them would let me create a brand new database.

 

I'm still not sure why it won't work for me. Could it really be a permissions problem? But then again, why would I be able to create a database that existed and is removed now but not a brand new database that never existed.

 

I copied the test file that I am using to the following link so that people here can see what message I am getting.

http://www.solitaryspaces.com/testing/

[kylew]

I know this is an old post, but i am having the same trouble. does anyone have solutions? I need to be able to create databases from a script and I can't seem to get it to work without first creating the db in cpanel.

 

any new solutions?

[borfast]

kylew, you will always have to create the database in cPanel first, because your regular user doesn't have permissions to create databases, only tables inside already existing databases.