This content originally appeared on DEV Community and was authored by Luis Juarez
What is PlanetScale?
PlanetScale is a MySQL compatible serverless database platform. I think of it as GitHub for databases. You can use it to host your database, create branches, and preview the impact that merging your changes will do to your database.
Okay so how do I connect to it using PHP?
The recommended way to connect to a Planetscale database is to use the mysqli() class.
$mysqli = mysqli_init();
$mysqli->ssl_set(NULL, NULL, "/etc/ssl/cert.pem", NULL, NULL);
$mysqli->real_connect($_ENV["HOST"], $_ENV["USERNAME"], $_ENV["PASSWORD"], $_ENV["DATABASE"]);
$mysqli->close();
The title says WITH PDO!
I tend to prefer using PDO objects as a personal preference.
Using PDO with SSL requires us to also setup additional 'options' when instantiating the handle.
$dsn = 'mysql:host=$host;dbname=$database;port=3306';
$user = "";
$dbP = "";
$options = array(
PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/ca-certificates.crt',
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
);
try {
$db = new PDO($dsn, $user, $dbP, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $error) {
$msg = $error->getMessage();
echo $msg;
}
Let's break down the options we used to turn SSL on
PDO::MYSQL_ATTR_SSL_CA => 'path_to_cert.pem',
This sets the file path to the SSL certificate authority
In some cases the MYSQL_ATTR_SSL_CA may need to be set to "/etc/ssl/cert.pem" depending on the OS your server is running. Your best bet if you aren't sure would be to use this function:
openssl_get_cert_locations()['default_cert_file']
You may run into an error like "openssl s_client no cipher match" which is why we use this option
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
Settings the options variable
To be more concise I prefer to use the int values for the options array. So my options look like this. It is equivalent to what you see above.
$options = array(
1009 => "/etc/ssl/cert.pem",
1014 => false,
);
Final code snippet
Here is what I use to connect my PHP app to PlanetScale.
$dsn = 'mysql:host=$hostURL;dbname=$databaseName;port=3306';
$user = "";
$dbP = "";
$options = array(
1009 => "/etc/ssl/cert.pem",
1014 => false,
);
try {
$db = new PDO($dsn, $user, $dbP, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $error) {
$msg = $error->getMessage();
echo "error:".$msg;
}
I hope you found this helpful! If you did, or you have a question, drop a comment below or ping me on twitter @helloLuisJ
This content originally appeared on DEV Community and was authored by Luis Juarez
Luis Juarez | Sciencx (2022-03-26T01:16:17+00:00) Connecting to PlanetScale with a PDO Object in PHP. Retrieved from https://www.scien.cx/2022/03/26/connecting-to-planetscale-with-a-pdo-object-in-php/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.