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;
}

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

$sql = "SELECT p.unit, pr.price 
        FROM products p 
        LEFT JOIN price pr ON p.id = pr.productId AND pr.AreaId = 0 
        WHERE p.id = $id ORDER BY pr.num ASC LIMIT 1";

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

if ($row = mysqli_fetch_assoc($result)) {
    echo json_encode([
        'unit' => $row['unit'],
        'price' => $row['price'] ?? '0.00'
    ]);
} else {
    echo json_encode(['error' => 'Product not found']);
}
?>