显示存储在MYSQL数据库中的逗号分隔字符串中唯一的第一项

Question

<ul class="p-b-54">
    <li class="p-t-4">
        <a href="#" class="s-text13 active1">
        All
        </a>
    </li>
    <?php 
    $category_query_sql = "SELECT DISTINCT `Category` FROM `laptops`  ORDER BY `Id` DESC LIMIT 100 ";
    $run_query_category = mysqli_query($con,$category_query_sql) or die(mysqli_error($con));
    if(mysqli_num_rows($run_query_category) > 0){
        while($row = mysqli_fetch_array($run_query_category)){
            $product_Category = $row["Category"];                
    ?>

    <li class="p-b-9">
        <a href="#" class="s-text7">
        <?php 
        $prod_Cat = explode(", ", $product_Category);
        echo $prod_Cat[0]; 
        ?>
        </a>
    </li>
<?php 
        }
    }
?>
</ul>

这是如何将它们保存在数据库中的。

Electronics, Computers & Accessories, Computers & Tablets, Laptops, Traditional Laptops
Electronics, Computers & Accessories, Computers & Tablets
Electronics, Computers & Accessories, Computers & Tablets, Laptops, 2 in 1 Laptops
Electronics, Computers & Accessories, Computers & Tablets, Tablets
Electronics, Computers & Accessories, Monitors
Electronics, Accessories & Supplies, Audio & Video Accessories, TV Accessories & Parts, TV Ceiling & Wall Mounts
Electronics, Accessories & Supplies, Audio & Video Accessories
Electronics, Camera & Photo, Digital Cameras, DSLR Cameras
Electronics, Television & Video, Televisions
Electronics, PC Gaming, Gaming Computers
Gaming Monitors
Gaming Laptops 

这就是我所期待的

Electronics
Gaming Monitors
Gaming Laptops 

我想摆脱所有重复的电子产品

到目前为止，我的产出是

Electronics
Electronics
Electronics
Electronics
Electronics
Electronics
Electronics
Electronics
Electronics
Electronics
Gaming Monitors
Gaming Laptops 

我有一个用逗号分隔单词存储在mysql数据库中的类别列表。我成功地显示了每个字符串的第一个单词形式。虽然我的意图是得到独特的类别后，提取第一个单词。但正如你所看到的，我得到了所有重复的内容。我怎么才能摆脱他们？

如果要创建表，这是SQL查询。

-- --------------------------------------------------------

--
-- Table structure for table `gaminglaptops`
--

CREATE TABLE `gaminglaptops` (
  `Id` int(11) NOT NULL,
  `Category` text NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `gaminglaptops`
--

INSERT INTO `gaminglaptops` (`Category`) VALUES
('Gaming Laptops'),
('Electronics, PC Gaming, Gaming Computers'),
('Gaming Monitors'),
('Electronics, Television & Video, Televisions'),
('Electronics, Camera & Photo, Digital Cameras, DSLR Cameras'),
('Electronics, Accessories & Supplies, Audio & Video Accessories, TV Accessories & Parts, TV Ceiling & Wall Mounts'),
('Electronics, Accessories & Supplies, Audio & Video Accessories'),
('Electronics, Computers & Accessories, Monitors'),
('Electronics, Computers & Accessories, Computers & Tablets, Tablets'),
('Electronics, Computers & Accessories, Computers & Tablets, Laptops, 2 in 1 Laptops'),
('Electronics, Computers & Accessories, Computers & Tablets'),
('Electronics, Computers & Accessories, Computers & Tablets, Laptops, Traditional Laptops');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `gaminglaptops`
--
ALTER TABLE `gaminglaptops`
  ADD PRIMARY KEY (`Id`),

Answer 1

您可以稍微更改查询以返回唯一的结果。

使用索引提取第一个值，

SUBSTRING_INDEX('Electronics, PC Gaming, Gaming Computers', ',', 1)

将返回Electronics。

然后对你的查询做个区分，

SELECT DISTINCT(SUBSTRING_INDEX(`Category`, ',', 1)) AS Cat
FROM `laptops`
ORDER BY `Id`
DESC LIMIT 100

现在，查询只返回一个值的唯一结果。因此，您可以消除在循环中爆炸结果的需要。优化了下面的片段，

<?php
while($row = mysqli_fetch_array($run_query_category)) {
?>
<li class="p-b-9">
    <a href="#" class="s-text7"><?php echo $row["Cat"]; ?></a>
</li>
<?php 
}

Answer 2

尝试使用continue语句

<ul class="p-b-54">
    <li class="p-t-4">
        <a href="#" class="s-text13 active1">
        All
        </a>
    </li>
    <?php 
    $cat_name = "";
    $category_query_sql = "SELECT DISTINCT `Category` FROM `laptops`  ORDER BY `Id` DESC LIMIT 100 ";
    $run_query_category = mysqli_query($con,$category_query_sql) or die(mysqli_error($con));
    if(mysqli_num_rows($run_query_category) > 0){
        while($row = mysqli_fetch_array($run_query_category)){
            $product_Category = $row["Category"];                
    ?>

    <li class="p-b-9">
        <a href="#" class="s-text7">
        <?php 
        $prod_Cat = explode(", ", $product_Category);
        if($cat_name == $prod_Cat[0]){
            continue;
        }else{
            $catname = $prod_Cat[0];
        }
        echo $catname;
        ?>
        </a>
    </li>
<?php 
        }
    }
?>
</ul>

Answer 3

最简单的方法可能是创建一个空数组(如$uniqueElements)，在for-循环中迭代每个元素，并通过使用in_array($element，$uniqueElements)检查内容将每个唯一项推入空数组。