如何从PHP/MMySQL中检索逗号分隔的值

Question

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

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

以下是我的表格：

动态尺寸表

T恤衫存放在"；tshirt_size"；柱

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

// 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);
}

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

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

只有当字段中存储了单个大小时，它才会检索值。类似于2或3，当有多个值作为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>";
} 

$available Size=内爆('，'，$available Size Arr(；

感谢@biesior的帮助！

Answer 1

正如其他人在评论中提到的那样，您应该考虑重组DB以使其正常化。

一个非常接近您的解决方案是使用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_CONCT：

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"

Answer 2

当您在字段中保留逗号分隔的值时，这意味着您应该使用IN表达式而不是简单的=比较器。就像在一些谷歌搜索的解决方案中一样，您的查询看起来像：

if($t_sizes != ''){
$get_sizes = "SELECT * FROM tshirt_sizes WHERE size_id IN($t_sizes)";
}

注意：尽管在这种情况下，您不处理传入的用户数据，但最好使用准备好的语句。今天的作业是检查整个代码，并用PDO或mysqli准备的语句替换原始查询。

Answer 3

问题的根源是数据库结构不尊重第一个正规形式-不能让text/varchar类型的字段充当逗号分隔的数组，因为表的字段必须只包含原子(不可分割的值(，以避免数据冗余并提高数据完整性。我建议您删除字段tshirt_size并创建一个表tshirt_size[/strong>(如果您只销售tshirt(

CREATE TABLE tshirt_size
(
tshirt_id int, // for tshirt 1
size_id int, // of size L
stock int // I have 3 in stock
)

如果你不尊重数据的基本原理，那么编码就会变得非常困难。