Building a CRUD Application with PHP and MySQL

CRUD is an acronym for Create, Read, Update and Delete, and it is a common set of operations that are performed on databases. In this tutorial, we will be building a CRUD application using PHP and MySQL. By the end of this tutorial, you will have a basic understanding of how to create, read, update and delete records in a MySQL database using PHP.

Before you can start building the application, you will need to have a web server that supports PHP and a MySQL database set up. You will also need to have a basic understanding of SQL (Structured Query Language) to be able to interact with the database.

The first step in building a CRUD application is to connect to the MySQL database. This can be done using the PHP function mysql_connect(). The function takes three parameters: the server name, the username and the password. Once connected, you can select the database you want to work with using the mysql_select_db() function.

$db_host = "localhost";
$db_username = "root";
$db_password = "";
$db_name = "mydb";

$conn = mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $conn);

The next step is to create the table that we will be working with. This can be done using the SQL command "CREATE TABLE". For the purpose of this tutorial, we will create a table named "users" with three columns: "id", "username", and "email". The "id" column will be the primary key and will be set to auto-increment.

$sql = "CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
)";

mysql_query($sql, $conn);

Now that we have our table set up, we can start building the CRUD operations. The first operation we will look at is the "Create" operation, which is used to insert new records into the database. This can be done using the SQL command "INSERT INTO".

$username = "John";
$email = "john@example.com";


$sql = "INSERT INTO users (username, email) VALUES ('$username', '$email')";
mysql_query($sql, $conn);

The second operation is the "Read" operation, which is used to retrieve records from the database. This can be done using the SQL command "SELECT".

$sql = "SELECT * FROM users";
$result = mysql_query($sql, $conn);

while ($row = mysql_fetch_assoc($result)) {
    echo "Username: " . $row['username'] . " Email: " . $row['email'] . "<br>";
}

The third operation is the "Update" operation, which is used to update existing records in the database. This can be done using the SQL command "UPDATE".

$username = "Jane";
$email = "jane@example.com";
$id = 1;

$sql = "UPDATE users SET username='$username', email='$email' WHERE id=$id";
mysql_query($sql, $conn);

The fourth and final operation is the "Delete" operation, which is used to delete records from the database. This can be done using the SQL command "DELETE".

$id = 1;

$sql = "DELETE FROM users WHERE id=$id";
mysql_query($sql, $conn);

It's important to note that the above mysql_* functions are deprecated and it's better to use PDO or mysqli_* functions for database connection in php.

In conclusion, this tutorial has shown you how to build a basic CRUD application using PHP and MySQL. CRUD operations are the backbone of many database-driven applications, and understanding how to implement them is an essential skill for any PHP developer. Remember to keep practicing and experimenting with different SQL commands and PHP functions to improve your skills.

3.6K