How to Update Multiple MySQL Rows from an HTML Form (Using WordPress WPDB)
As a WordPress developer, you often need to interact with your site's database to store and retrieve data. One common scenario is updating multiple rows in a MySQL table based on user input from an HTML form. This can be a challenging task, especially when dealing with dynamic data structures.
In this article, we'll explore a solution using the WordPress WPDB (database abstraction) class, which simplifies the process of updating multiple rows in a MySQL table directly from an HTML form.
Understanding the Problem
Imagine you have a WordPress website that allows users to manage product inventory. You have a MySQL table called products
with columns for shipid
, product_sku
, and product_quantity
. Users need to be able to update the quantity of multiple products at once, directly from an HTML form.
Here's an example of the HTML form:
<form method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
<table class="cktable" >
<tr>
<th>ShipID:</th>
<th>Product SKU:</th>
<th>Quantity:</th>
</tr>
<tr>
<td><input type="text" name="shipid[]" /></td>
<td><input type="text" name="product_sku[]" /></td>
<td><input type="text" name="product_quantity[]" /></td>
</tr>
<tr>
<td><input type="text" name="shipid[]" /></td>
<td><input type="text" name="product_sku[]" /></td>
<td><input type="text" name="product_quantity[]" /></td>
</tr>
</table>
<input type='submit' name="update" value='Update'>
</form>
The form allows users to enter multiple shipid
, product_sku
, and product_quantity
values. When the form is submitted, we need to update the corresponding rows in the products
table.
Updating Multiple Rows Using WPDB
To update multiple rows in the products
table, we'll use the WordPress WPDB class, which provides a simple and secure way to interact with the database.
Here's the code to handle the form submission and update the database:
if (isset($_POST['update'])) {
global $wpdb;
$table_name = 'products'; // Replace with your table name
$shipids = $_POST['shipid'];
$product_skus = $_POST['product_sku'];
$product_quantitys = $_POST['product_quantity'];
$count = count($_POST['shipid']);
for ($i = 0; $i < $count; $i++) {
$shipid = $wpdb->prepare('%s', $shipids[$i]);
$product_sku = $wpdb->prepare('%s', $product_skus[$i]);
$product_quantity = $wpdb->prepare('%s', $product_quantitys[$i]);
$wpdb->update(
$table_name,
array(
'product_quantity' => $product_quantity,
),
array(
'shipid' => $shipid,
'product_sku' => $product_sku,
),
array(
'%s',
),
array(
'%s',
'%s',
)
);
}
}
Let's break down the code:
- We first check if the
update
button was clicked on the form.
- We then get the global
$wpdb
object, which provides the WordPress database abstraction layer.
- We retrieve the values from the form fields (
$shipids
, $product_skus
, and $product_quantitys
).
- We loop through the form fields and use the
$wpdb->update()
method to update the corresponding rows in the products
table.
- The first argument is the table name.
- The second argument is an associative array of the column names and their new values.
- The third argument is an associative array of the column names and their values for the
WHERE
clause.
- The fourth argument is an array of data types for the values in the second argument.
- The fifth argument is an array of data types for the values in the third argument.
By using the $wpdb->prepare()
method, we ensure that the input values are properly escaped and sanitized, protecting against SQL injection attacks.
Handling Errors and Validation
It's important to add error handling and validation to your code to ensure the integrity of your database. Here's an example of how you can modify the code to handle errors and validate the input:
if (isset($_POST['update'])) {
global $wpdb;
$table_name = 'products'; // Replace with your table name
$shipids = $_POST['shipid'];
$product_skus = $_POST['product_sku'];
$product_quantitys = $_POST['product_quantity'];
$count = count($_POST['shipid']);
for ($i = 0; $i < $count; $i++) {
$shipid = $wpdb->prepare('%s', $shipids[$i]);
$product_sku = $wpdb->prepare('%s', $product_skus[$i]);
$product_quantity = $wpdb->prepare('%s', $product_quantitys[$i]);
// Validate input
if (!is_numeric($product_quantity) || $product_quantity < 0) {
$error_message = "Invalid quantity value for product SKU: " . $product_sku;
// Display the error message or handle it in another way
break;
}
$result = $wpdb->update(
$table_name,
array(
'product_quantity' => $product_quantity,
),
array(
'shipid' => $shipid,
'product_sku' => $product_sku,
),
array(
'%s',
),
array(
'%s',
'%s',
)
);
if ($result === false) {
$error_message = "Error updating product SKU: " . $product_sku;
// Display the error message or handle it in another way
break;
}
}
if (!isset($error_message)) {
// Display a success message or redirect the user
echo "Product quantities updated successfully.";
}
}
In this modified code, we:
- Validate the
$product_quantity
value to ensure it's a positive numeric value.
- Check the return value of the
$wpdb->update()
method, which will be false
if the update operation fails.
- If there are no errors, we display a success message or redirect the user to another page.
By adding these error handling and validation checks, you can ensure that your database updates are reliable and secure.
Conclusion
In this article, we've learned how to update multiple rows in a MySQL table directly from an HTML form using the WordPress WPDB class. This approach simplifies the process of managing and updating database records, and it also ensures the integrity of your data by handling errors and validating user input.
Remember, the techniques demonstrated in this article can be applied to various WordPress projects that require efficient database management. By understanding how to work with the WPDB class, you can build more robust and reliable WordPress applications.
Flowpoint.ai can help you identify all the technical errors that are impacting conversion rates on your website and directly generate recommendations to fix them
Get a Free AI Website Audit
Automatically identify UX and content issues affecting your conversion rates with Flowpoint's comprehensive AI-driven website audit.