How to Split and Store Keywords from a Comma-Separated List in PHP and MySQL?

Effective Keyword Splitting and Storage in PHP and MySQL

In database management, you may encounter scenarios where you need to extract and organize keywords from a given field. This article demonstrates an optimized approach using PHP and MySQL to split keywords, store them in a dedicated table, and link them back to the original data using intermediate tables.

Problem Statement:

Given a table containing post IDs and associated tags stored as a comma-separated list, the task involves:

• Separating tags into individual keywords
• Storing unique keywords in a separate table
• Establishing intermediate connections between posts and keywords

Solution:

Optimized Database Schema:

• Post_Tags: Stores original post data and tags as a string
• Keywords: Stores unique keywords
• Post_Keywords: Intermediate table linking posts to keywords

PHP Code:

<code class="php">// Connect to the database
$mysqli = new mysqli($host, $user, $password, $database);

// Initialize variables
$stmt = $mysqli->prepare("SELECT post_id, tags_csv FROM post_tags");
$stmt->execute();
$result = $stmt->get_result();

// Loop through the post tags
while ($row = $result->fetch_assoc()) {
    $post_id = $row['post_id'];
    $tags = explode(',', $row['tags_csv']);

    // Insert unique keywords into the Keywords table
    foreach ($tags as $tag) {
        $stmt = $mysqli->prepare("INSERT IGNORE INTO keywords (name) VALUES (?)");
        $stmt->bind_param("s", $tag);
        $stmt->execute();
    }

    // Get keyword IDs and insert into Post_Keywords table
    foreach ($tags as $tag) {
        $stmt = $mysqli->prepare("SELECT keyword_id FROM keywords WHERE name = ?");
        $stmt->bind_param("s", $tag);
        $stmt->execute();
        $result = $stmt->get_result();
        $keyword_id = $result->fetch_assoc()['keyword_id'];

        $stmt = $mysqli->prepare("INSERT IGNORE INTO post_keywords (post_id, keyword_id) VALUES (?, ?)");
        $stmt->bind_param("ii", $post_id, $keyword_id);
        $stmt->execute();
    }
}</code>

Benefits:

• Optimized schema for efficient keyword queries
• Ensures keyword uniqueness by utilizing the Keywords table
• Maintains data integrity through intermediate table relationships

Note: The provided solution is solely focused on the core functionality and does not consider additional aspects such as data validation or error handling.

The above is the detailed content of How to Split and Store Keywords from a Comma-Separated List in PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!