Getting "Could not get data: Access denied for user ''@'localhost' to database 'college'"

Question

I am trying out following code:

index.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>

        <?php
        include 'config.php';


        if (!$conn) {
            die('Could not connect: ' . mysql_error());
        }

        $sql = 'SELECT name, gender, age FROM student';
        mysql_select_db('college');
        echo $sql;
        //echo $conn[] ;
        $retval = mysql_query($sql, $conn);

        if (!$retval) {
            die('Could not get data: ' . mysql_error());
        }

        while ($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
            echo "Name :{$row['name']}  <br> " .
            "Gender : {$row['gender']} <br> " .
            "Age : {$row['age']} <br> " .
            "--------------------------------<br>";
        }

        echo "Fetched data successfully\n";

        mysql_close($conn);
        ?>

    </body>
</html>

config.php

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'college');

/* Attempt to connect to MySQL database */
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
#$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);

if($conn === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

I am getting following error:

Could not get data: Access denied for user ''@'localhost' to database 'college'

As per this thread, I have run:

GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'root';

in phpAdmin and it worked. But still getting same error.

If I remember this correct, I was able to work with college database using root user earlier, but now I am getting this exception. Whats going wrong here?

Do not use mysql use mysqli or direct PDO for an database connection. — Richard, Jun 22 '19 at 18:42
That's weird, you clearly pass "root" as second parameter, but error message says user is ''@'localhost', i.e. username part is empty string. Can you double-check that part please? Maybe put literal value there instead of constant (just for testing). — alx, Jun 22 '19 at 19:11

score 1 · Answer 1 · answered Jun 22 '19 at 19:13

I know what's the problem.

In config.php you initialize connection with mysqli_connect -- note i after mysql.

But later you use mysql_query, no i. That's a different driver which tries to connect using default (empty) credentials. That's why you see empty username in error message.

So, to fix that, replace all mysql_ functions with mysqli_ versions.

score -1 · Answer 2 · answered Jun 22 '19 at 18:58

The mysqli_connect() function returns an object which you have to use to make calls to your database.

I.E.

include 'config.php';

// Make a connection
$link = mysqli_connect($host, $username, $pw, $db);

// Create query.
$sql = 'SELECT name, gender, age FROM student';

// Apply query.
$result = mysqli_query($link, $sql);

For more information visit this link: https://www.php.net/manual/en/mysqli.query.php

Getting "Could not get data: Access denied for user ''@'localhost' to database 'college'"

2 Answers2