MySQL in PHP

Overview

MySQL is one of the most popular relational database management systems. PHP offers various ways to interact with MySQL, including using the mysqli (MySQL Improved) extension and PDO (PHP Data Objects). In this section, we will cover:

• Connecting to a MySQL Database
• Performing CRUD Operations
• Prepared Statements
• Handling Errors
• Using PDO for MySQL
• Closing Database Connections

Connecting to a MySQL Database

Example (Using mysqli):

<?php
// MySQL server credentials
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Explanation:

• new mysqli() creates a new connection to the MySQL server.
• $conn->connect_error checks if the connection was successful.

Example (Using PDO):

<?php
// MySQL server credentials
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

try {
    // Create connection using PDO
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Explanation:

• new PDO() creates a new connection to the MySQL database using the PDO extension.
• PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION sets the error mode to throw exceptions.

Performing CRUD Operations

CRUD stands for Create, Read, Update, and Delete operations, which are essential for managing data in a database.

Create (Insert Data into Database)

Example (Using mysqli):

<?php
$sql = "INSERT INTO users (username, email, password) VALUES ('JohnDoe', '[email protected]', 'password123')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Example (Using PDO):

<?php
$sql = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password);

$username = 'JohnDoe';
$email = '[email protected]';
$password = 'password123';

$stmt->execute();
echo "New record created successfully";
?>

Explanation:

• Using mysqli: query() executes the SQL query directly.
• Using PDO: prepare() prepares the SQL query, and bindParam() binds the variables to the placeholders in the query.

Read (Fetch Data from Database)

Example (Using mysqli):

<?php
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}
?>

Example (Using PDO):

<?php
$sql = "SELECT id, username, email FROM users";
$stmt = $conn->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "id: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}
?>

Explanation:

• Using mysqli: fetch_assoc() fetches each row as an associative array.
• Using PDO: fetch(PDO::FETCH_ASSOC) fetches each row as an associative array.

Update (Update Data in Database)

Example (Using mysqli):

<?php
$sql = "UPDATE users SET email='[email protected]' WHERE username='JohnDoe'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}
?>

Example (Using PDO):

<?php
$sql = "UPDATE users SET email = :email WHERE username = :username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':username', $username);

$email = '[email protected]';
$username = 'JohnDoe';

$stmt->execute();
echo "Record updated successfully";
?>

Delete (Delete Data from Database)

Example (Using mysqli):

<?php
$sql = "DELETE FROM users WHERE username='JohnDoe'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

Example (Using PDO):

<?php
$sql = "DELETE FROM users WHERE username = :username";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':username', $username);

$username = 'JohnDoe';
$stmt->execute();
echo "Record deleted successfully";
?>

Prepared Statements

Prepared statements provide an additional layer of security by preventing SQL injection attacks.

Example (Using mysqli):

<?php
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username = ?");
$stmt->bind_param("s", $username); // "s" denotes string parameter type
$username = 'JohnDoe';
$stmt->execute();
$stmt->bind_result($id, $username, $email);
while ($stmt->fetch()) {
    echo "id: $id - Name: $username - Email: $email<br>";
}
$stmt->close();
?>

Example (Using PDO):

<?php
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username = :username");
$stmt->bindParam(':username', $username);
$username = 'JohnDoe';
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "id: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}
?>

Handling Errors

PHP allows you to handle database errors gracefully with mysqli_error() for mysqli and exceptions for PDO.

Example (Using mysqli):

<?php
$sql = "SELECT * FROM non_existent_table";
$result = $conn->query($sql);
if (!$result) {
    echo "Error: " . $conn->error;
}
?>

Example (Using PDO):

<?php
try {
    $conn->exec("SELECT * FROM non_existent_table");
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Closing Database Connections

It’s a good practice to close your database connection once it’s no longer needed.

Example (Using mysqli):

<?php
$conn->close();
?>

Example (Using PDO):

<?php
$conn = null;
?>

Conclusion

In this section, we covered how to interact with MySQL databases using PHP. We discussed the following topics:

• Connecting to MySQL: Using both mysqli and PDO methods.
• CRUD Operations: How to insert, read, update, and delete data in MySQL.
• Prepared Statements: To prevent SQL injection attacks and enhance security.
• Error Handling: Techniques for catching and handling errors when working with databases.
• Closing Connections: Properly closing database connections after usage.

By mastering MySQL with PHP, you can manage and manipulate your data effectively, creating dynamic web applications.

---

Connect to Database

• conn.php

<?php
$db_name = "potato";
$mysql_username = "root";
$mysql_password = "potato";
$server_name = "localhost";
$conn = mysqli_connect($server_name, $mysql_username, $mysql_password, $db_name);
if($conn){
	echo "connection success";
}else{
	echo "connection fail";
}
?>

• login.php

<?php 
require "conn.php";
$user_name = "lim";
$user_pass = "";
$mysql_query = "SELECT * FROM task_table WHERE name = '$user_name'; ";
$result = mysqli_query($conn, $mysql_query);
if(mysqli_num_rows($result)>0){
	echo "login success";
}else{
	echo "login fail";
}
?>