Testing php + mysql

[chandrakn]

Hi nanoninja, Can you add simple code to give example connection from php to mysql is working?

[nanoninja]

Hi @chandrakn

If you use the default configuration you already have an example here. Perhaps you wanted something else ?

For more information about connecting PHP with MySQL I recommend you go to the official documentation of PDO.

Vincent.

[makros]

Hi @nanoninja I've some questions about database connection. Im trying to setup a class with some common mysql functions but dunno where and what to set up the files inside base project structure.

First of all I'm looking for an structure as Laravel has to develope some models, views and public files and then, set up a new database class with the methods mentioned and finally test if it is working.

Edit: I've tried as you mentioned here and error response is giving me an SQLSTATE[HY000] [2002] No such file or directory

Is it there a way to change mysql dir permissions on docker-compose file config?

Thanks for your time =)

[makros]

Honestly, it could be very helpfull if someone manage a project structure MVC Oriented with this incredible code =)

[furqank786]

@nanoninja Stuck with the database connectivity using the PHP PDO it return the error "SQLSTATE[HY000] [2002] Connection refused". My settings to connect database given below: { ----- laravel .env file configuration for database ------ DB_DRIVER=mysql DB_HOST=mysql or 172.23.0.3 or 192.168.99.100 (virtual machine ip) DB_DATABASE=test DB_USERNAME=dev DB_PASSWORD=dev DB_PORT=8989 charset=utf8 } I think connection refused mean issue with the port, may be I am wrong but please help me to get out of this problem I shall be thankful.

[makros]

Check your DB_HOST name, in my case, it must be same name as mysql container name: mysqldb

[furqank786]

@makros Thank you for your guidance I updated the DB_HOST : mysqldb but still same connection refused error. My database connection settings given below: DB_DRIVER=mysql DB_HOST=mysqldb DB_DATABASE=test DB_USERNAME=dev DB_PASSWORD=dev DB_PORT=3306 charset=utf8

[nanoninja]

Hello @furqank786,

"DB_HOST" is the name given to the container to connect to the database. Basically it's "mysql" and if you change the name make sure to stop and restart the services.

# stop services
docker-compose down -v

# start services
docker-compose up -d

For the connection with PDO, the dsn host refers to DB_HOST that you will configure in the ".env" file.

If you have changed DB_HOST to "mysqldb" then you must specify it when connecting to the database as follows :

<?php
    try {
        $dsn = 'mysql:host=mysqldb;dbname=test;charset=utf8;port=3306';
        $pdo = new PDO($dsn, 'dev', 'dev');
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
?>

Can I see your PHP code to connect to the database?

If you have a particular configuration, tell us a little more than we can provide clarification if necessary.

[furqank786]

Hi @nanoninja, thanks for your detail reply. I did the same as you mentioned with no success if I connected through your PHP snippts (try catch) $pdo object return null. PDO Object ( )

I am working on a laravel project and placed my project in the web folder which work correctly. I am able to connect database successfully with phpmyadmin as following credentials: (http://192.168.99.100:8080) (host = mysql , username = root or dev, password = root or dev)

But when I connect through laravel application's .env settings (or even with index file with your snippets) then I face the problem "SQLSTATE[HY000] [2002] Connection refused" as I mentioned in my previous comments. These are my files code may be I am missing something.

docker-compose.yml

version: '3' services: web: image: nginx volumes:

• "./etc/nginx/default.conf:/etc/nginx/conf.d/default.conf"
• "./etc/ssl:/etc/ssl"
• "./web:/var/www/html"
• "./etc/nginx/default.template.conf:/etc/nginx/conf.d/default.template" ports:
• "8000:80"
• "3000:443" environment:
• NGINX_HOST=${NGINX_HOST} command: /bin/bash -c "envsubst '$$NGINX_HOST' < /etc/nginx/conf.d/default.template > /etc/nginx/conf.d/default.conf && nginx -g 'daemon off;'" restart: always depends_on:
• php
• mysqldb php: image: nanoninja/php-fpm restart: always volumes:
• "./etc/php/php.ini:/usr/local/etc/php/conf.d/php.ini"
• "./web:/var/www/html" composer: image: "composer" volumes:
• "./web/app:/app" command: install myadmin: image: phpmyadmin/phpmyadmin container_name: phpmyadmin ports:
• "8080:80" environment:
• PMA_ARBITRARY=1
• PMA_HOST=${MYSQL_HOST} restart: always depends_on:
• mysqldb mysqldb: image: mysql container_name: ${MYSQL_HOST} restart: always env_file:
• ".env" environment:
• MYSQL_DATABASE=${MYSQL_DATABASE}
• MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
• MYSQL_USER=${MYSQL_USER}
• MYSQL_PASSWORD=${MYSQL_PASSWORD} ports:
• "8989:3306" volumes:
• "./data/db/mysql:/var/lib/mysql"

---

environment .env file

!/usr/bin/env bash

Nginx

NGINX_HOST=localhost

MySQL

MYSQL_HOST=mysql MYSQL_DATABASE=test MYSQL_ROOT_USER=root MYSQL_ROOT_PASSWORD=root MYSQL_USER=dev MYSQL_PASSWORD=dev

---

Laravel .env file in the web folder

APP_ENV=local APP_DEBUG=true APP_URL=http://localhost APP_KEY= application key will appear here

DB_DRIVER=mysql DB_HOST=mysql DB_DATABASE=test DB_USERNAME=root DB_PASSWORD=root DB_PORT=3306 charset=utf8

CACHE_DRIVER=file SESSION_DRIVER=file QUEUE_DRIVER=sync

---

Database.php Laravel config file and the .env variable correctly populate in the mysql section. It's default file with no change.

return [ 'default' => env('DB_DRIVER', 'sqlite'), 'connections' => [ 'sqlite' => [ 'driver' => 'sqlite', 'database' => env('DB_DATABASE', database_path('database.sqlite')), 'prefix' => '', ], 'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), 'unix_socket' => env('DB_UNIX_SOCKET', null), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => env('DB_PREFIX', null), 'strict' => false, 'engine' => null, ], 'pgsql' => [ 'driver' => 'pgsql', 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '5432'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'charset' => 'utf8', 'prefix' => env('DB_PREFIX', null), 'schema' => 'public', 'sslmode' => 'prefer', ],

],
'migrations' => 'migrations',
'redis' => [
    'client' => 'predis',
    'default' => [
        'host'     => env('REDIS_HOST', '127.0.0.1'),
        'password' => env('REDIS_PASSWORD', null),
        'port'     => env('REDIS_PORT', 6379),
        'database' => env('REDIS_DATABASE', 0),
    ],

],

];

---

The full error message is given below:

Doctrine \ DBAL \ Driver \ PDOException (2002) SQLSTATE[HY000] [2002] Connection refused

This is weird that I am able to connect using phpmyadmin but not with the laravel project, must be some issue with my configuration. I have also changed the DB_HOST= mysql as .env has the DB_HOST= mysql.

Please check and that would be a great help with this fix. Thank you in advance.

[makros]

DB_HOST and MYSQL_HOST must be "mysqldb" I supposed

[nanoninja]

@furqank786, @makros,

If we keep the configuration : MYSQL_HOST=mysql

It should be "mysql" not "127.0.0.1" : 'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1')

[furqank786]

@makros may be I am wrong but mysql db container section use this statement: container_name: ${MYSQL_HOST} and this comes from the .env file which is mysql

[furqank786]

@nanoninja , thanks for the quick reply

I have updated 127.0.0.1 to mysql (side note: DB_HOST correctly load the "mysql" if it missed then look for 127.0.0.1)

But the same error of connection refused :(

[nanoninja]

@furqank786,

Your Laravel configuration:

DB_DRIVER=mysql DB_HOST=mysql DB_DATABASE=test DB_USERNAME=root DB_PASSWORD=root DB_PORT=3306 charset=utf8

Correction:

'driver' => 'mysql', 'host' => env('DB_HOST', '127.0.0.1'), replace by "mysql" 'unix_socket' => env('DB_UNIX_SOCKET', null), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), replace by "test" 'username' => env('DB_USERNAME', 'forge'), replace by "root" 'password' => env('DB_PASSWORD', '') replace by "root"

Or change the .env file from Docker project

[furqank786]

@nanoninja, @makros,

Can this be a problem? When I connect to mysql container using docker exec -it mysql bash and execute the following commands to check the bind address of the mysql: Command: mysqld --verbose --help | grep bind-address
Result: --bind-address=name IP address to bind to. bind-address *

but when I display docker ps -a it shows the bind address 0.0.0.0:8989->3306/tcp

[furqank786]

@nanoninja

Updated the database.php as you mentioned in the above comments. But still the same issue of connection refused. One more dtail when I run the command php artisan migrate then php_network_address error appear this is same due to host not found. Do I need to set the network address ? If yes then how can I please.

php artisan migrate error message

[Illuminate\Database\QueryException] SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known. (SQL: select * from information_schema.tables where table_schema = test and table_name = migrations) [Doctrine\DBAL\Driver\PDOException] SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known.

[PDOException] SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known. [PDOException] PDO::__construct(): php_network_getaddresses: getaddrinfo failed: No such host is known.

[nanoninja]

@furqank786,

Logically it is to access from the outside (0.0.0.0:8989). It is a port forwarding. The containers are linked together.

A classic connection with PDO works, take a simple file in PHP and tried to connect. If it does not work then the problem comes from the services.

[furqank786]

@nanoninja

Thank you for your effort, I check the database connection with the simple file.

[furqank786]

@nanoninja

The DB_HOST = mysql works like a charm, the issue was laravel environment configuration. Thank you @nanoninja and @makros for your help.

[zanematthew]

@furqank786 care to share what the;

laravel environment configuration.

Was?

[furqank786]

@zanematthew

yes the issue was with .env configurations. Thanks.