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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Before proceeding, ensure that you have:<\/p>\n\n\n\n
demo<\/code>.<\/li>\n\n\n\n- MySQLi support enabled in PHP.<\/li>\n<\/ol>\n\n\n\n
Implementation<\/h2>\n\n\n\nStep 1: Create the Product Table<\/h3>\n\n\n\n
Create a table named product<\/code> using the following SQL query:<\/p>\n\n\n\nCREATE TABLE `product` (\n `pid` int(11) NOT NULL AUTO_INCREMENT,\n `pname` varchar(100) NOT NULL,\n `price` int(11) NOT NULL,\n `pimg` varchar(100) NOT NULL,\n `cat_id` int(11) NOT NULL,\n PRIMARY KEY (`pid`)\n) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;<\/pre>\n\n\n\nThe table contains product information such as the product ID, name, price, image, and category ID.<\/p>\n\n\n\n
Step 2: Create the HTML Edit Form<\/h3>\n\n\n\n
Create an edit form to display and update the product details.<\/p>\n\n\n\n
First, define the database connection details and retrieve the product record:<\/p>\n\n\n\n
define(\"DB_HOST\", \"localhost\");\ndefine(\"DB_USER\", \"root\");\ndefine(\"DB_PSSWD\", \"\");\ndefine(\"DB_NAME\", \"demo\");\n\n$id = 1;\n\n$sql = \"SELECT * FROM `product` WHERE pid=$id\";\n\n$result = mysqli_query($conn, $sql);\n\n\/\/ Associative array\n$row = mysqli_fetch_assoc($result);\n\n\/\/ print_r($row);<\/pre>\n\n\n\nCreate the HTML form:<\/p>\n\n\n\n
<form method=\"POST\" action=\"\" id=\"editproduct-form\">\n\n <label for=\"cat-id\">Select Category<\/label>\n <input name=\"cat_id\" id=\"cat-id\" value=\"\">\n\n <label for=\"product-name\">Product Name<\/label>\n <input\n id=\"product-name\"\n name=\"product_name\"\n placeholder=\"Enter Category\"\n value=\"\"\n type=\"text\"\n >\n\n <label for=\"product-price\">Product Price<\/label>\n <input\n id=\"product-price\"\n name=\"product_price\"\n placeholder=\"Enter Price\"\n value=\"\"\n type=\"text\"\n >\n\n <input type=\"hidden\" name=\"id\" id=\"id\" value=\"\">\n\n <button\n type=\"submit\"\n name=\"save\"\n class=\"btn btn-success\"\n value=\"save\"\n id=\"add-save\"\n >\n Save\n <\/button>\n\n<\/form><\/pre>\n\n\n\nThe hidden id<\/code> field is used to identify the product record that needs to be updated.<\/p>\n\n\n\nStep 3: Create the Update Function<\/h3>\n\n\n\n
Create a custom function named qry_update()<\/code> to perform the update operation:<\/p>\n\n\n\nfunction qry_update($table = '', $data = '', $where = '')\n{\n define(\"DB_HOST\", \"localhost\");\n define(\"DB_USER\", \"root\");\n define(\"DB_PSSWD\", \"\");\n define(\"DB_NAME\", \"demo\");\n\n $conn = mysqli_connect(\n DB_HOST,\n DB_USER,\n DB_PSSWD,\n DB_NAME\n );\n\n $cols = array();\n\n foreach ($data as $key => $val) {\n $cols[] = \"$key = '$val'\";\n }\n\n $query = \"UPDATE $table SET \" . implode(', ', $cols);\n\n if (!empty($where)) {\n foreach ($where as $key => $value) {\n $where_array[] = $key . ' = \"' . $value . '\"';\n }\n\n $query .= \" WHERE \" . implode(' AND ', $where_array);\n }\n\n \/\/ echo $query;\n\n $result = mysqli_query($conn, $query);\n\n if ($result) {\n echo 1;\n } else {\n echo 0;\n }\n}<\/pre>\n\n\n\nThe function accepts three parameters:<\/p>\n\n\n\n
\n$table<\/code> \u2013 The name of the database table.<\/li>\n\n\n\n$data<\/code> \u2013 The column names and values that need to be updated.<\/li>\n\n\n\n$where<\/code> \u2013 The condition used to identify the record to update.<\/li>\n<\/ul>\n\n\n\nStep 4: Process the Form Submission<\/h3>\n\n\n\n
When the user submits the form, collect the submitted values and pass them to the update function:<\/p>\n\n\n\n
if (isset($_POST['save'])) {\n\n $data = array(\n 'cat_id' => $_POST['cat_id'],\n 'pname' => $_POST['product_name'],\n 'price' => $_POST['product_price']\n );\n\n $where = array(\n 'pid' => $_POST['id']\n );\n\n $qry = qry_update('product', $data, $where);\n}<\/pre>\n\n\n\nHere, the $data<\/code> array contains the values that need to be updated, while the $where<\/code> array identifies the product record using its pid<\/code>.<\/p>\n\n\n\nThe function is then called as:<\/p>\n\n\n\n
qry_update('product', $data, $where);<\/pre>\n\n\n\nThis generates and executes the corresponding SQL UPDATE<\/code> query.<\/p>\n\n\n\nHow the Update Process Works<\/h2>\n\n\n\n
The overall process can be summarized as follows:<\/p>\n\n\n\n
\n- Retrieve the existing product record from the database.<\/li>\n\n\n\n
- Display the product details in an HTML form.<\/li>\n\n\n\n
- Submit the updated values through the form.<\/li>\n\n\n\n
- Create a
$data<\/code> array containing the updated values.<\/li>\n\n\n\n- Create a
$where<\/code> array containing the product ID.<\/li>\n\n\n\n- Pass the table name, data, and condition to the custom update function.<\/li>\n\n\n\n
- Execute the MySQL
UPDATE<\/code> query.<\/li>\n\n\n\n- Return the result of the update operation.<\/li>\n<\/ol>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
A custom PHP function can simplify repetitive database update operations by allowing the table name, update values, and conditions to be passed as parameters.<\/p>\n\n\n\n
In this example, we created a product table, prepared an edit form, created a custom qry_update()<\/code> function, and used the function to update product information in the MySQL database.<\/p>\n\n\n\nFrequently Asked Questions<\/h2>\n\n\n\n1. What is an UPDATE query in MySQL?<\/h3>\n\n\n\n
An UPDATE<\/code> query is used to modify existing records in a database table.<\/p>\n\n\n\nFor example:<\/p>\n\n\n\n
UPDATE product\nSET pname = 'Product Name', price = 100\nWHERE pid = 1;<\/pre>\n\n\n\n2. Why use a custom update function in PHP?<\/h3>\n\n\n\n
A custom function allows the update logic to be reused for different tables and records instead of writing the complete update query repeatedly.<\/p>\n\n\n\n
3. What does the $where<\/code> parameter do?<\/h3>\n\n\n\nThe $where<\/code> parameter specifies which database record should be updated. In this example, the product ID is used:<\/p>\n\n\n\n$where = array(\n 'pid' => $_POST['id']\n);<\/pre>\n\n\n\n4. Which PHP extension is used in this example?<\/h3>\n\n\n\n
The example uses the MySQLi<\/strong> extension to establish the database connection and execute the query.<\/p>\n\n\n\n5. Is this approach suitable for production applications?<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
Related Articles<\/h2>\n\n\n\n