Get Started With CRUD Operations in PHP MySQL Databases

This tutorial demonstrates basic CRUD (Create, Read, Update, Delete) operations using PHP and MySQL. It's a great starting point for PHP developers learning database connectivity, a crucial skill for any web application. We'll focus on core mysqli functions for simplicity.

Database connectivity is fundamental for most web applications, as databases store application data. This guide assumes a working PHP and MySQL installation with the mysqli extension enabled. You can verify this using phpinfo() (look for the "mysqli" section) or the command-line tool: php -m. The output should list mysqli if it's enabled.

Let's cover:

• Establishing a database connection
• Selecting a database
• Inserting and updating records
• Retrieving records
• Deleting records

Database Connection:

We'll use the procedural approach with the mysqli_connect function for clarity. The function takes four arguments:

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
var_dump($connection_obj);

• {MYSQL_HOSTNAME}: MySQL server hostname or IP (e.g., localhost, 127.0.0.1).
• {MYSQL_USERNAME}: MySQL username (often root).
• {MYSQL_PASSWORD}: MySQL password (often blank initially).
• {MYSQL_DATABASE}: The database name.

A successful connection returns a connection object. Error handling is essential:

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}

Selecting a Database:

While you can specify the database in mysqli_connect, mysqli_select_db allows switching databases after connection:

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}");
if (!$connection_obj) { /* error handling as above */ }
mysqli_select_db($connection_obj, "{MYSQL_DATABASE}");

mysqli_select_db takes the connection object and the database name as arguments.

Creating and Updating Records:

Create a MySQL table (e.g., using phpMyAdmin):

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Insertion:

$name = 'John Smith';
$email = 'john.smith@yahoo.com';
$phone = '541-754-1234';

$query = "INSERT INTO employee(`name`,`email`, `phone`)
VALUES ('" . mysqli_real_escape_string($connection_obj, $name) . "','" . mysqli_real_escape_string($connection_obj, $email) . "','" . mysqli_real_escape_string($connection_obj, $phone) . "')";

mysqli_query($connection_obj, $query);

Crucially, mysqli_real_escape_string prevents SQL injection vulnerabilities.

Updating:

$id = 1;
$phone = '333-555-4444';

$query = "UPDATE employee SET `phone` = '" . mysqli_real_escape_string($connection_obj, $phone) . "' WHERE `id` = '" . (int)$id . "'";
mysqli_query($connection_obj, $query);

Retrieving Records:

$query = "SELECT * FROM employee";
$result = mysqli_query($connection_obj, $query) or die(mysqli_error($connection_obj));

while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    echo "ID:" . $row['id'] . "<br>";
    echo "Name:" . $row['name'] . "<br>";
    echo "Phone:" . $row['phone'] . "<br>";
    echo "Email:" . $row['email'] . "<br><br>";
}

mysqli_fetch_array retrieves rows; MYSQLI_BOTH allows accessing columns by name or index. mysqli_fetch_assoc provides associative arrays only.

Deleting Records:

$id = 1;
$query = "DELETE FROM employee WHERE `id` = '" . (int)$id . "'";
mysqli_query($connection_obj, $query);

Remember to close the connection using mysqli_close($connection_obj); after all operations. This example provides a foundation for more complex database interactions in PHP. Remember to always sanitize user inputs to prevent SQL injection.

(Note: The image URLs are placeholders from the original input. Replace them with actual image URLs if needed.)

The above is the detailed content of Get Started With CRUD Operations in PHP MySQL Databases. For more information, please follow other related articles on the PHP Chinese website!