Jump to content


Photo

PHP script to create a database


  • Please log in to reply
2 replies to this topic

#1 satal keto

satal keto
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 01 January 2006 - 10:09 PM

Hi, I'm doing a project at the moment which requires the use of being able to create a database automatically when ever needed.
So obviously creating a PHP script to get all the needed information and then create the database would be perfect.

Here's the code I'm using

<?php
/////////////////////////
// MySQL Login details //
/////////////////////////
$host = "localhost";
$user = ""; 
$password = ""; 
////////////////////////////////
// End of MySQL Login details //
////////////////////////////////

////////////////////////
// Defining $database //
////////////////////////
$database = $_COOKIE['fusion_user'];
$bad_details = array(".");
$good_details = array("");
$database = str_replace($bad_details, $good_details, $database);
$database = $database{0};
$database = "database_$database";
// Used to test if database correct
echo $database. "<p>";
// End of used to test if database correct
///////////////////////////////
// End of defining $database //
///////////////////////////////

//////////////////////////////////////
// Create Database if doesn't exist //
//////////////////////////////////////
$conn = mysql_connect($host , $user , $password) or die( mysql_errno().': '.mysql_error() );

$query = "CREATE DATABASE IF NOT EXIST $database";

$result = mysql_query($query, $conn)
    or die(mysql_error());
echo "$result hi";
mysql_close($conn); 
/////////////////////////////////////////////
// End of Create Database if doesn't exist //
/////////////////////////////////////////////


?>

I personally can't see anything wrong with that code, but yet it comes up with this response.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]database_1

Access denied for user: 'username@localhost' to database 'database_1'[/quote]

The problem is this script uses my highest privilage account. But yet this account cannot create the database. I know that its not that im not supposed to be creating database's (i have unlimited number of database's as part of my hosting).

I was wondering if anyone could see any reason why this would be happening, I dont think its the code, but i want to make 100% certain before I start talking to my hosting company about this.

Thanks alot
Satal Keto

#2 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 01 January 2006 - 10:17 PM

Nothing wrong with the code. Only thing I can come up with is wrong user/pass

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 January 2006 - 12:17 AM

First, I thought it was "IF NOT EXISTS", plural, but I could be mistaken. Second, you need the CREATE privilege to do this. The manual has a very funny sounding line in it:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need the CREATE privilege on the database. [/quote]

What this means that is that you need to GRANT your user all of the DB-level privileges for your newly "named" DB _before_ you try and CREATE it using that user in order for this to work. This won't happen automatically. If you read on in the MySQL documentation, you'll see what I mean:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]MySQL allows you to grant privileges even on database objects that do not exist. In such cases, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to allow the database administrator to prepare user accounts and privileges for database objects that are to be created at a later time. [/quote]

Try something like (UNTESTED):

GRANT ALL ON db_name.* TO 'username@localhost'


Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users