How to implement database design for multi-specification SKU of products in PHP

How to implement database design for product multi-specification SKU in PHP

In e-commerce platforms, product specifications are a very important concept. Product specifications can be understood as the different attributes and characteristics of the product, such as size, color, weight, etc. In practical applications, for different specifications, we often need to set different prices, inventory, pictures and other information for each combination. This requires us to design a suitable database structure to store and manage product specifications and related information.

This article will introduce how to implement the database design of multiple product specifications SKU in PHP and provide code examples.

1. Data table design

In order to store product specifications and related information, we need to design two main data tables: product table and specification table.

Product table (products): stores basic information of products, such as product name, description, price, etc.

Specifications: Stores product specification information, such as size, color, weight, etc.

The following is sample code for the product table and specification table:

CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`price` decimal(10,2) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `specifications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the specification table, the product_id field is a foreign key that is related to ## in the product table The #id field realizes the relationship between products and specifications.

2. Data insertion and query

In order to demonstrate how to insert and query product specification data, we assume that there is a product named "Mobile Phone", which has two specifications: size and color.

First, we need to insert the basic information of the product:

INSERT INTO `products` (`name`, `description`, `price`, `created_at`, `updated_at`)
VALUES ('手机', '这是一款智能手机', 1999.00, NOW(), NOW());

Then, we insert the specification information of the product:

INSERT INTO `specifications` (`product_id`, `name`, `value`)
VALUES (1, '尺寸', '6寸');

INSERT INTO `specifications` (`product_id`, `name`, `value`)
VALUES (1, '颜色', '黑色');

INSERT INTO `specifications` (`product_id`, `name`, `value`)
VALUES (1, '颜色', '白色');

With the above code, we successfully insert it into the database Get the specification information of mobile phone products.

In order to query product specification information, we can use SQL statements and PHP code to achieve this. The following is a simple query example:

SELECT p.name, p.price, s.name, s.value
FROM products p
JOIN specifications s ON p.id = s.product_id
WHERE p.id = 1;

Through the above query, we can get the product name, price and corresponding specification name and value.

3. Dynamic display of product specifications

In practical applications, we usually need to dynamically display product specifications and related price, inventory and other information. To achieve this we can do it using PHP and JavaScript.

In the front-end page, we can use controls such as drop-down boxes, radio boxes or check boxes to display product specification options. When the user selects different specification options, we can send a request to the backend service through an Ajax asynchronous request, query the corresponding price and inventory information from the database, and display it in real time.

The following is a simple JavaScript sample code that implements the dynamic display of product specification options:

$(document).ready(function() {
    $('#specification-select').on('change', function() {
        var productId = $(this).val();
        
        $.ajax({
            url: 'getProductInfo.php',
            type: 'POST',
            data: {productId: productId},
            success: function(response) {
                var productInfo = JSON.parse(response);
                $('#price').text(productInfo.price);
                $('#stock').text(productInfo.stock);
            }
        });
    });
});

In the back-end PHP code, we can retrieve data from the database based on the received product ID. Query the corresponding specification information and return it to the front-end page for display.

$productId = $_POST['productId'];

// 查询规格信息
$query = "SELECT name, value FROM specifications WHERE product_id = :productId";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':productId', $productId, PDO::PARAM_INT);
$stmt->execute();
$specifications = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 查询价格和库存信息
$query = "SELECT price, stock FROM products WHERE id = :productId";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':productId', $productId, PDO::PARAM_INT);
$stmt->execute();
$productInfo = $stmt->fetch(PDO::FETCH_ASSOC);

$response = array(
    'specifications' => $specifications,
    'price' => $productInfo['price'],
    'stock' => $productInfo['stock']
);

echo json_encode($response);

Through the above code, we implemented the database design of product multi-specification SKU, demonstrated how to insert and query product specification information, and dynamically displayed product specification information through the front-end page.

Of course, this is just a simple example, and actual applications may be more complex. I hope this article can help developers who need to implement multiple product specifications SKU in PHP.

The above is the detailed content of How to implement database design for multi-specification SKU of products in PHP. For more information, please follow other related articles on the PHP Chinese website!