Here are the key changes I made to fix the order display issue:
Fixed Syntax Errors:
$_POST['field']] to $_POST['field'])Improved Order Query:
WHERE o.status = 'Pending'Restructured Order Display Logic:
Added Empty State Handling:
Fixed Redirects:
The main issue was that the original code wasn't properly filtering for pending orders and had some syntax errors that prevented the orders from displaying. The new version:
These changes make the order display more reliable while maintaining all the original functionality.
Let me fix the issue where added products aren't showing in the Order List despite being in the database. Here's the corrected version:
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
$amount = $unit_price * $quantity;
// Create order
$wpdb->insert($table_name_orders, [
'customer_name' => 'Default Customer',
'total_amount' => $amount,
'status' => 'Pending',
'created_at' => current_time('mysql')
]);
$order_id = $wpdb->insert_id;
// Add order item
$wpdb->insert($table_name_order_items, [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $amount
]);
// Update stock
$wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id]
);
// Set success message
$_SESSION['order_message'] = 'Order added successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} else {
$message = 'Insufficient stock!';
}
} else {
$message = 'Product not found!';
}
}
}
// --- Handle Payment ---
if (isset($_POST['pay_orders'])) {
$wpdb->query("UPDATE $table_name_orders SET status = 'Paid' WHERE status = 'Pending'");
$_SESSION['order_message'] = 'Orders paid successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
}
// --- Handle Cancellation ---
if (isset($_POST['cancel_order'])) {
$order_id = absint($_POST['order_id']);
if ($order_id) {
// Restore stock first
$order_item = $wpdb->get_row($wpdb->prepare(
"SELECT oi.*, p.in_stock
FROM $table_name_order_items oi
JOIN $table_name_products p ON oi.product_id = p.id
WHERE oi.order_id = %d",
$order_id
));
if ($order_item) {
$wpdb->update(
$table_name_products,
['in_stock' => $order_item->in_stock + $order_item->quantity],
['id' => $order_item->product_id]
);
}
// Delete order
$wpdb->delete($table_name_orders, ['id' => $order_id]);
$wpdb->delete($table_name_order_items, ['order_id' => $order_id]);
$_SESSION['order_message'] = 'Order cancelled!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT o.*, oi.*, p.name AS product_name
FROM $table_name_orders o
JOIN $table_name_order_items oi ON o.id = oi.order_id
JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC
");
// Calculate total
$total_amount = 0;
foreach ($orders as $order) {
$total_amount += ($order->quantity * $order->unit_price);
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
?>
<!-- HTML remains the same until the orders display section -->
<tbody>
<?php if (!empty($orders)): ?>
<?php foreach ($orders as $order): ?>
<tr>
<td><?= esc_html($order->id) ?></td>
<td><?= esc_html($order->product_name) ?></td>
<td><?= esc_html($order->quantity) ?></td>
<td><?= esc_html($order->unit) ?></td>
<td><?= number_format($order->unit_price, 2) ?></td>
<td><?= number_format($order->quantity * $order->unit_price, 2) ?></td>
<td><?= esc_html($order->status) ?></td>
<td>
<form method="post">
<input type="hidden" name="order_id" value="<?= $order->id ?>">
<button type="submit" name="cancel_order"
onclick="return confirm('Cancel this order?')">
Cancel
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan="5" class="text-right"><strong>Total:</strong></td>
<td colspan="3"><?= number_format($total_amount, 2) ?></td>
</tr>
<?php else: ?>
<tr>
<td colspan="8" class="text-center">No pending orders</td>
</tr>
<?php endif; ?>
</tbody>
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
$amount = $unit_price * $quantity;
// Create order
$wpdb->insert($table_name_orders, [
'customer_name' => 'Default Customer',
'total_amount' => $amount,
'status' => 'Pending',
'created_at' => current_time('mysql')
]);
$order_id = $wpdb->insert_id;
// Add order item
$wpdb->insert($table_name_order_items, [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $amount
]);
// Update stock
$wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id]
);
// Set success message
$_SESSION['order_message'] = 'Order added successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} else {
$message = 'Insufficient stock!';
}
} else {
$message = 'Product not found!';
}
}
}
// --- Handle Payment ---
if (isset($_POST['pay_orders'])) {
$wpdb->query("UPDATE $table_name_orders SET status = 'Paid' WHERE status = 'Pending'");
$_SESSION['order_message'] = 'Orders paid successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
}
// --- Handle Cancellation ---
if (isset($_POST['cancel_order'])) {
$order_id = absint($_POST['order_id']);
if ($order_id) {
// Restore stock first
$order_item = $wpdb->get_row($wpdb->prepare(
"SELECT oi.*, p.in_stock
FROM $table_name_order_items oi
JOIN $table_name_products p ON oi.product_id = p.id
WHERE oi.order_id = %d",
$order_id
));
if ($order_item) {
$wpdb->update(
$table_name_products,
['in_stock' => $order_item->in_stock + $order_item->quantity],
['id' => $order_item->product_id]
);
}
// Delete order
$wpdb->delete($table_name_orders, ['id' => $order_id]);
$wpdb->delete($table_name_order_items, ['order_id' => $order_id]);
$_SESSION['order_message'] = 'Order cancelled!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT o.*, oi.*, p.name AS product_name
FROM $table_name_orders o
JOIN $table_name_order_items oi ON o.id = oi.order_id
JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC
");
// Calculate total
$total_amount = 0;
foreach ($orders as $order) {
$total_amount += ($order->quantity * $order->unit_price);
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
?>
<!-- HTML remains the same until the orders display section -->
<tbody>
<?php if (!empty($orders)): ?>
<?php foreach ($orders as $order): ?>
<tr>
<td><?= esc_html($order->id) ?></td>
<td><?= esc_html($order->product_name) ?></td>
<td><?= esc_html($order->quantity) ?></td>
<td><?= esc_html($order->unit) ?></td>
<td><?= number_format($order->unit_price, 2) ?></td>
<td><?= number_format($order->quantity * $order->unit_price, 2) ?></td>
<td><?= esc_html($order->status) ?></td>
<td>
<form method="post">
<input type="hidden" name="order_id" value="<?= $order->id ?>">
<button type="submit" name="cancel_order"
onclick="return confirm('Cancel this order?')">
Cancel
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan="5" class="text-right"><strong>Total:</strong></td>
<td colspan="3"><?= number_format($total_amount, 2) ?></td>
</tr>
<?php else: ?>
<tr>
<td colspan="8" class="text-center">No pending orders</td>
</tr>
<?php endif; ?>
</tbody>
Key fixes made:
Session Management:
Database Query Fixes:
$order['field']) to object syntax ($order->field)ORDER BY created_at DESC to show newest orders firstDisplay Improvements:
Redirect Handling:
Error Handling:
The main issue was likely due to:
This version should now properly:
I understand the issue now - orders are being created in the orders table but not properly linked in the order_items table. Let's fix this by ensuring the order items are correctly inserted and displayed.
Here's the corrected version:
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product with proper error handling
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
// Start transaction for atomic operations
$wpdb->query('START TRANSACTION');
try {
// Create order
$order_inserted = $wpdb->insert($table_name_orders, [
'customer_name' => 'Default Customer',
'total_amount' => $unit_price * $quantity,
'status' => 'Pending',
'created_at' => current_time('mysql')
]);
if (!$order_inserted) {
throw new Exception('Failed to create order');
}
$order_id = $wpdb->insert_id;
// Add order item with explicit error checking
$item_inserted = $wpdb->insert($table_name_order_items, [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
]);
if (!$item_inserted) {
throw new Exception('Failed to create order item');
}
// Update stock
$stock_updated = $wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id]
);
if ($stock_updated === false) {
throw new Exception('Failed to update stock');
}
// Commit transaction if all operations succeeded
$wpdb->query('COMMIT');
$_SESSION['order_message'] = 'Order added successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} catch (Exception $e) {
// Rollback on any error
$wpdb->query('ROLLBACK');
$message = 'Error: ' . $e->getMessage();
}
} else {
$message = 'Insufficient stock!';
}
} else {
$message = 'Product not found!';
}
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT
o.id AS order_id,
o.customer_name,
o.status,
o.created_at AS order_date,
oi.id AS item_id,
oi.quantity,
oi.unit,
oi.unit_price,
oi.amount,
p.id AS product_id,
p.name AS product_name
FROM $table_name_orders o
INNER JOIN $table_name_order_items oi ON o.id = oi.order_id
INNER JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC, oi.id ASC
");
// Calculate total
$total_amount = 0;
if ($orders) {
foreach ($orders as $order) {
$total_amount += $order->amount;
}
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
?>
<!-- HTML remains the same until the orders display section -->
<tbody>
<?php if (!empty($orders)): ?>
<?php foreach ($orders as $order): ?>
<tr>
<td><?= esc_html($order->order_id) ?></td>
<td><?= esc_html($order->product_name) ?></td>
<td><?= esc_html($order->quantity) ?></td>
<td><?= esc_html($order->unit) ?></td>
<td><?= number_format($order->unit_price, 2) ?></td>
<td><?= number_format($order->amount, 2) ?></td>
<td><?= esc_html($order->status) ?></td>
<td>
<form method="post">
<input type="hidden" name="order_id" value="<?= $order->order_id ?>">
<button type="submit" name="cancel_order"
onclick="return confirm('Cancel this order?')">
Cancel
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan="5" class="text-right"><strong>Total:</strong></td>
<td colspan="3"><?= number_format($total_amount, 2) ?></td>
</tr>
<?php else: ?>
<tr>
<td colspan="8" class="text-center">No pending orders</td>
</tr>
<?php endif; ?>
</tbody>
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product with proper error handling
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
// Start transaction for atomic operations
$wpdb->query('START TRANSACTION');
try {
// Create order
$order_inserted = $wpdb->insert($table_name_orders, [
'customer_name' => 'Default Customer',
'total_amount' => $unit_price * $quantity,
'status' => 'Pending',
'created_at' => current_time('mysql')
]);
if (!$order_inserted) {
throw new Exception('Failed to create order');
}
$order_id = $wpdb->insert_id;
// Add order item with explicit error checking
$item_inserted = $wpdb->insert($table_name_order_items, [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
]);
if (!$item_inserted) {
throw new Exception('Failed to create order item');
}
// Update stock
$stock_updated = $wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id]
);
if ($stock_updated === false) {
throw new Exception('Failed to update stock');
}
// Commit transaction if all operations succeeded
$wpdb->query('COMMIT');
$_SESSION['order_message'] = 'Order added successfully!';
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} catch (Exception $e) {
// Rollback on any error
$wpdb->query('ROLLBACK');
$message = 'Error: ' . $e->getMessage();
}
} else {
$message = 'Insufficient stock!';
}
} else {
$message = 'Product not found!';
}
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT
o.id AS order_id,
o.customer_name,
o.status,
o.created_at AS order_date,
oi.id AS item_id,
oi.quantity,
oi.unit,
oi.unit_price,
oi.amount,
p.id AS product_id,
p.name AS product_name
FROM $table_name_orders o
INNER JOIN $table_name_order_items oi ON o.id = oi.order_id
INNER JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC, oi.id ASC
");
// Calculate total
$total_amount = 0;
if ($orders) {
foreach ($orders as $order) {
$total_amount += $order->amount;
}
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
?>
<!-- HTML remains the same until the orders display section -->
<tbody>
<?php if (!empty($orders)): ?>
<?php foreach ($orders as $order): ?>
<tr>
<td><?= esc_html($order->order_id) ?></td>
<td><?= esc_html($order->product_name) ?></td>
<td><?= esc_html($order->quantity) ?></td>
<td><?= esc_html($order->unit) ?></td>
<td><?= number_format($order->unit_price, 2) ?></td>
<td><?= number_format($order->amount, 2) ?></td>
<td><?= esc_html($order->status) ?></td>
<td>
<form method="post">
<input type="hidden" name="order_id" value="<?= $order->order_id ?>">
<button type="submit" name="cancel_order"
onclick="return confirm('Cancel this order?')">
Cancel
</button>
</form>
</td>
</tr>
<?php endforeach; ?>
<tr>
<td colspan="5" class="text-right"><strong>Total:</strong></td>
<td colspan="3"><?= number_format($total_amount, 2) ?></td>
</tr>
<?php else: ?>
<tr>
<td colspan="8" class="text-center">No pending orders</td>
</tr>
<?php endif; ?>
</tbody>
Key improvements made:
Transaction Management:
START TRANSACTION, COMMIT, ROLLBACK) to ensure atomic operationsError Handling:
Database Query Fixes:
created_at timestamp to order_items tableData Integrity:
Display Improvements:
Common reasons why order items might not show up:
This version should now:
If you're still not seeing items, please check:
Let's fix the "Failed to create order item" error. This typically happens when there's a mismatch between the database schema and the data we're trying to insert. Here's the corrected version with detailed error handling:
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product with proper error handling
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
// Start transaction for atomic operations
$wpdb->query('START TRANSACTION');
try {
// 1. Create order
$order_data = [
'customer_name' => 'Default Customer',
'total_amount' => $unit_price * $quantity,
'status' => 'Pending',
'created_at' => current_time('mysql')
];
$order_inserted = $wpdb->insert($table_name_orders, $order_data);
if ($order_inserted === false) {
throw new Exception('Failed to create order: ' . $wpdb->last_error);
}
$order_id = $wpdb->insert_id;
// 2. Add order item with debug information
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
// Debug: Show what we're trying to insert
error_log('Attempting to insert order item: ' . print_r($item_data, true));
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
if ($item_inserted === false) {
throw new Exception('Failed to create order item: ' . $wpdb->last_error);
}
// 3. Update stock
$stock_updated = $wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id],
['%d'], // Data format
['%d'] // Where format
);
if ($stock_updated === false) {
throw new Exception('Failed to update stock: ' . $wpdb->last_error);
}
// Commit transaction if all operations succeeded
$wpdb->query('COMMIT');
$_SESSION['order_message'] = 'Order added successfully! Order ID: ' . $order_id;
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} catch (Exception $e) {
// Rollback on any error
$wpdb->query('ROLLBACK');
$message = $e->getMessage();
// Additional debug info
error_log('Order processing failed: ' . $e->getMessage());
error_log('Last query: ' . $wpdb->last_query);
error_log('Last error: ' . $wpdb->last_error);
}
} else {
$message = 'Insufficient stock! Available: ' . $product->in_stock . ', Requested: ' . $quantity;
}
} else {
$message = 'Product not found: ' . esc_html($product_name);
}
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT
o.id AS order_id,
o.customer_name,
o.status,
o.created_at AS order_date,
oi.id AS item_id,
oi.quantity,
oi.unit,
oi.unit_price,
oi.amount,
p.id AS product_id,
p.name AS product_name,
p.in_stock
FROM $table_name_orders o
INNER JOIN $table_name_order_items oi ON o.id = oi.order_id
INNER JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC, oi.id ASC
");
// Calculate total
$total_amount = 0;
if ($orders) {
foreach ($orders as $order) {
$total_amount += $order->amount;
}
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
<?php
global $wpdb;
// Define your tables
$table_name_categories = 'categories';
$table_name_products = 'products';
$table_name_orders = 'orders';
$table_name_order_items = 'order_items';
$message = '';
// Start session if not already started
if (!session_id()) {
session_start();
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// Get form values
$product_name = sanitize_text_field($_POST['product_name']);
$quantity = absint($_POST['quantity']);
$unit_price = floatval($_POST['unit_price']);
$unit = sanitize_text_field($_POST['unit']);
// Validate input
if (empty($product_name) || $quantity <= 0 || $unit_price <= 0 || empty($unit)) {
$message = 'Please fill all fields with valid values!';
} else {
// Retrieve product with proper error handling
$product = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM $table_name_products WHERE name = %s",
$product_name
));
if ($product) {
if ($quantity <= $product->in_stock) {
// Start transaction for atomic operations
$wpdb->query('START TRANSACTION');
try {
// 1. Create order
$order_data = [
'customer_name' => 'Default Customer',
'total_amount' => $unit_price * $quantity,
'status' => 'Pending',
'created_at' => current_time('mysql')
];
$order_inserted = $wpdb->insert($table_name_orders, $order_data);
if ($order_inserted === false) {
throw new Exception('Failed to create order: ' . $wpdb->last_error);
}
$order_id = $wpdb->insert_id;
// 2. Add order item with debug information
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit' => $unit,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
// Debug: Show what we're trying to insert
error_log('Attempting to insert order item: ' . print_r($item_data, true));
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
if ($item_inserted === false) {
throw new Exception('Failed to create order item: ' . $wpdb->last_error);
}
// 3. Update stock
$stock_updated = $wpdb->update(
$table_name_products,
['in_stock' => $product->in_stock - $quantity],
['id' => $product->id],
['%d'], // Data format
['%d'] // Where format
);
if ($stock_updated === false) {
throw new Exception('Failed to update stock: ' . $wpdb->last_error);
}
// Commit transaction if all operations succeeded
$wpdb->query('COMMIT');
$_SESSION['order_message'] = 'Order added successfully! Order ID: ' . $order_id;
wp_redirect($_SERVER['REQUEST_URI']);
exit;
} catch (Exception $e) {
// Rollback on any error
$wpdb->query('ROLLBACK');
$message = $e->getMessage();
// Additional debug info
error_log('Order processing failed: ' . $e->getMessage());
error_log('Last query: ' . $wpdb->last_query);
error_log('Last error: ' . $wpdb->last_error);
}
} else {
$message = 'Insufficient stock! Available: ' . $product->in_stock . ', Requested: ' . $quantity;
}
} else {
$message = 'Product not found: ' . esc_html($product_name);
}
}
}
// --- Fetch Orders for Display ---
$orders = $wpdb->get_results("
SELECT
o.id AS order_id,
o.customer_name,
o.status,
o.created_at AS order_date,
oi.id AS item_id,
oi.quantity,
oi.unit,
oi.unit_price,
oi.amount,
p.id AS product_id,
p.name AS product_name,
p.in_stock
FROM $table_name_orders o
INNER JOIN $table_name_order_items oi ON o.id = oi.order_id
INNER JOIN $table_name_products p ON oi.product_id = p.id
WHERE o.status = 'Pending'
ORDER BY o.created_at DESC, oi.id ASC
");
// Calculate total
$total_amount = 0;
if ($orders) {
foreach ($orders as $order) {
$total_amount += $order->amount;
}
}
// Display session message if exists
if (isset($_SESSION['order_message'])) {
$message = $_SESSION['order_message'];
unset($_SESSION['order_message']);
}
Key improvements for debugging:
Detailed Error Reporting:
$wpdb->last_error to all error messagesStrict Data Validation:
Enhanced Debugging:
Transaction Safety:
To diagnose the issue:
Check your PHP error logs for the debug output
Verify your order_items table structure matches these columns:
Check for:
The error logs should now show exactly why the order item insertion is failing. Common issues include:
Ah, this error indicates that your order_items table doesn't have a unit column. Let's fix this by either:
Here's the solution:
<?php
global $wpdb;
// First, let's check if the column exists and add it if needed
$column_check = $wpdb->get_row("
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{$wpdb->prefix}order_items'
AND COLUMN_NAME = 'unit'
");
if (!$column_check) {
$wpdb->query("ALTER TABLE {$wpdb->prefix}order_items ADD COLUMN unit VARCHAR(50) AFTER quantity");
}
// Rest of your code remains the same, but with better error handling for the unit field
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// ... [previous code]
try {
// ... [previous order creation code]
// Modified order item insertion with unit fallback
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
// Only add unit if the column exists
if ($column_check) {
$item_data['unit'] = $unit;
}
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
// ... [rest of your code]
<?php
global $wpdb;
// First, let's check if the column exists and add it if needed
$column_check = $wpdb->get_row("
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{$wpdb->prefix}order_items'
AND COLUMN_NAME = 'unit'
");
if (!$column_check) {
$wpdb->query("ALTER TABLE {$wpdb->prefix}order_items ADD COLUMN unit VARCHAR(50) AFTER quantity");
}
// Rest of your code remains the same, but with better error handling for the unit field
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// ... [previous code]
try {
// ... [previous order creation code]
// Modified order item insertion with unit fallback
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
// Only add unit if the column exists
if ($column_check) {
$item_data['unit'] = $unit;
}
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
// ... [rest of your code]
Alternatively, if you can't modify the table structure, remove all references to the 'unit' field:
// In your form:
// Remove the unit input field
// In your order item insertion:
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
// In your form:
// Remove the unit input field
// In your order item insertion:
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity,
'created_at' => current_time('mysql')
];
To implement this properly:
ALTER TABLE wp_order_items ADD COLUMN unit VARCHAR(50) AFTER quantity;
ALTER TABLE wp_order_items ADD COLUMN unit VARCHAR(50) AFTER quantity;
The error occurs because your code tries to insert into a column that doesn't exist in the database. Either solution above will resolve it - choose based on whether you want to:
Would you like me to provide the complete code for either approach?_
This error indicates your order_items table is missing the created_at column. Let's fix this by modifying the code to either:
Here's the complete fixed solution:
<?php
global $wpdb;
// Define your tables
$table_name_order_items = $wpdb->prefix . 'order_items';
// Check and add missing columns if needed
$columns = $wpdb->get_col("DESCRIBE $table_name_order_items");
$missing_columns = [];
if (!in_array('unit', $columns)) {
$missing_columns[] = 'ADD COLUMN unit VARCHAR(50) AFTER quantity';
}
if (!in_array('created_at', $columns)) {
$missing_columns[] = 'ADD COLUMN created_at DATETIME AFTER amount';
}
if (!empty($missing_columns)) {
$wpdb->query("ALTER TABLE $table_name_order_items " . implode(', ', $missing_columns));
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// [Previous form handling code...]
try {
// [Previous order creation code...]
// Prepare order item data
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity
];
// Only add these fields if they exist
if (in_array('unit', $columns)) {
$item_data['unit'] = $unit;
}
if (in_array('created_at', $columns)) {
$item_data['created_at'] = current_time('mysql');
}
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
if ($item_inserted === false) {
throw new Exception('Failed to create order item: ' . $wpdb->last_error);
}
// [Rest of your transaction code...]
} catch (Exception $e) {
$wpdb->query('ROLLBACK');
$message = $e->getMessage();
error_log('Order Error: ' . $e->getMessage());
}
}
// [Rest of your code...]
<?php
global $wpdb;
// Define your tables
$table_name_order_items = $wpdb->prefix . 'order_items';
// Check and add missing columns if needed
$columns = $wpdb->get_col("DESCRIBE $table_name_order_items");
$missing_columns = [];
if (!in_array('unit', $columns)) {
$missing_columns[] = 'ADD COLUMN unit VARCHAR(50) AFTER quantity';
}
if (!in_array('created_at', $columns)) {
$missing_columns[] = 'ADD COLUMN created_at DATETIME AFTER amount';
}
if (!empty($missing_columns)) {
$wpdb->query("ALTER TABLE $table_name_order_items " . implode(', ', $missing_columns));
}
// --- Handle Add Order Form Submission ---
if (isset($_POST['add_order']) && wp_verify_nonce($_POST['_wpnonce_order_form'], 'add_order_action')) {
// [Previous form handling code...]
try {
// [Previous order creation code...]
// Prepare order item data
$item_data = [
'order_id' => $order_id,
'product_id' => $product->id,
'quantity' => $quantity,
'unit_price' => $unit_price,
'amount' => $unit_price * $quantity
];
// Only add these fields if they exist
if (in_array('unit', $columns)) {
$item_data['unit'] = $unit;
}
if (in_array('created_at', $columns)) {
$item_data['created_at'] = current_time('mysql');
}
$item_inserted = $wpdb->insert($table_name_order_items, $item_data);
if ($item_inserted === false) {
throw new Exception('Failed to create order item: ' . $wpdb->last_error);
}
// [Rest of your transaction code...]
} catch (Exception $e) {
$wpdb->query('ROLLBACK');
$message = $e->getMessage();
error_log('Order Error: ' . $e->getMessage());
}
}
// [Rest of your code...]
Key changes made:
Automatic Schema Validation:
Conditional Field Insertion:
Better Error Handling:
Backward Compatibility:
To implement this:
The error should now be resolved. If you still encounter issues, please: