mtb_crm_asp/get_product_info.php

<?php
require_once 'conn.php';
checkLogin();

header('Content-Type: application/json');

// Product search functionality
if (isset($_GET['search'])) {
    $search = mysqli_real_escape_string($conn, $_GET['search']);
    
    // Limit to 30 results for better performance in real-time searching
    // Search in ProductName, note, and tips fields, and include category information
    $sql = "SELECT p.id, p.ProductName, pc.name as category_name 
            FROM products p 
            LEFT JOIN product_categories pc ON p.category_id = pc.id
            WHERE p.ProductName LIKE '%$search%' 
               OR p.note LIKE '%$search%'
               OR p.tips LIKE '%$search%'
            ORDER BY 
                CASE 
                    WHEN p.ProductName LIKE '$search%' THEN 1  /* Exact start match first */
                    WHEN p.ProductName LIKE '%$search%' THEN 2 /* Contains match in name */
                    WHEN p.note LIKE '%$search%' THEN 3        /* Contains match in note */
                    WHEN p.tips LIKE '%$search%' THEN 4        /* Contains match in tips */
                END,
                p.ProductName 
            LIMIT 30";
    
    $result = mysqli_query($conn, $sql);
    
    $products = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $products[] = $row;
    }
    
    echo json_encode(['products' => $products]);
    exit;
}

// Get product specifications
if (isset($_GET['product_id'])) {
    $productId = (int)$_GET['product_id'];
    
    // Get product base information
    $productSql = "SELECT id, ProductName, unit FROM products WHERE id = $productId";
    $productResult = mysqli_query($conn, $productSql);
    $product = mysqli_fetch_assoc($productResult);
    
    if (!$product) {
        echo json_encode(['error' => 'Product not found']);
        exit;
    }
    
    // Get specifications for the product
    $specSql = "SELECT id, spec_name, spec_value, price, min_order_quantity, spec_code 
                FROM product_specifications 
                WHERE product_id = $productId 
                ORDER BY sort_order, spec_name";
    $specResult = mysqli_query($conn, $specSql);
    
    $specifications = [];
    while ($row = mysqli_fetch_assoc($specResult)) {
        $specifications[] = $row;
    }
    
    echo json_encode([
        'product' => $product,
        'specifications' => $specifications
    ]);
    exit;
}

// Original get product info functionality
$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

// Modified to include specification information
$sql = "SELECT p.unit 
        FROM products p 
        WHERE p.id = $id";

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

if ($row = mysqli_fetch_assoc($result)) {
    // Get specifications
    $specSql = "SELECT id, spec_name, spec_value, price, min_order_quantity, spec_code 
                FROM product_specifications 
                WHERE product_id = $id 
                ORDER BY sort_order, spec_name";
    $specResult = mysqli_query($conn, $specSql);
    
    $specifications = [];
    while ($spec = mysqli_fetch_assoc($specResult)) {
        $specifications[] = $spec;
    }
    
    echo json_encode([
        'unit' => $row['unit'],
        'specifications' => $specifications
    ]);
} else {
    echo json_encode(['error' => 'Product not found']);
}
?>