MySQL: Using MySQL with PHP

This documentation is part of the Getting started guide. View the full guide here: How to get started with MySQL.

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MySQL cloud solution that provides a host of benefits, including:

  • Unlimited connections and transfers.
  • phpMyAdmin web UI included.
  • Effortless updates with just a click.
  • Optimal performance and robust security powered by a private and dedicated VM.

Save time and simplify your life: it only takes 5 minutes to try Stackhero's MySQL cloud hosting solution!

MySQLi (object-oriented style)

Here is an example of connecting to MySQL using PHP's MySQLi extension in object-oriented style:

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // While this example uses the "root" database, it is a good idea to create a separate database and user for your application via phpMyAdmin.

$mysqli = mysqli_init();
$mysqliConnected = $mysqli->real_connect($hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die("Connection Error: " . $mysqli->connect_error);
}

echo 'Connection successful... ' . $mysqli->host_info . "\n";

$mysqli->close();

?>

MySQLi (procedural style)

If you prefer procedural code, here is how you can connect using MySQLi in procedural style:

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // For best security, create a dedicated database and user in phpMyAdmin rather than using "root".

$mysqli = mysqli_init();
$mysqliConnected = mysqli_real_connect($mysqli, $hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die("Connection error: " . mysqli_connect_error($mysqli));
}

echo 'Success: ' . mysqli_get_host_info($mysqli) . "\n";

mysqli_close($mysqli);

?>

PDO

If you prefer PDO for database access, here is a sample connection setup:

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // It is best to create a dedicated database and user for your application.

$dsn = "mysql:host=$hostname;port=$port;dbname=$database";

$options = array(
  // If you see SSL-related errors when connecting, make sure your system has the right CA certificates installed (see below).
  PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
  // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
  PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
);

$pdo = new PDO($dsn, $user, $password, $options);

$stm = $pdo->query("SELECT VERSION()");
$version = $stm->fetch();

echo "You are connected to a database running version " . $version[0] . "\n";

?>

Troubleshooting: SSL operation failed with code 1

If you see an error like this:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

This is likely because your system is missing CA certificates in /etc/ssl/certs/.

If you have access to the system where your PHP code runs, you can add the certificates like this:

  1. On Ubuntu/Debian, you might run: sudo apt-get install ca-certificates
  2. On Alpine Linux, you can run: apk add ca-certificates

If you do not have direct access, you can add the certificate manually:

  1. Download the certificate: https://letsencrypt.org/certs/isrgrootx1.pem
  2. Place the isrgrootx1.pem file in your PHP project.
  3. Comment out the line PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/'
  4. Uncomment the line PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem'

Resolving: undefined constant errors with PDO

If you see errors like:

Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_ATTR_SSL_CAPATH

or similar messages, this indicates that PDO was installed without MySQL support.

  1. On Ubuntu/Debian

You can add the required extension by running:

sudo apt-get install php-mysql
  1. In Docker

If you are using Docker, make sure MySQL support is included during the build. You can add this to your Dockerfile:

RUN docker-php-ext-install pdo pdo_mysql

Using MySQL with Symfony and Doctrine

If you are working with Symfony and Doctrine, you can configure your connection like this:

  1. Edit your .env file and set the DATABASE_URL variable:
DATABASE_URL="mysql://<USER>:<PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/<DATABASE>"
  1. Then, in config/packages/doctrine.yaml, set the driver and options:
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CAPATH
            1010: '/etc/ssl/certs'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

Troubleshooting: SSL operation failed with code 1

If you encounter an error like:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

This is likely because the /etc/ssl/certs/ directory is missing CA certificates.

If you can access the system, you can install them as follows:

  1. On Ubuntu/Debian: sudo apt-get install ca-certificates
  2. On Alpine Linux: apk add ca-certificates

If you do not have direct access, you can manually add the certificate:

  1. Download: https://letsencrypt.org/certs/isrgrootx1.pem
  2. Place isrgrootx1.pem in your Symfony project.
  3. Update config/packages/doctrine.yaml:
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CA
            1009: 'isrgrootx1.pem'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

Using MySQL with Laravel

To configure MySQL in Laravel, open config/database.php and update the MySQL section:

'mysql' => [
  'driver' => 'mysql',
  'host' => env('STACKHERO_MYSQL_HOST'),
  'port' => env('STACKHERO_MYSQL_PORT'),
  'username' => env('STACKHERO_MYSQL_USER'),
  'password' => env('STACKHERO_MYSQL_PASSWORD'),
  'database' => env('STACKHERO_MYSQL_USER'),
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_unicode_ci',
  'prefix' => '',
  'prefix_indexes' => true,
  'strict' => true,
  'engine' => null,
  'sslmode' => 'require',
  'options' => extension_loaded('pdo_mysql')
    ? array_filter([
      // For SSL errors, see troubleshooting above.
      PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
      // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ])
    : [],
],

Using MySQL with PHP CodeIgniter

In the database.php configuration file, you can add:

$db['default'] = array(
  'hostname' => getenv('STACKHERO_MYSQL_HOST'),
  'port'     => getenv('STACKHERO_MYSQL_PORT'),
  'username' => getenv('STACKHERO_MYSQL_USER'),
  'password' => getenv('STACKHERO_MYSQL_PASSWORD'),
  'database' => getenv('STACKHERO_MYSQL_USER'), // By convention, the database name matches the user.
  'dbdriver' => 'mysqli',
  'dbprefix' => '',
  'pconnect' => true,
  'char_set' => 'utf8',
  'dbcollat' => 'utf8_general_ci',
  'encrypt'  => array() // Important: enable TLS encryption
);

Connecting with environment variables in PHP

It is generally safer to avoid storing your database credentials in your code. Instead, you can use environment variables. Here is how you can retrieve them:

$hostname = getenv('STACKHERO_MYSQL_HOST');
$port = getenv('STACKHERO_MYSQL_PORT');
$user = getenv('STACKHERO_MYSQL_USER');
$password = getenv('STACKHERO_MYSQL_PASSWORD');
$database = getenv('STACKHERO_MYSQL_USER'); // By convention, the database name matches the user.

Continue reading this guide

  1. Read the full guide: Getting started
    How to get started with MySQL
  2. MySQL URLs
  3. Using MySQL with WordPress
  4. Using MySQL with Node.js
  5. Using MySQL with Node.js, NestJS, or TypeORM
  6. Using MySQL with Prisma
  7. Using MySQL with Django
  8. Connecting MySQL with Java/Spring
  9. Connecting MySQL with Groovy/Grails
  10. Creating a user on MySQL using phpMyAdmin

Other articles for MySQL

  1. Introduction
    Introduction to MySQL
  2. Advanced usage
    How to configure, optimize, export, or import MySQL data
  3. Configure GUIs
    How to connect graphical interfaces to MySQL
  4. Troubleshooting
    MySQL common issues and solutions
  5. Workbench
    How to configure MySQL Workbench
  6. Automate with the CLI
    Start MySQL, retrieve its credentials, and change its configuration programmatically with the Stackhero CLI
  7. GitHub Actions & GitLab CI
    Spin up a real MySQL service from your GitHub Actions or GitLab CI pipeline, run your tests against it, and tear it down automatically