PHP MySQL connection string using PDO

Before using database we need to connect it first. This process is common in all types of database irrespective of language we use. We will learn here the code for connecting to MySQL database using PDO class.

PHP Data Object PDO installation or enable and creating connection string to manage MySQL database

It is always a good practice to keep the database connection details at a common place and call it from different pages where it is required. This helps in maintaining the code when the database connection details changes or the script is shifted to different server with different connection details. If we are not using a common file then to change the database details we need not change the connection string in all the files. By keeping all details at one place if we update the details then all the files will use the same ( updated ) details.

Podcast on MySQL database management using PHP PDO

We can keep the connection details in config.php file or any other file name you find suitable. All the codes inside this file are to be within the PHP code block, so it will not be exposed if it is opened directly in browser.

Let us start with the basic connection code in PDO for PHP & MySQL ( PHP MySQL Connection code for old system is here )

config.php

The code below is kept inside config.php file and can be connected from any other file in the script.

<?php
// Database Connection Configuration
// Use either MySQL or SQLite by uncommenting the respective section

$host_name = "localhost";
$database = "pdo"; // Change this to your actual database name
$username = ""; // Your MySQL database username
$password = ""; // Your MySQL database password

//////// Establish Connection to MySQL Database ////////
try {
    $dbo = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
    print("Error!: " . $e->getMessage() . "<br/>");
    die();
}

///// For SQLite Database Connection /////
// Uncomment the line below to switch to SQLite
// $dbo = new PDO('sqlite:' . dirname(__FILE__) . '/my_student.db');

?>

Example 2: Connecting to a PostgreSQL Database Using PHP PDO

Connecting to a PostgreSQL database with PDO is similar to MySQL but requires a different DSN. Here’s an example:

<?php
try {
    $dbo = new PDO('pgsql:host=localhost;dbname=testdb', 'username', 'password');
    $dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Example 3: Setting Connection Timeout in PDO

It’s possible to define a connection timeout by setting the ATTR_TIMEOUT attribute:

<?php
$dbo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');
$dbo->setAttribute(PDO::ATTR_TIMEOUT, 5); // Timeout set to 5 seconds
?>

Example 4: Using Persistent Connections in PDO

Persistent connections improve performance by reusing the same connection rather than opening a new one each time:

<?php
$dbo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password', array(
    PDO::ATTR_PERSISTENT => true
));
?>

Handling Connection Errors Gracefully

Handling connection errors in a structured way helps improve user experience and debugging:

<?php
try {
    $dbo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');
    $dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful!";
} catch (PDOException $e) {
    error_log("Connection failed: " . $e->getMessage()); // Log the error
    echo "Database connection could not be established.";
}
?>

Google Cloud & MySQL connection using PDO

<?Php
$host_name = "34.68.103.244";
$database = "my_tutorial"; // Change your database name
$username = "root-plus2net";          // Your database user id 
$password = "************";          // Your password

//////// Do not Edit below /////////
try {
$dbo = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}

/// Sample script to display records ////
$sql="select * from student ";

echo "<table>";
foreach ($dbo->query($sql) as $row) {
echo "<tr ><td>$row[name]</td></tr>";
}
echo "</table>";
?>

Details of MySQL database setup
More on Managing MySQL database at Google cloud

Download Zip file to test your PHP PDO script

PDO References PDO Fetch record

Subscribe to our YouTube Channel here

	02-10-2021
cant we connect to a phpmyadmin sql database?

PHP MySQL connection using PDO