How to Update MySQL Records in PHP Using a Custom Function

Introduction

Updating existing records is a common requirement when working with PHP and MySQL databases. Instead of writing the same update logic repeatedly, you can create a custom PHP function that accepts the table, data, and conditions required for the update operation.

In this article, we will explain how to update a product record in a MySQL database using PHP and MySQLi. We will create a product table, display the existing product details in an edit form, and use a custom function to execute the update query.

Prerequisites

Before proceeding, ensure that you have:

1. A PHP-enabled web server.
2. MySQL or MariaDB installed and running.
3. Basic knowledge of PHP and MySQL.
4. A database named demo.
5. MySQLi support enabled in PHP.

Implementation

Step 1: Create the Product Table

Create a table named product using the following SQL query:

CREATE TABLE `product` (
    `pid` int(11) NOT NULL AUTO_INCREMENT,
    `pname` varchar(100) NOT NULL,
    `price` int(11) NOT NULL,
    `pimg` varchar(100) NOT NULL,
    `cat_id` int(11) NOT NULL,
    PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

The table contains product information such as the product ID, name, price, image, and category ID.

Step 2: Create the HTML Edit Form

Create an edit form to display and update the product details.

First, define the database connection details and retrieve the product record:

define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PSSWD", "");
define("DB_NAME", "demo");

$id = 1;

$sql = "SELECT * FROM `product` WHERE pid=$id";

$result = mysqli_query($conn, $sql);

// Associative array
$row = mysqli_fetch_assoc($result);

// print_r($row);

Create the HTML form:

<form method="POST" action="" id="editproduct-form">

    <label for="cat-id">Select Category</label>
    <input name="cat_id" id="cat-id" value="">

    <label for="product-name">Product Name</label>
    <input
        id="product-name"
        name="product_name"
        placeholder="Enter Category"
        value=""
        type="text"
    >

    <label for="product-price">Product Price</label>
    <input
        id="product-price"
        name="product_price"
        placeholder="Enter Price"
        value=""
        type="text"
    >

    <input type="hidden" name="id" id="id" value="">

    <button
        type="submit"
        name="save"
        class="btn btn-success"
        value="save"
        id="add-save"
    >
        Save
    </button>

</form>

The hidden id field is used to identify the product record that needs to be updated.

Step 3: Create the Update Function

Create a custom function named qry_update() to perform the update operation:

function qry_update($table = '', $data = '', $where = '')
{
    define("DB_HOST", "localhost");
    define("DB_USER", "root");
    define("DB_PSSWD", "");
    define("DB_NAME", "demo");

    $conn = mysqli_connect(
        DB_HOST,
        DB_USER,
        DB_PSSWD,
        DB_NAME
    );

    $cols = array();

    foreach ($data as $key => $val) {
        $cols[] = "$key = '$val'";
    }

    $query = "UPDATE $table SET " . implode(', ', $cols);

    if (!empty($where)) {
        foreach ($where as $key => $value) {
            $where_array[] = $key . ' = "' . $value . '"';
        }

        $query .= " WHERE " . implode(' AND ', $where_array);
    }

    // echo $query;

    $result = mysqli_query($conn, $query);

    if ($result) {
        echo 1;
    } else {
        echo 0;
    }
}

The function accepts three parameters:

• $table – The name of the database table.
• $data – The column names and values that need to be updated.
• $where – The condition used to identify the record to update.

Step 4: Process the Form Submission

When the user submits the form, collect the submitted values and pass them to the update function:

if (isset($_POST['save'])) {

    $data = array(
        'cat_id' => $_POST['cat_id'],
        'pname'  => $_POST['product_name'],
        'price'  => $_POST['product_price']
    );

    $where = array(
        'pid' => $_POST['id']
    );

    $qry = qry_update('product', $data, $where);
}

Here, the $data array contains the values that need to be updated, while the $where array identifies the product record using its pid.

The function is then called as:

qry_update('product', $data, $where);

This generates and executes the corresponding SQL UPDATE query.

How the Update Process Works

The overall process can be summarized as follows:

1. Retrieve the existing product record from the database.
2. Display the product details in an HTML form.
3. Submit the updated values through the form.
4. Create a $data array containing the updated values.
5. Create a $where array containing the product ID.
6. Pass the table name, data, and condition to the custom update function.
7. Execute the MySQL UPDATE query.
8. Return the result of the update operation.

Conclusion

A custom PHP function can simplify repetitive database update operations by allowing the table name, update values, and conditions to be passed as parameters.

In this example, we created a product table, prepared an edit form, created a custom qry_update() function, and used the function to update product information in the MySQL database.

Frequently Asked Questions

1. What is an UPDATE query in MySQL?

An UPDATE query is used to modify existing records in a database table.

For example:

UPDATE product
SET pname = 'Product Name', price = 100
WHERE pid = 1;

2. Why use a custom update function in PHP?

A custom function allows the update logic to be reused for different tables and records instead of writing the complete update query repeatedly.

3. What does the $where parameter do?

The $where parameter specifies which database record should be updated. In this example, the product ID is used:

$where = array(
    'pid' => $_POST['id']
);

4. Which PHP extension is used in this example?

The example uses the MySQLi extension to establish the database connection and execute the query.

5. Is this approach suitable for production applications?

The basic concept can be used, but the original implementation should be improved for production by using prepared statements, input validation, secure database credentials, and proper error handling.

Related Articles

• Dynamic Dependent drop down list using HTML, PHP, MySQL, and Ajax
• How to Change MySQL User Authentication Plugin for Password?

Connect with Our Technology Experts

Have a technology challenge or looking for the right solution for your business? Our team can help you with cloud, DevOps, development, infrastructure, design, and more. Feel free to reach out to our experts here.