时间戳

首页 > 解决方案 > 如何从 PHP/MySQL 中检索逗号分隔的值？

php - 如何从 PHP/MySQL 中检索逗号分隔的值？

问题描述

目前我正在一个电子商务网站上工作，并且我已经成功地将动态多选下拉值插入到带有 implode 的数据库中。他们看起来像2, 3, 4。

现在我想检索那些没有逗号和空格的值并将它们显示为按钮，我尝试使用 explode 但它不起作用。

这是我的表：

动态尺寸表

尺寸存储在“tshirt_size”列中的 T 恤表

这是我用来插入大小值的代码

// Inserting Multiple Sizes into Database

        $tshirt_sizes_array = array();

        foreach($_POST['t_size'] as $key=>$value){
            $tshirt_sizes_array[].= $value;
            $tshirt_sizes = implode(', ', $tshirt_sizes_array);
        }

现在我想用explode检索这些值，但它不起作用，下面是我用来检索大小值的代码

while($row = mysqli_fetch_array($run_get_sizes)){
     $pr_sizes = explode(", ", $row['size_title']);
}

如果字段中存储了单个大小，它只会检索该值。像2or 3，并且当有多个值 as 时不起作用2, 3, 4。

这正是我想要实现的！

任何帮助将不胜感激！

更新

我的 SQL 查询：

$t_sizes = $row_t['tshirt_size'];

$get_sizes = "SELECT * FROM tshirt_sizes WHERE size_id = $t_sizes";

while($row_t4 = mysqli_fetch_array($run_get_sizes)){

                $pr_sizes = $row_t4['size_title'];

}

获取 T 恤型号代码

function get_tshirt_modal(){
    
    global $conn;

        $get_t = "SELECT * FROM tshirts ORDER BY RAND() LIMIT 0,6";
        $run_t = mysqli_query($conn, $get_t);

        while($row_t = mysqli_fetch_array($run_t)){

            $t_id = $row_t['tshirt_id'];
            $t_name = $row_t['tshirt_title'];
            $t_price = $row_t['tshirt_price'];
            $t_sale_price = $row_t['tshirt_sale_price'];
            $t_pre_1 = $row_t['tshirt_preview1'];
            $t_pre_2 = $row_t['tshirt_preview2'];
            $t_status = $row_t['tshirt_status'];
            $t_desc = $row_t['tshirt_desc'];
            $t_cat = $row_t['tshirt_category'];
            $t_sizes = $row_t['tshirt_size'];

            $get_status = "SELECT * FROM product_status WHERE p_status_id = $t_status";
            $run_get_status = mysqli_query($conn, $get_status);

            $get_cat = "SELECT * FROM tshirt_categories WHERE tshirt_category_id = $t_cat";
            $run_get_cat = mysqli_query($conn, $get_cat);

            $get_sizes = "SELECT * FROM tshirt_sizes WHERE size_id IN($t_sizes)";
            $run_get_sizes = mysqli_query($conn, $get_sizes);

            while($row_t2 = mysqli_fetch_array($run_get_status)){

                $pr_status = $row_t2['p_status_title'];
                $pr_status_lowercase = strtolower($pr_status);

            while($row_t3 = mysqli_fetch_array($run_get_cat)){

                $pr_cat = $row_t3['tshirt_category_title'];

            while($row_t4 = mysqli_fetch_array($run_get_sizes)){

                $pr_sizes = $row_t4['size_title'];
                foreach($pr_sizes as $key=>$value){
            
    echo "<div class='modal fade product-modal' id='productModal$t_id' tabindex='-1' role='dialog' aria-hidden='true'>
            <div class='modal-dialog' role='document'>
                <div class='modal-content'>
                    <div class='modal-body'>
                        <button type='button' class='close custom-close' data-dismiss='modal' aria-label='Close'>
                            <span aria-hidden='true'><i class='dl-icon-close'></i></span>
                        </button>
                        <div class='row'>
                            <div class='col-md-6'>
                                <div class='product-image-carousel nav-vertical-center nav-style-1'>
                                    <div class='product-image'>
                                        <div class='product-image--holder'>
                                            <a href='product-details.php?t_id=$t_id'>
                                                <img src='images/tshirt_preview_images/$t_pre_1' alt='Product Image' class='primary-image'>
                                            </a>
                                        </div>";
                                        if (!empty($pr_status_lowercase == 'none')) {
                                            // Don't Show Any Badge
                                        }else{
                                            echo "<span class='product-badge $pr_status_lowercase'>$pr_status</span>";
                                        }
                                    echo "</div>
                                </div>
                            </div>
                            <div class='col-md-6'>
                                <div class='modal-box product-summary'>
                                    <h3 class='product-title mb--15'>$t_name</h3>
                                    <span class='product-price-wrapper mb--20'>
                                        <span class='money'>₹$t_sale_price</span>
                                        <span class='product-price-old'>
                                            <span class='money'>₹$t_price</span>
                                        </span>
                                    </span>
                                    <div class='product-action d-flex flex-row align-items-center mb--30' style='margin-top:15px'>
                                        <div class='quantity'>
                                            <input type='number' class='quantity-input' name='qty' id='qty' value='1' min='1'>
                                        </div>
                                        <button type='button' class='btn btn-style-1 btn-semi-large add-to-cart'
                                            onclick='window.location.href='cart.php''>
                                            Add To Cart
                                        </button>
                                        <a href='wishlist.php?t_id=$t_id'><i class='dl-icon-heart2'></i></a>
                                    </div>
                                    <p class='product-short-description mb--25 mb-md--20'>$t_desc</p>
                                    <div class='product-meta float-left'>
                                        <span class='sku_wrapper font-size-12'>Tags -  
                                            <a href='shop-sidebar.html' rel='tag'>Style Blogger </a>
                                            <a href='shop-sidebar.html' rel='tag'>Style </a>
                                            <a href='shop-sidebar.html' rel='tag'>Trending</a>
                                        </span>
                                        <span class='posted_in font-size-12'>Category -  
                                            <a href='shop-sidebar.php' rel='tag'>$pr_cat</a>
                                        </span>
                                        <span class='posted_in font-size-12'>Available Sizes -  
                                            <a href='shop-sidebar.php' rel='tag'>$value</a>
                                        </span>
                                    </div>
                                    <div class='product-share-box float-right'>
                                        <span class='font-size-12'>Let's Vibe Humanity</span>
                                        <!-- Social Icons Start Here -->
                                        <ul class='social social-small'>
                                            <li class='social__item'>
                                                <a href='https://facebook.com' class='social__link'>
                                                    <i class='fa fa-facebook'></i>
                                                </a>
                                            </li>
                                            <li class='social__item'>
                                                <a href='https://twitter.com' class='social__link'>
                                                    <i class='fa fa-twitter'></i>
                                                </a>
                                            </li>
                                            <li class='social__item'>
                                                <a href='https://plus.google.com' class='social__link'>
                                                    <i class='fa fa-google-plus'></i>
                                                </a>
                                            </li>
                                            <li class='social__item'>
                                                <a href='https://plus.google.com' class='social__link'>
                                                    <i class='fa fa-pinterest-p'></i>
                                                </a>
                                            </li>
                                        </ul>
                                        <!-- Social Icons End Here -->
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>";}
                }
            }
        }
    }
}

更新决赛

伙计们，这是工作代码

$availableSizeArr = [];
            
     while($row_t4 = mysqli_fetch_array($run_get_sizes)){

     $pr_sizes = $row_t4['size_title'];
     $availableSizeArr[] = "<a href='shop-sidebar.php' rel='tag'>$pr_sizes</a>";

}

$availableSizes = implode(', ', $availableSizeArr);

非常感谢@biesior 的帮助！

标签： phpmysqldatabasee-commercedatabase-normalization

解决方案

正如评论中提到的其他人一样，您应该考虑重组数据库以使其正常化。

一个与您非常接近的解决方案是使用 SET 数据类型（它在 MySQL/MariaDB 中可用，当您有一组固定的值要存储时，它对于像您这样的情况非常有用）。方法如下：

CREATE TABLE shirts(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    sizes SET("XS", "S", "M", "L", "XL", "XXL", "XXXL")
);

INSERT INTO shirts(name, sizes) VALUES
("shirt1", "S,M,L"),
("shirt2", "L,XL"),
("shirt3", "M");

SELECT * FROM shirts;
+----+--------+-------+
| id | name   | sizes |
+----+--------+-------+
|  1 | shirt1 | S,M,L |
|  2 | shirt2 | L,XL  |
|  3 | shirt3 | M     |
+----+--------+-------+

以这种方式管理它要容易得多，并且在存储方面非常有效。是的，它离你已经拥有的代码也不远了！而且您根本不需要“动态尺寸表”表 - 所有内容都存储在 tshirts 表中。我的建议是基于您永远不会添加新 T 恤尺寸的假设——它们始终是一个固定的集合（就像您将来永远不会添加“XXXXXXXXL”一样）。最后——在里面搜索真的很简单。例如，如果用户只想搜索尺寸为“M”的衬衫，您可以像这样获取列表：

SELECT * FROM shirts
WHERE FIND_IN_SET("M", sizes)>0;

如果您必须有动态的 T 恤尺寸，那么您应该在尺寸表和衬衫表之间建立 M:M 关系。可以这样做：

CREATE TABLE shirts_sizes(
    size_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    size_title VARCHAR(5)
);

INSERT INTO shirts_sizes(size_title) VALUES
("XS"),("S"),("M"),("L"),("XL"),("XXL"),("XXXL");

CREATE TABLE shirts(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO shirts(name) VALUES
("shirt1"),
("shirt2"),
("shirt3");

CREATE TABLE shirts_sizes_availability(
    size_id INT NOT NULL,
    shirt_id INT NOT NULL,
    PRIMARY KEY(size_id, shirt_id),
    FOREIGN KEY(size_id) REFERENCES shirts_sizes(size_id),
    FOREIGN KEY(shirt_id) REFERENCES shirts(id)
);

INSERT INTO shirts_sizes_availability(shirt_id,size_id) VALUES
(1,2),(1,3),(1,4),(2,4),(2,5),(3,3);

请注意，现在我们在连接表的单独行中插入特定的衬衫尺寸，而不是用逗号值分隔。以下是稍后提取它们的方法：

SELECT shirts.id, shirts.name, shirts_sizes.size_title
FROM shirts
JOIN shirts_sizes_availability
  ON shirts.id = shirts_sizes_availability.shirt_id
JOIN shirts_sizes
  ON shirts_sizes_availability.size_id = shirts_sizes.size_id;
+----+--------+------------+
| id | name   | size_title |
+----+--------+------------+
|  1 | shirt1 | S          |
|  1 | shirt1 | M          |
|  1 | shirt1 | L          |
|  2 | shirt2 | L          |
|  2 | shirt2 | XL         |
|  3 | shirt3 | M          |
+----+--------+------------+

根据前面的示例，如果您只搜索尺寸为“M”的衬衫，则可以在此处轻松添加 WHERE 子句。如果你想像前面的例子一样将它们以逗号分隔，你可以像这样使用 GROUP_CONCAT：

SELECT shirts.id, shirts.name, GROUP_CONCAT(shirts_sizes.size_title) AS sizes
FROM shirts
JOIN shirts_sizes_availability 
  ON shirts.id = shirts_sizes_availability.shirt_id
JOIN shirts_sizes
  ON shirts_sizes_availability.size_id = shirts_sizes.size_id
GROUP BY shirts.id;
+----+--------+-------+
| id | name   | sizes |
+----+--------+-------+
|  1 | shirt1 | S,M,L |
|  2 | shirt2 | L,XL  |
|  3 | shirt3 | M     |
+----+--------+-------+

尽管我在上面说了所有这些，但我不明白为什么爆炸不起作用。这是一个可以正常工作的简化示例：

//You should have fetched that string from the DB
$row['size_title'] = "S,L,XL";
//Now I explode it
$pr_sizes = explode(",", $row['size_title']);
//And each element is stored correctly
echo $pr_sizes[0]."<br />"; //prints "S"
echo $pr_sizes[1]."<br />"; //prints "L"
echo $pr_sizes[2];          //prints "XL"

推荐阅读