Connecting to MYSQL database with PDO
Before you proceed with this tutorial ensure that your server is PDO_MYSQL driver enabled, you can find this in the php.ini file. Created a PHP file with the name "pdo_db_conn.php" and copy the below code in it: This article breaks down a PHP script responsible for handling a user registration process using PHP Data Objects (PDO), a secure way to interact with a database. The script is designed to: Check if an email is already registered. If not, insert a new user record into the database. Handle exceptions and errors gracefully. Let’s go step by step to understand the purpose and flow of each part. 1. Input and PDO Prepared Statement The code begins by preparing an SQL query using the PDO prepare() method. The purpose of this initial query is to determine whether the email submitted by the user already exists in the registration table. prepare() sets up the SQL query with a placeholder (?) to safely inject the user-supplied email. execute([$email]) executes the prepared query with the provided email value. 2. Email Existence Validation The result of the execution is then validated using: 3. Proceeding with Registration If no matching email is found, the script proceeds to insert the new user details: This prepares an SQL INSERT statement with three placeholders corresponding to the name, email, and password fields. 4. Executing the Insert Query The script attempts to execute the insertion: If the insert is successful, it prints a confirmation message, "Successful". 5. Exception Handling There are two try-catch blocks used to handle exceptions: The inner try-catch handles exceptions that may occur during the insertion phase. The outer try-catch captures errors from the initial email check. In both cases, if a PDOException is caught, the error message is printed: This helps with debugging and alerts developers to potential issues in database operations. Security Considerations Prepared Statements: The use of placeholders (?) with prepare() and execute() protects against SQL injection, a common security threat in web applications. Password Storage: In production, passwords should be hashed before storage using functions like password_hash() instead of storing them in plain text. Error Exposure: Displaying raw exception messages to the user can expose sensitive information. In production, it's better to log the error internally and show a generic message to the user. Conclusion This code snippet demonstrates a basic yet essential pattern for registering users securely with PHP and PDO. It checks for duplicate emails before creating new entries, handles database operations using prepared statements, and includes exception handling to ensure robustness. Troubleshooting There are some common issues when you connect to a MySQL database: If the MySQL driver is not enabled in the php.ini file, you will get the error message: could not find driver If you provide an incorrect password, you get the following error message: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) If you provide an invalid database name or the database does not exist, you get the following error message: SQLSTATE[HY000] [1049] Unknown database 'dbname' If you provide an invalid database hostname, the following error message will display: SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known.
<?php
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "database_password";
try{
$pdo = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if($pdo){
echo "Successful";
}
}catch(PDOException $e){
echo $e->getMessage();
}
?>
Michael Isijola
About Author
I am an experienced IT expert with 10+ years of experience with a track record of success in creating apps that are both well-received and commercially viable. Skilled in working as a team and incorporating input into projects, a strong eye for detail, and tenacity to never quit on something until it is absolutely perfect. Innovative use of technology for excellent delivery of tasks, ability to complete projects efficiently, and satisfy customers with attractive, user-friendly applications. And also, the ability to oversee the development and dissemination of technology for external customers, vendors, and other clients to help improve and increase business.
