How to Perform Simple CRUD with PHP and MySQL

Q: What is CRUD and why is it important in web development?

CRUD means Create, Read, Update, and Delete, which are fundamental database operations. These operations are important for dynamic web applications because they allow users to interact with and manipulate data efficiently.

Q: How can I perform CRUD operations with PHP and MySQL?

You can perform CRUD operations in PHP and MySQL using various methods, including single query execution, multiple query execution, prepared statements, and fetching data from the database.

Q: What are prepared statements, and why are they recommended for CRUD operations?

Prepared statements are SQL statements that are precompiled and stored in the database. By using prepared statements to execute the same query with different parameters efficiently, SQL injection attacks can be avoided, and performance can be improved.

Q: How can I recover data from the MySQL database using PHP?

You can recover data from a MySQL database using PHP by writing a select query and executing it using functions like mysqli_query() or mysqli_fetch_assoc(). Prepared statements can also be used for secure and efficient data retrieval.

CRUD operations are required in dynamic web applications to allow for smooth data manipulation and database interactions. PHP and MySQL are formidable duos for creating dynamic and interactive web pages among the many programming languages and database management systems available.

This blog delves into the complexities of performing basic CRUD operations on PHP and MySQL. Whether you're a beginner looking to solidify your understanding of web development concepts or an experienced programmer looking to refresh your knowledge, this guide will provide you with the necessary skills to navigate the complexities of database interaction.

How do you create a MySQL Database Connection?

Before making a connection, you should collect the following information:

Database Host: The hostname or IP address of the MySQL server (commonly "localhost").
Database Name: The name of the MySQL database you want to connect to.
Database Username: The username used to access the database.
Database Password: The password associated with the username.

The following code links the PHP code to the database where the web page's data will be stored.

<;?php
// db_connection.php
// Function to open database connection
function OpenCon()
{
    $dbhost = "localhost";
    $dbuser = "username"; // Replace with your MySQL username
    $dbpass = "password"; // Replace with your MySQL password
    $dbname = "database_name"; // Replace with your MySQL database name
    $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die("Connect failed: %s\n". $conn -> error);
    return $conn;
}
// Function to close database connection
function CloseCon($conn)
{
    $conn -> close();
}

Now you have links the database with PHP code. You can perform queries in this database file.

Single Query Execution

The focus here is on running a single query at a time. We'll insert data into our previously established database using PHP MySQL hosting. To start, create a new file named crud.php in PHP's "practice" directory. The central component of our database interactions is CRUD, which stands for Create, Read, Update, and Delete.

Include the line require_once 'db_connection.php' at the beginning of this new file and define the function as follows:

require_once 'db_connection.php';
function SingleQuery($query)
{
  $connection = OpenCon();
  
  if($connection->query($query) === TRUE)
  {
    CloseCon($connection);
    return true;
  }
  else
  {
    return $connection->error;
  }

This function only requires your selected query as a parameter to function properly. Now, inside the same directory, create another file called index2.php. At the top of this file, include require_once 'crud.php'. Then, incorporate the following lines into index2.php:

include 'crud.php';
$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('w','w','t@gmail.com','First Insert Using Single Query')";
$result = SingleQuery($sql);
if($result === true)
{
  echo 'success';
}
else
{
  echo $result;
}

Open your web browser and fire the command localhost/practice/index2.php. If your query is properly formatted, you should receive a success message.

Multi Queries Execution

This technique allows you to run multiple insert, select, or update queries at the same time. Let's create a function that can handle multiple insert or update queries. This new function will be added to your crud.php file:

function MultiQuery($queries)
{
  $connection = OpenCon();
  
  if($connection->multi_query($queries) === true)
  {
    CloseCon($connection);
    return true;
  }
  else
  {
    return $connection->error;
  }
}

This function takes a single parameter containing your desired queries and executes them.

Now, add this function to your index2.php:

include 'crud.php';
$sql = "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('a','a','a@gmail.com','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('b','b','b@gmail.com','First Insert Using Multiple Queries');";
$sql .= "INSERT INTO myguests(firstname,lastname,email,subject) VALUES ('c','c','c@gmail.com','First Insert Using Multiple Queries');";
$result = MultiQuery($sql);
if($result === true)
{
 echo 'success';
}
else
{
 echo $result;
}

After you've done this, open your browser and go to localhost/practice/index2.php. If your query is properly formatted, you should receive a success message.

CRUD in PHP and MySQL with Prepared Statements

In our CRUD (Create, Read, Update, Delete) PHP file, we're adding a new function called PreQuery. This function will assist us in efficiently executing the same database query multiple times. Let's go through it step-by-step.

function PreQuery($fname, $lname, $email, $subj) {
  // Opening connection to the database
  $conn = OpenCon();
  
  // Preparing the SQL statement with placeholders
  $query = $conn->prepare("INSERT INTO myguests(firstname, lastname, email, subject) VALUES (?,?,?,?)");
  
  // Binding parameters to the placeholders
  $query->bind_param("ssss", $fname, $lname, $email, $subj);
  
  // Executing the query
  if($query->execute()) {
    // Closing the database connection
    CloseCon($conn);
    return true; // Indicating success
  } else {
    return $conn->error; // Returning error message if execution fails
  }
}

Understanding Prepared Statements

Preparation: You write your SQL statement with placeholders (?) where you want to insert values.

Binding: You associate values with the placeholders. Additionally, you specify the data type for each value (s for string, i for integer, d for double, b for blob).

Execution: The statement is executed.

In our function PreQuery, we take four parameters ($fname, $lname, $email, $subj) representing the values we want to insert into the database. We then execute the query using these values.

Call the function

include 'crud.php';

// Setting values to be inserted
$firstn = "Hello";
$lastn = "Elightwalk";
$email = "hello@elightwalk.com";
$subject = "Inserting Data using prepared Query";

// Calling the PreQuery function with provided values
$result = PreQuery($firstn, $lastn, $email, $subject);

// Checking if the query executed successfully
if($result === true) {
  echo 'success'; // Printing success message if query is successful
} else {
  echo $result; // Printing error message if query fails
}

Now, Open your browser and go to localhost/practice/index2.php. If everything is correct, you should see "success" indicating that the query was executed successfully. Otherwise, an error message will be displayed.

Select Query Execution

By combining the execution process into a single function. We can speed up the process of retrieving data from our MySQL database with a select query. Here's an updated version of the crud.php script that contains the selectdata() function:

<?php
// crud.php

// Function to execute select queries
function selectdata($sql_query)
{
    // Establish connection
    $connection = OpenCon();
    
    // Execute the query
    $query_result = $connection->query($sql_query);
    
    // Check if query execution was successful
    if ($query_result) {
        // Check if any rows were returned
        if ($query_result->num_rows > 0) {
            // Return the result set
            return $query_result;
        } else {
            // Return a message indicating zero results found
            return "Zero results found";
        }
    } else {
        // Return the error message if query execution failed
        return $connection->error;
    }
}
?>

The selectdata() function in the revised script is in charge of carrying out select queries. It connects to the MySQL database using the OpenCon() function. Then, it executes the provided SQL query and handles various scenarios accordingly.

If the query executes successfully, it checks whether any rows were returned. If there are any rows, it returns the result set.
If no rows were returned, the message "zero results found" is returned.
If the query execution fails, it returns the error message.

Now, let us add this method to the index2.php script:

<?php
// index2.php

// Include the CRUD functions
include 'crud.php';

// SQL query to select data
$sql_query = "SELECT * FROM `myguests`";

// Call the selectdata function to execute the query
$query_result = selectdata($sql_query);

// Display the results in a table
?>
<table>
    <tr>
        <td>Name</td>
        <td>Email</td>
        <td>Message</td>
    </tr>
    <?php
    // Check if there are results
    if ($query_result != "Zero results found") {
        // Loop through the result set and display data
        while ($row = $query_result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>" . $row['firstname'] . ' ' . $row['lastname'] . "</td>";
            echo "<td>" . $row['email'] . "</td>";
            echo "<td>" . $row['subject'] . "</td>";
            echo "</tr>";
        }
    } else {
        // Display the message if no results found
        echo "<tr><td colspan='3'>$query_result</td></tr>";
    }
    ?>
</table>

The selectdata() function in index2.php is used to run the SQL query that will retrieve data from the database table myguests. The results are then displayed in an HTML table. The appropriate message is displayed within the table if no results are found. At last, in the browser, go to localhost/practice/index2.php to view the database data.

Use Prepared Statement to Update Query

Create a new function in your crud.php file to process update statements.

function UpdateQuery($column, $value, $id)
{
    $conn = OpenCon();
    $query = $conn->prepare("UPDATE myguests SET $column = ? WHERE id = ?");
    $query->bind_param("si", $value, $id);
    if ($query->execute()) {
        CloseCon($conn);
        return true;
    } else {
        return $conn->error;
    }
}

The UpdateQuery function requires three parameters: the column name to be updated, the new value to replace the existing one, and the ID of the row to be modified. Use this function in index2.php as follows:

include 'crud.php';
$result = UpdateQuery("firstname", "David", 1);
if ($result === true) {
    echo 'success';
} else {
    echo $result;
}

Once you've finished these steps, go to localhost/practice/index2.php in your browser. If there are no mistakes, the operation should succeed.

Delete a query using a prepared statement

Create a new function in your crud.php file to perform the delete statement:

function DeleteQuery($id)
{
    $conn = OpenCon();
    $query = $conn->prepare("DELETE FROM myguests WHERE id = ?");
    $query->bind_param("i", $id);
    
    if ($query->execute()) {
        CloseCon($conn);
        return true;
    } else {
        return $conn->error;
    }
}

The DeleteQuery function requires one parameter: the ID of the row to be deleted from the table. Use this function in index2.php as follows:

include 'crud.php';
$result = DeleteQuery(1);
if ($result === true) {
    echo 'success';
} else {
    echo $result;
}

Once you've executed these steps, go to localhost/practice/index2.php in your browser. If no errors occur, you should see a success message.

Also Read: 4 Easy Ways to Check If an Array Is Empty in PHP

Essence

Here is a summary of the major topics this MySQL guide covers.

An overview of PHP and MySQL CRUD operations.
A detailed explanation of three different methods for executing CRUD operations.
Details on MySQL data fetch clauses and their importance in database management.

We learned about three methods for performing CRUD operations in PHP and MySQL. The guide also covers the importance of data fetch clauses in MySQL for effective database administration. Following these steps, you can successfully perform CRUD operations in PHP and MySQL. We hope this tutorial helps you better understand MySQL and PHP integration for database management.

Elightwalk's Laravel developers are experts in efficiently implementing CRUD operations with PHP and MySQL. They can provide expert advice and support for your database management requirements. Contact us for more information on how our team can assist you with your PHP and MySQL projects.

Table of contents

FAQs about CURD operation

What is CRUD and why is it important in web development?

How can I perform CRUD operations with PHP and MySQL?

What are prepared statements, and why are they recommended for CRUD operations?

How can I recover data from the MySQL database using PHP?

Jayram Prajapati

Full Stack Developer

Jayram Prajapati brings expertise and innovation to every project he takes on. His collaborative communication style, coupled with a receptiveness to new ideas, consistently leads to successful project outcomes.

Most Visited Blog

Blog

Custom Route in Venia: Enhancing Your Storefront

Discover the potential of unique routes in Venia for a personalized retail experience. Improve Magento PWA capabilities by following our simple approach to customizing routes for maximum performance and user engagement.

Blog

How to delete all products and categories in Magento

Manage your Magento database with a SQL script to delete all categories and products. Prioritise backups, test in a controlled environment and follow best practices for responsible and secure usage.

Blog

How to add order list numbers without "ol" and "ul" elements by CSS for all browsers?

Optimise your order list without utilising 'ol' and 'ul' elements! Discover CSS strategies for adding list numbers across all browsers, ensuring that ordered content is presented and visually appealingly.