PHP での CRUD 操作の初心者ガイド

CREATE TABLE users (
    id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    age INT(3) NOT NULL
)

<!-- Opening PHP tag to write PHP code -->
<?php

// Specifies the hostname of the MySQL server.
$servername = "localhost";

// The MySQL username. "root" is the default administrative username for MySQL.
$username = "root";

// The MySQL password for the specified user. It is empty ("") by default for the root user in many local development environments.
$password = "";

// The name of the database you want to connect to.
$dbname = "php_project";

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

// Check connection
if (!$conn) {
    // Log the error and display a generic message to the user
    error_log("Connection failed: " . mysqli_connect_error());
    die("Connection failed. Please try again later.");
}

// If the connection is successful, display or log a success message
echo "Connected successfully";

// Close the connection (optional, as it will close when the script ends)
mysqli_close($conn);

?>

<?php
// Set a value for each variable. Variables type of values should be same as set in database
$name = "person1";
$email = "person1@example.com";
$age = 25;

// Prepare the SQL statement
$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email, age) VALUES ($name, $email, $age)");

// Bind parameters to the prepared statement
mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

// Execute the prepared statement
if (mysqli_stmt_execute($stmt)) {
    echo "New record created successfully </br>";
} else {
    // Log the error for debugging purposes
    error_log("Error: " . mysqli_stmt_error($stmt));

    // Display a generic error message to the user
    echo "An error occurred while creating the record. Please try again later.";
}

// Close the prepared statement
mysqli_stmt_close($stmt);

// Create an SQL query
$sql = "SELECT id, name, email, age FROM users";
$result = mysqli_query($conn, $sql);

// Check if there are any results
if (mysqli_num_rows($result) > 0) {
    // Fetch and output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

<?php
// Assuming you already have a connection established in $conn

$newAge = 32;
$email = 'person1@example.com';

// Prepare an SQL statement
$stmt = mysqli_prepare($conn, "UPDATE users SET age=$newAge WHERE email=$email");

if ($stmt) {
    // Bind parameters to the prepared statement
    mysqli_stmt_bind_param($stmt, "is", $newAge, $email);

    // Execute the prepared statement
    if (mysqli_stmt_execute($stmt)) {
        echo "Record updated successfully";
    } else {
        // Log the error internally, do not display it to the user
        error_log("Error executing statement: " . mysqli_stmt_error($stmt));
        echo "An error occurred while updating the record. Please try again later.";
    }

    // Close the statement
    mysqli_stmt_close($stmt);
} else {
    // Log the error internally, do not display it to the user
    error_log("Error preparing statement: " . mysqli_error($conn));
    echo "An error occurred. Please try again later.";
}

// Close the connection
mysqli_close($conn);
?>

<?php

$email = 'person3@example.com';

// Prepare an SQL statement
$stmt = mysqli_prepare($conn, "DELETE FROM users WHERE email=$email");

if ($stmt) {
    // Bind parameter to the prepared statement
    mysqli_stmt_bind_param($stmt, "s", $email);

    // Execute the prepared statement
    if (mysqli_stmt_execute($stmt)) {
        // Verify if any records were deleted using mysqli_stmt_affected_rows
        if (mysqli_stmt_affected_rows($stmt) > 0) {
            echo "Record deleted successfully";
        } else {
            echo "No record found with the specified email.";
        }
    } else {
        // Log the error internally, do not display it to the user
        error_log("Error executing statement: " . mysqli_stmt_error($stmt));
        echo "An error occurred while deleting the record. Please try again later.";
    }

    // Close the statement
    mysqli_stmt_close($stmt);
} else {
    // Log the error internally, do not display it to the user
    error_log("Error preparing statement: " . mysqli_error($conn));
    echo "An error occurred. Please try again later.";
}

// Close the connection
mysqli_close($conn);
?>