CODEWITHSUNDEEP
phpmysqldatabase
Tharindu Thisarasinghe
Tharindu Thisarasinghe

Reputation: 3998

PHP code doesn't create the database

In my web application, it has a self installing functionality so, after it is copied to the server, you just need to run the install.php file which has the following PHP code. 

define("DB_SERVER","localhost");
define("DB_USER","db_user");
define("DB_PASS","pass");

//create mysql connection
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS);

if(mysqli_connect_errno()){
    die("Connection Error");
}else{
    echo "MySQL Connection Successful!";
}


//create database if not exists
$sql = "CREATE DATABASE equiz";
$mysqlQuery = mysqli_query($connection, $sql);

if($mysqlQuery){
    echo "Database Created Successfully";
}else{
    die("Database Not Created!");
}

In the localhost, this works perfectly fine. But, when this is moved to the server, it gives me this error

Access denied for user 'db_user'@'localhost' to database 'equiz'

I had created the user db_user in advance along with another database and I'm sure he was granted with all the permissions. But I'm not sure if those permissions were for that particular database only.

Anyway any clue for this error ? I know little bit PHP but very new to real world web servers.

Upvotes: 1

Views: 467

Answers (2)

Fakhruddin Ujjainwala
Fakhruddin Ujjainwala

Reputation: 2553

You need to set privilege for that user:

GRANT INSERT, SELECT, DELETE, UPDATE ON database.* TO 'db_user'@'localhost' IDENTIFIED BY 'password';

Then try running your code.

If the above does not work out try creating the user will all rights:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'user_name'@'localhost';
FLUSH PRIVILEGES;

To create using PHP:

//Creation of user "user_name"

$mysqli->("CREATE USER 'user_name'@'%' IDENTIFIED BY 'pass_word';");

//Creation of database "new_db"

$mysqli->("CREATE DATABASE `new_db`;");

//Adding all privileges on our newly created database

$mysqli->("GRANT ALL PRIVILEGES on `new_db`.* TO 'user_name'@'%';");

Update:

Connect to database

// localhost <==> user_name <==> password <==> database
$mysqli = new mysqli("localhost", "user_id", "passwlrd", "db_name");

/* check connection */
if ($mysqli->connect_errno) 
{
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

Upvotes: 2

Farside
Farside

Reputation: 10343

The key here is:

Access denied for user 'db_user'@'localhost' to database 'equiz'

If it's your localhost, then probably you gotten phpMyAdmin installed. You need to grant access rights to your db_user. How to do this, there's explicit manual:

http://docs.phpmyadmin.net/en/latest/privileges.html

You also can do it manually, if you don't have phpMyAdmin installed, reference the official MySQL manual, or tutorials

Upvotes: 2

Related Questions