Based on the code provided, the issue with the product search not showing results in the Sales and Report section appears to be related to the SQL query construction. Here's the fix:
The main issue is in the SQL query where it's not properly handling the search term and date filtering conditions. The query needs to be adjusted to correctly filter products.
Here's the corrected SQL query section:
// Replace the SQL query section with this corrected version
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
oi.quantity AS quantity_sold,
o.updated_at AS date_sold
FROM
{$product_table_name} p
JOIN
{$order_item_table_name} oi ON p.id = oi.product_id
JOIN
{$order_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Prepare parameter
$params = ['Paid'];
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range conditions
if (!empty($start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
}
if (!empty($end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
}
// Add sorting
$base_sql .= " ORDER BY o.updated_at DESC";
// Final SQL query
$prepared_sql = $wpdb->prepare($base_sql, ...$params);
$sales_result = $wpdb->get_results($prepared_sql, ARRAY_A_
// Replace the SQL query section with this corrected version
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
oi.quantity AS quantity_sold,
o.updated_at AS date_sold
FROM
{$product_table_name} p
JOIN
{$order_item_table_name} oi ON p.id = oi.product_id
JOIN
{$order_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Prepare parameter
$params = ['Paid'];
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range conditions
if (!empty($start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
}
if (!empty($end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
}
// Add sorting
$base_sql .= " ORDER BY o.updated_at DESC";
// Final SQL query
$prepared_sql = $wpdb->prepare($base_sql, ...$params);
$sales_result = $wpdb->get_results($prepared_sql, ARRAY_A_
Key changes made:
1. Fixed the SQL query structure to properly handle search terms and date filtering
2. Simplified the date comparison by using DATE() function
3. Ensured proper parameter binding for security
4. Maintained all existing functionality while fixing the search issue
5. Kept the same variable names and structure to maintain compatibility with the rest of the code
Key changes made:
1. Fixed the SQL query structure to properly handle search terms and date filtering
2. Simplified the date comparison by using DATE() function
3. Ensured proper parameter binding for security
4. Maintained all existing functionality while fixing the search issue
5. Kept the same variable names and structure to maintain compatibility with the rest of the code
Apologies for the confusion. The sl (forward slashes) in the code are not meant to be changed. The forward slashes in the SQL query are part of the standard SQL syntax and should remain as they are.
The key changes I made were:
The forward slashes in the SQL query (like in JOIN clauses) should remain as they are - they are valid SQL syntax. The only slashes that needed removal were those that were incorrectly placed in the original query structure.
The corrected version maintains all the proper SQL syntax while fixing the search functionality. No valid SQL slashes were removed - only those that were causing syntax errors in the original query._
Let me analyze the search issue and provide a comprehensive fix. The problem appears to be in the SQL query construction and data handling. Here's the corrected version:
// --- Fetch Sales Data ---
$sales_data = [];
$total_revenue = 0;
$total_profit = 0;
$fetch_error = '';
// Ensure required tables exist before querying
$products_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $product_table_name)) == $product_table_name;
$orders_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $orders_table_name)) == $orders_table_name;
$order_items_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $order_items_table_name)) == $order_items_table_name;
if ($products_exists && $orders_exists && $order_items_exists) {
// Get filter values from URL query parameters
$search_term = isset($_GET['product_search']) ? sanitize_text_field(wp_unslash($_GET['product_search'])) : '';
$start_date = isset($_GET['start_date']) ? sanitize_text_field(wp_unslash($_GET['start_date'])) : '';
$end_date = isset($_GET['end_date']) ? sanitize_text_field(wp_unslash($_GET['end_date'])) : '';
// Initialize SQL query with proper table joins
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
SUM(oi.quantity) AS quantity_sold,
MAX(o.updated_at) AS date_sold
FROM
{$product_table_name} p
INNER JOIN
{$order_items_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$orders_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Prepare parameters
$params = ['Paid'];
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range conditions
if (!empty($start_date)) {
if (preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
} else {
$fetch_error .= " Invalid start date format.";
$start_date = '';
}
}
if (!empty($end_date)) {
if (preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
} else {
$fetch_error .= " Invalid end date format.";
$end_date = '';
}
}
// Group by product and order by most recent sale
$base_sql .= " GROUP BY p.id, p.name, p.category, p.unit, p.selling_price, p.buying_price";
$base_sql .= " ORDER BY date_sold DESC";
// Final SQL query
$prepared_sql = $wpdb->prepare($base_sql, ...$params);
$sales_results = $wpdb->get_results($prepared_sql, ARRAY_A);
// Debugging: Uncomment to see the actual query being executed
// error_log("Executing query: " . $prepared_sql);
if ($sales_results === null) {
$fetch_error = "Error fetching sales data: " . $wpdb->last_error;
} elseif (!empty($sales_results)) {
foreach ($sales_results as $row) {
// Calculate Revenue and Profit
$current_revenue = $row['quantity_sold'] * $row['selling_price'];
$cost_of_goods = $row['quantity_sold'] * $row['buying_price'];
$profit = $current_revenue - $cost_of_goods;
// Format the sold date
$date_sold = new DateTime($row['date_sold']);
$formatted_date = $date_sold->format('M j, Y g:i A');
$sales_data[] = [
'id' => $row['id'],
'product_name' => $row['product_name'],
'date_sold' => $formatted_date,
'category' => $row['category'],
'unit' => $row['unit'],
'unit_price' => $row['selling_price'],
'quantity_sold' => $row['quantity_sold'],
'product_revenue' => $current_revenue,
'product_profit' => $profit,
];
// Accumulate totals
$total_revenue += $current_revenue;
$total_profit += $profit;
}
}
} else {
$fetch_error = "Error: One or more required tables (products, orders, order_items) not found.";
}
// --- Fetch Sales Data ---
$sales_data = [];
$total_revenue = 0;
$total_profit = 0;
$fetch_error = '';
// Ensure required tables exist before querying
$products_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $product_table_name)) == $product_table_name;
$orders_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $orders_table_name)) == $orders_table_name;
$order_items_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $order_items_table_name)) == $order_items_table_name;
if ($products_exists && $orders_exists && $order_items_exists) {
// Get filter values from URL query parameters
$search_term = isset($_GET['product_search']) ? sanitize_text_field(wp_unslash($_GET['product_search'])) : '';
$start_date = isset($_GET['start_date']) ? sanitize_text_field(wp_unslash($_GET['start_date'])) : '';
$end_date = isset($_GET['end_date']) ? sanitize_text_field(wp_unslash($_GET['end_date'])) : '';
// Initialize SQL query with proper table joins
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
SUM(oi.quantity) AS quantity_sold,
MAX(o.updated_at) AS date_sold
FROM
{$product_table_name} p
INNER JOIN
{$order_items_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$orders_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Prepare parameters
$params = ['Paid'];
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range conditions
if (!empty($start_date)) {
if (preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
} else {
$fetch_error .= " Invalid start date format.";
$start_date = '';
}
}
if (!empty($end_date)) {
if (preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
} else {
$fetch_error .= " Invalid end date format.";
$end_date = '';
}
}
// Group by product and order by most recent sale
$base_sql .= " GROUP BY p.id, p.name, p.category, p.unit, p.selling_price, p.buying_price";
$base_sql .= " ORDER BY date_sold DESC";
// Final SQL query
$prepared_sql = $wpdb->prepare($base_sql, ...$params);
$sales_results = $wpdb->get_results($prepared_sql, ARRAY_A);
// Debugging: Uncomment to see the actual query being executed
// error_log("Executing query: " . $prepared_sql);
if ($sales_results === null) {
$fetch_error = "Error fetching sales data: " . $wpdb->last_error;
} elseif (!empty($sales_results)) {
foreach ($sales_results as $row) {
// Calculate Revenue and Profit
$current_revenue = $row['quantity_sold'] * $row['selling_price'];
$cost_of_goods = $row['quantity_sold'] * $row['buying_price'];
$profit = $current_revenue - $cost_of_goods;
// Format the sold date
$date_sold = new DateTime($row['date_sold']);
$formatted_date = $date_sold->format('M j, Y g:i A');
$sales_data[] = [
'id' => $row['id'],
'product_name' => $row['product_name'],
'date_sold' => $formatted_date,
'category' => $row['category'],
'unit' => $row['unit'],
'unit_price' => $row['selling_price'],
'quantity_sold' => $row['quantity_sold'],
'product_revenue' => $current_revenue,
'product_profit' => $profit,
];
// Accumulate totals
$total_revenue += $current_revenue;
$total_profit += $profit;
}
}
} else {
$fetch_error = "Error: One or more required tables (products, orders, order_items) not found.";
}
Key improvements made:
The main issues that were causing the search to not work were:
This revised version should now correctly show products when searched, while maintaining all the original functionality._
The main reason you can't search for products in your Sales Report is due to incorrect SQL query construction and data aggregation issues. Here are the specific problems:
Your original query doesn't group results by product, so it's trying to show individual order items rather than aggregated product sales.
You're using simple JOINs which might exclude products that exist but haven't been sold yet. For a sales report, you should use INNER JOIN to only show products with actual sales.
The search condition is added correctly, but without proper grouping, it won't work as expected.
Your original date filters include time components which might exclude valid matches.
// Replace your SQL query with this version:
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
SUM(oi.quantity) AS quantity_sold, // Sum all sales quantities
MAX(o.updated_at) AS date_sold // Get most recent sale date
FROM
{$product_table_name} p
INNER JOIN
{$order_items_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$orders_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Then add this after your search/date conditions:
$base_sql .= " GROUP BY p.id"; // Critical - groups by product ID
// Keep your existing search/date filtering code
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Replace your SQL query with this version:
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
SUM(oi.quantity) AS quantity_sold, // Sum all sales quantities
MAX(o.updated_at) AS date_sold // Get most recent sale date
FROM
{$product_table_name} p
INNER JOIN
{$order_items_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$orders_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Then add this after your search/date conditions:
$base_sql .= " GROUP BY p.id"; // Critical - groups by product ID
// Keep your existing search/date filtering code
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
GROUP BY p.id ensures each product appears only once in resultsSUM(oi.quantity) gives you total sales per productMAX(o.updated_at) shows the most recent sale dateINNER JOIN guarantees you only see products with actual saleserror_log("Executed query: " . $prepared_sql);
error_log("Executed query: " . $prepared_sql);
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders WHERE status = 'Paid';
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders WHERE status = 'Paid';
error_log("Search term received: " . $search_term);
error_log("Search term received: " . $search_term);
This solution will make your product search work while maintaining all other functionality. The key was properly aggregating the sales data before applying search filters._
Got it, you want to show individual sales records, not grouped by product. Here's the corrected version:
// Replace your SQL query with this version:
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
oi.quantity AS quantity_sold, // Individual quantity per sale
o.updated_at AS date_sold // Individual sale date
FROM
{$product_table_name} p
INNER JOIN
{$order_item_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$order_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range condition if provided
if (!empty($start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
}
if (!empty($end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
}
// Add sorting
$base_sql .= " ORDER BY o.updated_at DESC";
// Replace your SQL query with this version:
$base_sql = "SELECT
p.id,
p.name AS product_name,
p.category,
p.unit,
p.selling_price,
p.buying_price,
oi.quantity AS quantity_sold, // Individual quantity per sale
o.updated_at AS date_sold // Individual sale date
FROM
{$product_table_name} p
INNER JOIN
{$order_item_table_name} oi ON p.id = oi.product_id
INNER JOIN
{$order_table_name} o ON oi.order_id = o.id
WHERE o.status = %s";
// Add search condition if search term is present
if (!empty($search_term)) {
$search_like = '%' . $wpdb->esc_like($search_term) . '%';
$base_sql .= " AND (p.name LIKE %s OR p.category LIKE %s)";
$params[] = $search_like;
$params[] = $search_like;
}
// Add date range condition if provided
if (!empty($start_date)) {
$base_sql .= " AND DATE(o.updated_at) >= %s";
$params[] = $start_date;
}
if (!empty($end_date)) {
$base_sql .= " AND DATE(o.updated_at) <= %s";
$params[] = $end_date;
}
// Add sorting
$base_sql .= " ORDER BY o.updated_at DESC";
Key changes:
GROUP BY clause to show individual salesSUM(oi.quantity) to oi.quantity to show individual quantitiesThis will now show each individual sale record with:
The search should now work as expected, showing all individual sales records that match your search criteria._