PHP code doesn't create the database

Question

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.

create an another user and try once, it will hardly take 5 min. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Feb 24, 2016 at 11:00
There is db name. First create the mysql connection and then creates the database in the second query — Tharindu Thisarasinghe
– Tharindu Thisarasinghe, Commented Feb 24, 2016 at 11:00
@Anant I did create another user there's no option to add privileges unless I add that user to a database. What I need is create the DB by my PHP application. Not manually. — Tharindu Thisarasinghe
– Tharindu Thisarasinghe, Commented Feb 24, 2016 at 11:02

Farside · Accepted Answer · 2016-02-24 11:11:17Z

2

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

edited Feb 24, 2016 at 11:11

answered Feb 24, 2016 at 11:05

Farside

10.4k4 gold badges52 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fakhruddin Ujjainwala · Accepted Answer · 2016-02-24 11:31:42Z

2

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();
}

edited Feb 24, 2016 at 11:31

answered Feb 24, 2016 at 11:07

Fakhruddin Ujjainwala

2,55919 silver badges26 bronze badges

6 Comments

Farside Over a year ago

I think you need also to add CREATE privilege, as he tries to create the DB from PHP. dev.mysql.com/doc/refman/5.7/en/…

Tharindu Thisarasinghe Over a year ago

@FakhruddinUjjainwala Thanks, your code make sense, but let me clear this out, before your first query create user runs, mysqli_connection needs to be initialized. Right ? So, how to initialize mysqli connection before creating a user ? mysqli_connect() function needs a user.

Fakhruddin Ujjainwala Over a year ago

@TharinduLucky u must be having a user with admin rights. Use those credentials there.

Tharindu Thisarasinghe Over a year ago

@FakhruddinUjjainwala, Well, that makes sense, but, how to know whether I have a user with admin rights ? or If not how to create him ?

Fakhruddin Ujjainwala Over a year ago

@TharinduLucky normally your server login details are same as your database login. Unless some one has changed it. And the server login details by default have all the rights (query) to database.

|

Collectives™ on Stack Overflow

PHP code doesn't create the database

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related