How to implement a product SKU system with multiple specifications in PHP

How to implement a product SKU system with multiple specifications in PHP

In the field of e-commerce, the SKU (Stock Keeping Unit) of a product is a unique identifier Characters are used to distinguish products of different specifications and attributes. In some scenarios, a product may have multiple specifications, such as size, color, capacity, etc. Implementing a product SKU system with multiple specifications is very common in the development of e-commerce platforms. This article will introduce how to use PHP to implement this function.

First, we need to define a product data table, which stores basic information about the product, such as product ID, product name, product description, etc. Next, we need to define a specification data table, which stores all possible specification options, such as size, color, capacity, etc.

Example of product data table:

CREATE TABLE products (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT
);

Example of specification data table:

CREATE TABLE attributes (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    options TEXT
);

Next, we need to define an association table to store the relationship between products and specifications. The association table will record the specification options included in each product, as well as the unique SKU code associated with the product.

Example of associated table:

CREATE TABLE product_attributes (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    product_id INT(11),
    attribute_id INT(11),
    option_id INT(11),
    sku VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT(11),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (attribute_id) REFERENCES attributes(id)
);

In PHP code, we can write a method to obtain all specification options of the product, for example:

function getAttributes($productId) {
    // 查询商品对应的规格选项
    $query = "SELECT a.id, a.name, a.options
              FROM attributes a
              INNER JOIN product_attributes pa ON a.id = pa.attribute_id
              WHERE pa.product_id = $productId";

    // 执行查询并返回结果
    $result = mysqli_query($conn, $query);
    $attributes = array();

    while ($row = mysqli_fetch_assoc($result)) {
        $optionIds = explode(',', $row['options']);
        $options = getOptions($optionIds);
        
        $attribute = array(
            'id' => $row['id'],
            'name' => $row['name'],
            'options' => $options
        );

        $attributes[] = $attribute;
    }

    return $attributes;
}

Next, we can Write a method to get all the specific values ​​​​of a certain specification option, for example:

function getOptions($optionIds) {
    // 查询规格选项对应的数值
    $optionIds = implode(',', $optionIds);
    $query = "SELECT id, name, value
              FROM options
              WHERE id IN ($optionIds)";

    // 执行查询并返回结果
    $result = mysqli_query($conn, $query);
    $options = array();

    while ($row = mysqli_fetch_assoc($result)) {
        $option = array(
            'id' => $row['id'],
            'name' => $row['name'],
            'value' => $row['value']
        );

        $options[] = $option;
    }

    return $options;
}

Finally, we can write a method to get all SKUs of the product, for example:

function getSkus($productId) {
    // 查询商品对应的所有SKU
    $query = "SELECT sku, price, stock
              FROM product_attributes
              WHERE product_id = $productId";

    // 执行查询并返回结果
    $result = mysqli_query($conn, $query);
    $skus = array();

    while ($row = mysqli_fetch_assoc($result)) {
        $sku = array(
            'sku' => $row['sku'],
            'price' => $row['price'],
            'stock' => $row['stock']
        );

        $skus[] = $sku;
    }

    return $skus;
}

Pass With the above code example, we can implement a product SKU system with multiple specifications in PHP. Using this system, we can easily manage products of different specifications and obtain corresponding SKU information based on specific specification options. This is very useful for the development of e-commerce platforms, as it allows users to select product specifications and generate corresponding SKU codes.

The above is the detailed content of How to implement a product SKU system with multiple specifications in PHP. For more information, please follow other related articles on the PHP Chinese website!