CodeIgniter：多商品场景下提取URL主机名并去重入库

Alright, let's walk through building this multi-product shopping cart system that handles domain deduplication for your database. Here's a step-by-step breakdown tailored to your requirements:

1. Frontend: Let Users Add Multiple Products #

First, you'll need a way for users to input multiple items. A dynamic form that lets users add/remove product rows works perfectly. Here's a quick example of how that might look in HTML/JS:

<div id="product-container">
  <!-- Initial product row -->
  <div class="product-row">
    <input type="text" placeholder="商品名称" name="product_names[]" required>
    <input type="number" step="0.01" placeholder="商品价格" name="product_prices[]" required>
    <input type="url" placeholder="商品URL" name="product_urls[]" required>
    <button type="button" class="remove-row">删除</button>
  </div>
</div>
<button type="button" id="add-product">添加商品</button>
<button type="submit">结算</button>

<script>
document.getElementById('add-product').addEventListener('click', () => {
  const container = document.getElementById('product-container');
  const newRow = document.querySelector('.product-row').cloneNode(true);
  // Clear input values in the cloned row
  newRow.querySelectorAll('input').forEach(input => input.value = '');
  container.appendChild(newRow);
});

// Handle remove row clicks
document.addEventListener('click', (e) => {
  if (e.target.classList.contains('remove-row') && document.querySelectorAll('.product-row').length > 1) {
    e.target.parentElement.remove();
  }
});
</script>

This setup lets users add as many products as needed, and submits all data as arrays (e.g., product_names[], product_prices[], product_urls[]) to your backend.

2. Backend: Extract Unique Domains from URLs #

Next, you need to parse each URL to get its root domain, and ensure we only store each domain once in the store table. Let's use Python as an example (the logic translates easily to other languages like JavaScript/PHP):

from urllib.parse import urlparse

def extract_root_domain(url):
    # Handle URLs without http/https prefix (common user input)
    if not url.startswith(('http://', 'https://')):
        url = f'https://{url}'
    
    parsed = urlparse(url)
    domain = parsed.netloc
    
    # Normalize domain: remove www. to avoid duplicate entries (e.g., www.aliexpress.com vs aliexpress.com)
    if domain.startswith('www.'):
        domain = domain[4:]
    
    return domain

Testing this function with your example URLs:

• aliexpress.com/product/phone_cover → aliexpress.com
• nike.com/cat/shoes → nike.com

3. Database: Store Unique Domains & Link Products #

First, define your table structures. Here's how they might look in SQL:

-- Store table (unique domains only)
CREATE TABLE store (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table (links to store via store_id)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    url TEXT NOT NULL,
    store_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (store_id) REFERENCES store(id)
);

Then, in your backend logic, process each product entry:

1. For each product, extract the domain using the function above.
2. Check if the domain already exists in the store table. If not, insert it.
3. Get the store_id (either from the existing entry or the new one).
4. Insert the product details into the products table with the corresponding store_id.

Using Python with MySQL (parameterized queries to prevent SQL injection):

import mysql.connector

def process_products(product_data):
    db = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_db"
    )
    cursor = db.cursor()

    for name, price, url in product_data:
        domain = extract_root_domain(url)
        
        # Insert domain if it doesn't exist, get the store_id
        cursor.execute("INSERT INTO store (domain) VALUES (%s) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)", (domain,))
        store_id = cursor.lastrowid
        
        # Insert product
        cursor.execute(
            "INSERT INTO products (product_name, price, url, store_id) VALUES (%s, %s, %s, %s)",
            (name, price, url, store_id)
        )
    
    db.commit()
    cursor.close()
    db.close()

4. Key Things to Keep in Mind #

• Input Validation: Always validate user input before processing. Ensure URLs are valid, prices are numeric, and product names aren't empty.
• Domain Normalization: The example removes www., but you might also want to handle other subdomains if needed (e.g., m.aliexpress.com should still map to aliexpress.com).
• Batch Optimization: If dealing with a lot of products, batch your database queries to reduce overhead (e.g., collect all unique domains first, insert them in bulk, then link products).

内容的提问来源于stack exchange，提问作者TanzilCh