Error: SQLSTATE[HY000] [2002] No such file or directory

Question

I know this type of question as probably been asked a couple of times, but they all have something to do with laravel, in my case this is vanilla php with no framework.

I have a form that that will collect form from users and save it into the database as it should, but i keep getting this error about MYSQL Error: SQLSTATE[HY000] [2002] No such file or directory. Here is my database config file and the file that includes it:

// Database config 
<?php

$host = 'localhost';
$user = 'admin';
$password = '123456';
$db_name = 'nairobi';

$dsn = 'mysql:host='.$host.';dbname='.$db_name;
$options = [ 
  PDO::ATTR_PERSISTENT => true,
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

    // Create a new PDO instance 
    try {
      $dbh = new PDO($dsn, $user, $password);
    } catch (PDOException $e) {
      print "Error: " . $e->getMessage() . "</br>";
      die();
    }

?>

<?php 
require "./library/Database.php";

if (isset($_POST['submit'])) {

  $first_name = $_POST['first_name'];
  $last_name = $_POST['last_name'];
  $email = $_POST['email'];

  $sql = 'INSERT INTO TABLE members(first_name, last_name, email) 
  VALUES (:first_name, :last_name, :email)';
  $stmt = $dbh->prepare($sql);
  $stmt_value = [
    ':first_name' => $first_name, 
    ':last_name' => $last_name, 
    ':email'=>$email
  ];
  $stmt->execute($stmt_value);
}


?>

Yes, I know the script that process the form is not secure.

Also, I get the following error when I access the page

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/library/Database.php:19 Stack trace: #0 /var/www/html/index.php(2): require() #1 {main} thrown in /var/www/html/library/Database.php on line 19

If needed, this is the sql I have gone with

CREATE TABLE IF NOT EXISTS members(
  member_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  reference_code INT NOT NULL
);

This entire project is also inside a docker container, here is the docker-compose.yml file

version: '3.7'

services:
  php:
    container_name: nairobi_php
    build:
      context: ./
    volumes:
      - './src:/var/www/html'
    depends_on:
      - mysql
    ports:
      - 80:80

  mysql:
    container_name: nairobi_mysql
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: CUeHpADRmZCtnTFGctxp
      MYSQL_DATABASE: nairobi
      MYSQL_USER: admin
      MYSQL_PASSWORD: 123456
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 3306:3306

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Answer 1

I finally solved the problem, thanks @Dlk for your help.

The cause of the problem was because in database.php, I was referring to the host for mysql as localhost instead of the name of the MYSQL service in the docker-compose.yml file. Hence database.php file should look like so :

<?php

$host = 'nairobi_mysql'; // Must be the service name of the database in `docker-compose.yml`
$db_name = 'nairobi';
$user = 'admin';
$pass = '123456';
$charset = 'utf8mb4'; // Always set charset for database
$port = '3306'; 

$dsn = "mysql:host=$host;dbname=$db_name;port=$port;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>

which correlates with my docker-compose.yml file

version: '3.7'

services:
  php:
    container_name: nairobi_php
    build:
      context: ./
    volumes:
      - './src:/var/www/html'
    depends_on:
      - mysql
    ports:
      - 80:80

  mysql:
    container_name: nairobi_mysql
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: CUeHpADRmZCtnTFGctxp
      MYSQL_DATABASE: nairobi
      MYSQL_USER: admin
      MYSQL_PASSWORD: 123456
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 3306:3306

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Answer 2

First of all change your connection like this: replace codes in Database.php with the following codes and read comments in codes.

$host = 'localhost';
$db   = 'nairobi';
$user = 'admin';
$pass = '123456';
$charset = 'utf8mb4'; // Always set charset for database
$port = '3308'; //Your port can be 3306 or 3307

$dsn = "mysql:host=$host;dbname=$db;port=$port;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $dbh = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Now your codes! you are using prepare statements which makes your codes safe enough. Check if url to Database.php file is correct and change yours to this

require_once "./library/Database.php";
    if (isset($_POST['submit'])) {

      $first_name = $_POST['first_name'];
      $last_name = $_POST['last_name'];
      $email = $_POST['email'];
      $reference_code = $_POST['reference_code'];
    //You have a `TABLE` name call members so you dont need `TABLE` its causing problem
    //Your query should look like this
      $sql = 'INSERT INTO members (first_name, last_name, email, reference_code) VALUES (:first_name, :last_name, :email, :reference_code)';
      $stmt = $dbh->prepare($sql);
      $stmt->execute([$first_name, $last_name, $email, $reference_code]);
    //I removed array here which you dont need, you can directly add fields in execute.
      $stmt->closeCursor();
    //Use `closeCursor()` to free your connection instead unset or close.
    }

Final make sure you created a database. make sure you created a table call members in database.

if you have done that all, your codes will work without any problem.

HTML form should look like this, you need to add multi encrypt into form if you want to upload images.

<form action="yourpage.php" method="post">
  <div class="container">first_name</b></label>
    <input type="text" placeholder="Enter first_name" name="first_name" required>

    <label for="last_name"><b>last_name</b></label>
    <input type="text" placeholder="Enter last_name" name="last_name" required>

    <label for="email"><b>email</b></label>
    <input type="email" placeholder="Enter email" name="email" required>

    <label for="reference_code"><b>reference_code</b></label>
    <input type="text" placeholder="Enter reference_code" name="reference_code" required>

    <button type="submit">Login</button>
  </div>
</form>

Datatable should look like this :

CREATE TABLE IF NOT EXISTS `members` (
  `member_id` int(8) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_code` int(11) NOT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;