[SOLVED] Connect to MySQL database in PHP

Connect to MySQL database in PHP

I'm trying to build a blog website.
It is deployed on Heroku and it is supposed to connect to a MySQL database. The info required to login to my database is stored in an environment variable on Heroku, and looks like this (These are fake credentials of course):

mysql://g46w916ds134b8:639f463e@us-cdbr-east-03.cleardb.net/heroku_45fab1d19h35yetf?reconnect=true

It contains the DB name, the user, the password and the host.

Is there a way to use this one string directly in my PHP code to connect to the database? I checked MySQLi and PDO documentation, and it seems like they only accept DSN/user/password or Host/user/password/DBname format.

Solution

This is a url after all, so you can use parse_url function to extract data.

// Connection string from environmental variable in heroku
$connectionStringHerokuEnv = 'mysql://g46w916ds134b8:639f463e@us-cdbr-east-03.cleardb.net/heroku_45fab1d19h35yetf?reconnect=true';
$parsed = parse_url($connectionStringHerokuEnv);
$dbname = ltrim($parsed['path']. '/'); // PATH has prepended / at the beginning, it needs to be removed
// Connecting to the database
$conn = new PDO("{$parsed['scheme']}:host={$parsed};$dbname={$dbname};charset=utf8mb4", $parsed['user'], $parsed['pass'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

For database connection you should always use PDO and not mysqli driver. PDO allows you to connect to almost any database, without rewriting code in 85% of cases.

dont forget options [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION], this will allow you to catch any errors and handle them accordingly to application needs.

PDO accept this connection string driver: host=DATABASE_HOST;dbname=DATABASE_NAME; charset=DEFAULT_CHARSET(use utf8 whenever you can)

Learn more on parse_url: https://www.php.net/manual/en/function.parse-url

Learn more on PDO: https://www.php.net/manual/en/class.pdo.php