Accesee's Blog

Docker:PHP/PDO + MySQL.. How to?

Docker:PHP/PDO + MySQL.. How to?

Mattia Ducci's photo
Mattia Ducci
·

3 min read

Table of contents

Photo by fabio on Unsplash

Context

A PHP server and a MySQL database. This is a very common setup. You’ve to create a PHP script that interact with that database, but you’ve no access to the server because only script from localhost works due to firewall security restriction for example.

Problem: To test your script you’ve to open a FTP client, upload the .php script to the server and test it. Every time the php file changes you’ve to repeat this procedure, not considering that make attempts in production is a very bad practice.

Solution: Docker

Docker can come to rescue as he let the developer to create in a few steps an environment similar to the production one. This even allow developer testing their code in a local environment without fear to make mistakes!

There are ready to use containers for PHP and MySQL, many tutorial and online resources that tell how to put them together with and without docker-compose. I found this one very useful using docker-compose, and this one not using it, but I struggled trying to use PHP+PDO to simply open a connection with my MySQL database only reading these articles. The reason why I’d use PDO is that MySQLi extension has been deprecated, as W3C says.

Environment configuration problems (the reason why I’m writing this article):

Communication from Host to MySQL container worked, but communication from PHP container to MySQL container not.

docker-compose and an adequate variable assignment in the .php file are the keys to solve the problem

Here’s my docker-compose:

version: '3'

services:
  database:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: 123
      MYSQL_DATABASE: stt_db
      MYSQL_USER: devuser
      MYSQL_PASSWORD: 123
    ports:
      - "9906:3306"
  web:
    build: .
    container_name: php_web
    depends_on:
      - database
    volumes:
      - ./local/:/var/www/html/
    ports:
      - "8100:80"
    stdin_open: true
    tty: true

We can see that db image has left as is (mysql:5.7), but not the web one, since we’ve to install PDO extension to the base PHP image. Note that both containers are exposed to host by ports 8100 for PHP container and by 9906 port for MySQL database: in this way we can connect to it through mysql-cli from host terminal with a command like the one below:

mysql -uroot -p123 -h127.0.0.1 -P9906

where -P stands for “Port” and must match with the hots-exposed one defined in docker-compose. -p stands for “password” and -u stands for “user”.

Below the Dockerfile

FROM php:7.4-cli
COPY ./local /usr/src/dst_folder
WORKDIR /usr/src/dst_folder
RUN docker-php-ext-install pdo pdo_mysql

last line install the required extensions.

Let's put things together

At the end, this is my MySQL PHP connection script:

<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
 <?php  
 $servername = "database";
 $username = "root";
 $password = "123";
 $dbname = "stt_db";
 $port = "3306";

 try{
    $conn = new PDO("mysql:host=$servername;port=$port;dbname=$dbname",$username,$password);
    $conn -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    echo "Connected succesfully";
 } catch(PDOException $e){
    echo "Connection failed: " . $e -> getMessage();
 }
 ?> 
 </body>
</html>

note two important things:

  • $servername is set to the mysql service name in docker-compose.yml since docker-compose allow containers declared inside docker-compose.yml to communicate each other by service name. No worries about IP address
  • $port is set to the in-network port where “in-network” is the one created automatically by docker-compose when run by docker-compose up
  • browse http://localhost:8100 and you’ll se the message: “Connected succesfully”

An happy runner

Photo by Wesley Eland on Unsplash

Thanks for reading :)

PHPDockerMySQL