Php Performing Crud Operations Complete Guide

Top 10 Questions and Answers: PHP Performing CRUD Operations

1. What are CRUD operations in PHP?

CRUD stands for Create, Read, Update, and Delete. These are the four basic functions that can be performed on database records via PHP scripts:

• Create: Insert new data into the database.
• Read: Retrieve existing data from the database.
• Update: Modify existing data in the database.
• Delete: Remove data from the database.

2. How do you connect to a MySQL database in PHP?

To connect to a MySQL database, you typically use mysqli_connect() or PDO (PHP Data Objects) for newer applications. Here’s an example using mysqli:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

For PDO:

try {
    $conn = new PDO("mysql:host=localhost;dbname=myDB", 'username', 'password');
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

3. How do you insert data into a MySQL database using PHP?

You can insert data into a MySQL database using mysqli_query() for mysqli or prepared statements with PDO for better security against SQL injection. Here's an example using mysqli:

$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

And using PDO:

try {
    $stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (:username, :email)");
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':email', $email);
    $username = "JohnDoe";
    $email = "john@example.com";
    $stmt->execute();

    echo "New record created successfully";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

4. How can you retrieve data from a MySQL database using PHP?

To retrieve records, use SELECT queries. With mysqli, it would look like this:

$sql = "SELECT id, username, email FROM Users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Username: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

Using PDO:

try {
    $stmt = $conn->prepare("SELECT id, username, email FROM Users");
    $stmt->execute();
    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
    foreach($stmt->fetchAll() as $k=>$v) { 
        echo $v['id'] . " - " . $v['username'] . " - " . $v['email'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

5. Can you provide an example of updating data in a MySQL database?

Sure, here's how you can update data using both methods.

With mysqli:

$user_id = 1;
$new_email = 'john_new@example.com';

$sql = "UPDATE Users SET email='$new_email' WHERE id=$user_id";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

Using PDO:

try {
    $stmt = $conn->prepare("UPDATE Users SET email = :email WHERE id = :id");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':email', $email);

    $id = 1;
    $email = 'john_new@example.com';
    $stmt->execute();

    echo "Record updated successfully";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

6. How do you delete a record from a MySQL database in PHP?

Here’s how to delete a specific record using both methods:

With mysqli:

$user_id = 1;

$sql = "DELETE FROM Users WHERE id=$user_id";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

Using PDO:

try {
    $stmt = $conn->prepare("DELETE FROM Users WHERE id=:id");
    $stmt->bindParam(':id', $id);
    $id = 1;
    $stmt->execute();

    echo "Record deleted successfully";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

7. Why should I prefer using PDO over mysqli when executing queries?

PDO and mysqli are both great options but PDO offers several advantages:

• Portability: PDO supports twelve different types of databases, making migrations easier.
• Security: Provides prepared statements which help prevent SQL injection attacks.
• Object-oriented interface: Easier to use for complex queries with object-oriented programming.
• Flexibility: Offers more control over transactions and error handling.

8. How can I handle errors gracefully during database operations in PHP?

Handling errors gracefully ensures your application doesn’t crash if something goes wrong with the database. For PDO:

try {
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // execute query...
} catch(PDOException $e) {
    echo "An error occurred: " . $e->getMessage();
}

For mysqli, check functions’ return values and use mysqli_error():

$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";

if (!mysqli_query($conn, $sql)) {
    throw new Exception("Query failed: " . mysqli_error($conn));
} else {
    echo "New record created successfully";
}

9. How do you handle forms in PHP for CRUD operations?

Handling forms involves collecting user inputs in HTML forms and processing them with PHP server-side. Here's a simple form to create a user:

HTML Form:

<form action="create_user.php" method="post">
    Username: <input type="text" name="username"><br>
    Email: <input type="text" name="email"><br>
    <input type="submit">
</form>

create_user.php:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

$uname = $_POST['username'];
$email = $_POST['email'];

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $uname, $email);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

10. Can you show how to perform CRUD using AJAX for non-refresh operations?

Absolutely, let's demonstrate how to perform a CREATE operation using AJAX.

HTML:

<div id="user_form">
    <input type="text" id="username" placeholder="Username" required>
    <input type="email" id="email" placeholder="Email" required>
    <button onclick="addUser()">Add User</button>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
function addUser(){
    var username = $('#username').val();
    var email = $('#email').val();

    $.post("users.php", 
    {
        action: "add",
        username: username,
        email: email
    },
    function(response){
        alert(response);
    });
}
</script>

users.php:

<?php
header('Content-Type: application/json');

$action = $_POST['action'];
$username = $_POST['username'];
$email = $_POST['email'];

$servername = "localhost";
$db_username = "db_username";
$db_password = "db_password";
$dbname = "myDB";

$conn = new mysqli($servername, $db_username, $db_password, $dbname);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try{
    if($action === "add"){
        $stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (:username, :email)");
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        
        echo json_encode(['success' => true, 'message'=> 'User added successfully']);
    }else{
        echo json_encode(['success' => false, 'message'=> 'Invalid action']);
    }
}catch(Exception $e){
    echo json_encode(['success' => false, 'message'=> 'Database error: '.$e->getMessage()]);
}

$conn = null;
?>

By using AJAX combined with PHP’s PDO or mysqli library in a JSON response format, you can update the webpage dynamically without refreshing the page.