PHP: Connect to MySQL

Learn how to connect to MySQL from PHP using MySQLi and PDO

👋 Welcome to the Stackhero documentation!

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

  • Deploy your application in seconds with a simple git push.
  • Use your own domain name and benefit from the automatic configuration of HTTPS certificates for enhanced security.
  • Enjoy peace of mind with automatic backups, one-click updates, and straightforward, transparent, and predictable pricing.
  • Get optimal performance and robust security thanks to a private and dedicated VM.

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

Connecting PHP to MySQL with MySQLi (object-oriented style)

The following example demonstrates how to connect PHP to MySQL using MySQLi in an object-oriented style. It is best practice to create a dedicated database and user for your application rather than using the 'root' account. This approach improves your application's security.

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$user = 'root';
$password = '<yourPassword>';
$database = 'root'; // In production, create a dedicated database and user in phpMyAdmin

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

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

$mysqli->close();

?>

Connecting PHP to MySQL with MySQLi (procedural style)

If you prefer a procedural approach, the example below shows how to establish a connection using MySQLi in a procedural style. As always, using a dedicated database and a restricted user is recommended for enhanced security.

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$user = 'root';
$password = '<yourPassword>';
$database = 'root'; // For enhanced security, create a dedicated database and user in phpMyAdmin

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

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

mysqli_close($mysqli);

?>

Connecting PHP to MySQL with PDO

PDO offers a flexible method for connecting PHP to MySQL. The example below illustrates how to use PDO to establish a connection. It also addresses potential SSL certificate issues that may arise if your system lacks access to CA certificates.

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$user = 'root';
$password = '<yourPassword>';
$database = 'root'; // For production environments, consider creating a dedicated database and user

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

$options = array(
  // If you encounter an error like "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1...",
  // it may be because the /etc/ssl/certs/ directory is missing CA certificates
  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";

?>

Handling the error "SSL operation failed with code 1"

If you see an error such as Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed, it is likely that your system's /etc/ssl/certs/ directory does not contain the necessary CA certificates.

If you have access to the system running your PHP code, you can install the certificates as follows:

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

If you do not have direct access to install these certificates on your system, you can install the certificate manually:

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

Connecting PHP to MySQL using credentials from environment variables

For enhanced security and easier configuration management, it is recommended that you avoid hardcoding your credentials in your source code. Instead, retrieve these settings from environment variables as shown below:

<?php

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

?>

Using environment variables helps maintain the security of your credentials and makes your configuration more adaptable across different environments.

Other articles for PHP