PHP not connecting to my database on Linux?

Question

I use mariadb as my database and phpmyadmin to access on databases web on Linux. Although I am able to connect to the database on the commandline as root but cannot check connection to the database with PHP? I am unsure if there's something I'm missing out?

My HTML/PHP Code:

<!DOCTYPE HTML>
<html>
<body>
        <?php
        $host = 'localhost';
        $user = root';
        $password = '';
        $db = 'testdb';

        $dbconnect=mysqli_connect($host,$user,$password,$db);

        if ($dbconnect->connect_error) {
                die("Database connection failed.");
        }

        ?>

</body>
</html>

What my web page displays:

"connect_error) { die("Database connection failed: " . $dbconnect->connect_error); } ?> "

What I've tried:

Changing to bind-address 0.0.0.0.
Allowing the port 3306 through firewall.

Still the same display.

You have a problem in your PHP Syntax `$user = root';` is missing a quotation mark. — allexiusw, Jun 28 '20 at 07:40

score 1 · Answer 1 · answered Jun 28 '20 at 07:55

1

In addition to the PHP issue, there is a mire fundamental issue here.

There are different MySQL authentication methods that can be used. Relevant ones here are:

Username / password
Socket authentication

MySQL is often configured so that it uses socket authentication for root user. In this case it means, the command line tool uses a UNIX domain socket to connect to the server and the server can check who is connecting by checking the user at the other end of socket.

This means that only processes running as root can connect to the MySQL server via UNIX domain socket.

Your web server is running under another user, so it cannot use the root account. Therefore you must create another user/password for your web server / PHPMyAdmin.

answered Jun 28 '20 at 07:55

Tero Kilkanen

36,796
3
41
63

How do you do the socket authentication? – TJH Jun 29 '20 at 01:13
You shouldn't use socket authentication with web applications. You need to create separate user for your web application with MySQL command line tool. – Tero Kilkanen Jun 29 '20 at 06:47
Created user, Granted privileges but my page is blank white? How do I check that mysql is working with localhost? – TJH Jun 29 '20 at 09:44
Your example PHP code connects to database. If the connection is succesful, it doesn't display anything extra. You need to add more to your PHP code to actually see something. – Tero Kilkanen Jun 29 '20 at 17:00
Ah right I see what you mean. I'll try that. – TJH Jun 29 '20 at 18:24
Can't seem to retrieve data from the database. I created errors to see if any error response appears but none – TJH Jun 29 '20 at 20:25

score 0 · Answer 2 · answered Jun 28 '20 at 07:49

Once you have fixed your syntax error in $user = root';...

From MySQL 8.0 Reference Manual 4.2.4 Connecting to the MySQL Server Using Command Options:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs: the client connects using a Unix socket file. The --socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

As PHP doesn't do that, the MySQL server cannot determine the user. Therefore, you must use password authentication. Also, for the sake of security, please don't do this as the root!

I fixed the syntax error and looked at the reference manual. Tired to perform the commands from there just comes up as syntax errors. Although there's no code error displaying but the page is blank now. I used root because I was trying to get the PHP to communicate with my database server. I also tried ""testuser" same problem. — TJH, Jun 29 '20 at 00:09

PHP not connecting to my database on Linux?

2 Answers2