Detailed explanation of techniques and methods for implementing multi-specification SKU of products using PHP

Detailed explanation of the techniques and methods of implementing multi-specification SKU of products in PHP

In e-commerce websites, multi-specification SKU (Stock Keeping Unit) of products is a common method Sales management methods. By setting different specification attributes, such as size, color, style, etc., consumers can be provided with more choices and merchants can easily manage inventory and sales. This article will introduce a technique and method to use PHP to implement multi-specification SKU of products, and give code examples.

First of all, we need to design a database table structure to store product specification information. A common table structure design is to use three tables: product table, specification table and SKU table. The product table stores the basic information of the product, the specifications table stores the attributes of the specifications, and the SKU table stores information such as inventory and price of specific product specifications. The following is a simplified table structure example:

Product table (product):

• id (product ID)
• name (product name)

Specification table (specification):

• id (specification ID)
• name (specification name)

SKU table (sku) :

• id (SKU ID)
• product_id (product ID)
• specification_id (specification ID)
• value (specification value)
• stock(stock)
• price(price)

Next, we will write PHP code for this table structure. The first is a code example to query all specification attributes and values ​​of a product:

<?php
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// 查询商品规格
$query = "SELECT * FROM product";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    $product_id = $row['id'];
    $product_name = $row['name'];

    echo "商品ID：$product_id<br>";
    echo "商品名称：$product_name<br>";

    // 查询商品规格属性
    $query_spec = "SELECT specification.id, specification.name FROM specification
                   LEFT JOIN sku ON sku.specification_id = specification.id
                   WHERE sku.product_id = $product_id AND sku.stock > 0
                   GROUP BY specification.id";
    $result_spec = mysqli_query($conn, $query_spec);

    while ($row_spec = mysqli_fetch_assoc($result_spec)) {
        $specification_id = $row_spec['id'];
        $specification_name = $row_spec['name'];

        echo "规格属性ID：$specification_id<br>";
        echo "规格属性名称：$specification_name<br>";

        // 查询商品规格值
        $query_value = "SELECT sku.value FROM sku
                        WHERE sku.product_id = $product_id AND sku.specification_id = $specification_id
                        AND sku.stock > 0";
        $result_value = mysqli_query($conn, $query_value);

        while ($row_value = mysqli_fetch_assoc($result_value)) {
            $specification_value = $row_value['value'];

            echo "规格属性值：$specification_value<br>";
        }
    }
}

// 关闭数据库连接
mysqli_close($conn);
?>

The above code will query the database multiple times to obtain the specification attributes and values ​​of the product, and then output it in the form of HTML. In practical applications, we can modify it as needed, such as storing it in an array to facilitate subsequent processing.

The following is a code example to query the corresponding SKU information based on the specifications selected by the user:

<?php
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// 根据选择的规格查询SKU信息
$selected_specifications = $_POST['specifications']; // 假设用户选择的规格为一个数组，如array('颜色' => '红色', '尺寸' => 'XL')

$query_sku = "SELECT * FROM sku WHERE product_id = $product_id";
foreach ($selected_specifications as $specification => $value) {
    $query_sku .= " AND specification_id IN
                   (SELECT sku.specification_id FROM sku
                   WHERE sku.value = '$value')";
}

$result_sku = mysqli_query($conn, $query_sku);

while ($row_sku = mysqli_fetch_assoc($result_sku)) {
    $sku_id = $row_sku['id'];
    $sku_stock = $row_sku['stock'];
    $sku_price = $row_sku['price'];

    echo "SKU ID：$sku_id<br>";
    echo "库存：$sku_stock<br>";
    echo "价格：$sku_price<br>";
}

// 关闭数据库连接
mysqli_close($conn);
?>

The above code dynamically constructs a query statement based on the specification attributes and values ​​selected by the user, and queries the corresponding SKU information and output to the page.

In this way, we can achieve flexible multi-specification SKU management and query functions. Of course, for better user experience and effects, some interaction and optimization can be performed on the front end, such as using AJAX to dynamically update SKU information, etc. I hope this article has provided some tips and methods for using PHP to implement multi-specification SKUs for products.

Reference materials:

• [PHP MySQL database connection](https://www.php.net/manual/en/mysqli.quickstart.connections.php)
• [PHP MySQL query statement](https://www.w3schools.com/php/php_mysql_select.asp)

The above is the detailed content of Detailed explanation of techniques and methods for implementing multi-specification SKU of products using PHP. For more information, please follow other related articles on the PHP Chinese website!