PHP单循环实现特色商品优先显示的技术问询

Absolutely! You can replace those nested loops with a single database query and loop, which is both cleaner and more efficient (it cuts down from N+1 database calls to just one). Here's how to do it:

The Core Idea #

Instead of fetching tariff data first and then querying products one by one, we'll join the two tables in a single SQL query. We'll also add a calculated flag to identify featured items for page ID 74, then order our results so featured items come first, followed by non-featured items sorted by price ascending.

Revised Code #

// Prepare a joined query with featured flag and proper ordering
$sql = $db_con->prepare("
    SELECT t.*, p.*,
           CASE WHEN STRPOS(p.featured_in, '74') IS NOT NULL THEN 1 ELSE 0 END AS is_featured
    FROM `panel_tariff` t
    INNER JOIN `panel_product` p ON t.post_id = p.id
    ORDER BY is_featured DESC, t.price ASC
");

$sql->execute();
$products = $sql->fetchAll(PDO::FETCH_ASSOC);

if (count($products) > 0) {
    // Single loop to display all items (featured first)
    foreach ($products as $product) {
        // Access tariff data via $product['price'], $product['post_id'], etc.
        // Access product data via $product['featured_in'], $product['id'], etc.
        
        // Your display logic here - no need to check featured status anymore since they're ordered
        echo "<div class='product'>";
        echo "Price: " . $product['price'];
        echo "Name: " . $product['product_name']; // Replace with actual product field
        echo "</div>";
    }
}

Key Improvements: #

• Single Database Call: Joining tables eliminates the need for nested queries, which is much faster especially with large datasets.
• Automatic Ordering: The ORDER BY is_featured DESC ensures featured items (where page ID 74 is present in featured_in) appear first, followed by non-featured items sorted by price ascending.
• Cleaner Code: No more nested loops or variable juggling between queries.

Notes: #

• If your featured_in field stores page IDs as comma-separated values (e.g., "74,85"), use FIND_IN_SET('74', p.featured_in) instead of STRPOS to avoid false matches (like detecting "174" as containing "74"). The CASE statement would become:

  CASE WHEN FIND_IN_SET('74', p.featured_in) THEN 1 ELSE 0 END AS is_featured
  

• I noticed a possible typo in your original code: you had if (strpos(...) === false) with a comment "Show featured items"—that condition would actually show non-featured items. The revised query fixes this by ordering featured items first automatically.

内容的提问来源于stack exchange，提问作者Qarar Ul Hassan