从mysql表中分离逗号分隔的值

本文介绍了从mysql表中分离逗号分隔的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表命名related_products，

其中 products_id 是主要产品。 / p>

和 related_products_ids 包含与主要产品相关的产品ID。

------------------------------------- ------- | products_id | related_products_ids？ | ----------------------------------------- | 1 | 1,2,3,4,6，| | ----------------------------------------- | 2 | 1,2,3，| | ----------------------------------------- | 3 | 1,2，| -------------------------------------------

我有复选框，

< input value =1type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 1 < input value =2type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 2 < input value =3type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 3 < input value =4type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 4 < input value =5type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 5 < input value =6type =checkboxname =rp_product []id =in-category> Microsoft IntelliMouse Pro 6

复选框由php

<？php echo'< div class =categorydiv>< div id =category-allclass = >< ul id =categorychecklistclass =list：category categorychecklist form-no-clear>'; $ rp_sql =select products_id，products_name from.TABLE_PRODUCTS_DESCRIPTION。order by products_id; $ rp_1 = mysql_query（$ rp_sql）; while（$ rp_2 = mysql_fetch_array（$ rp_1））{ echo< li id = \category-。$ rp_2 ['products_id']。\class = \ popular-category\>< label class = \selectit\>< input value = \。$ rp_2 ['products_id']。\type = \ checkbox\name = \rp_product [] \id = \in-category-1 \>。$ rp_2 ['products_name']。< / label>< / li> ;; } mysql_free_result（$ rp_1）; echo'< / ul>< / div>< / div>'; ？>

如何添加选中上的 related_products_ids 值。例如，im在产品ID 1中。

另一件事：我还想在产品页面中显示这些相关产品。怎么做？ 例如，在 products_id 为1的产品页面中，我要显示 table_name1 $ c>由 related_products_ids 。

UPDATE： 我已使用此代码显示数据，

$ sql =SELECT related_products_ids FROM.TABLE_RELATED_PRODUCTS。where products_id ='。 （int）$ _ GET [products_id]。'; $ result = mysql_query（$ sql）; $ row = mysql_fetch_array（$ result）; $ lst_rp = explode（'，'，$ row ['related_products_ids']）; foreach（$ lst_rp as $ rp_id）{ $ res =SELECT products_id，products_name FROM.TABLE_PRODUCTS_DESCRIPTION。WHERE products_id ='。 $ rp_id。 '; } $ result1 = mysql_query（$ res）; while（$ row1 = mysql_fetch_array（$ result1））{ echo $ row1 ['products_name']; }

但不显示任何内容。

我的代码是否错误？

请帮助，因为我不知道该怎么做。 谢谢。

解决方案

首先，您的复选框不应该有相同的ids。

执行这样的操作：

foreach（$ lst_product_ids as $ product_id）{ $ checked =''; $ lst_rel_prods = explode（'，'，$ arr_related_product_ids [$ product_id]）; if（in_array（$ product_id，$ lst_rel_prods）） $ checked =checked; echo< input value ='。 $ product_id。 'type ='checkbox'name ='rp_product []'。 $ checked。 > }

显然，假设你有$ lst_product_ids = array ，3,4,5,6）和$ arr_related_product_ids = array（1 =>'1,2,3,4,6，'，2 =>等。）

UPDATE：将您的代码更改为此（编辑：在还选择related_products_ids之后）：

while $ rp_2 = mysql_fetch_array（$ rp_1））{ $ checked =''; $ lst_rp = explode（'，'，$ rp_2 ['related_products_ids']）; if（in_array $ rp_2 ['products_id']，$ lst_rp））$ checked =checked; echo< li id = \category - 。$ rp_2 ['products_id']。\class = \popular-category\>< label class = \selectit\>< input value = \$ rp_2 ['products_id']。\type = \checkbox\name = \rp_product [] \id = \\。$ checked。> 。$ rp_2 ['products_name']。< / label>< / li>; }

但是请记住，对于每个复选框也要使用不同的ID。

编辑：您还应该选择'related_products_ids'：

$ rp_sql =从.TABLE_PRODUCTS_DESCRIPTION中选择products_id，products_name，related_products_ids。 order by products_id;

UPDATE：显示逗号分隔的ID的值，存储逗号分隔关系的方法，你必须在while循环中使用一个额外的 foreach（$ lst_rp as $ rp_id） loop，并且在foreach循环为每个相关产品执行SQL选择，例如

$ query =SELECT products_id，products_name FROM.TABLE_PRODUCTS_DESCRIPTION。 WHERE products_id =。$ rp_id;

更新：如果您有新问题，

尝试这个代码如果你的产品id是一个SQL int（），那么你必须使用一个新的问题，并链接到这一个作为参考，否则这可以永远。不要在查询中使用引号：

$ sql =SELECT related_products_ids FROM.TABLE_RELATED_PRODUCTS。其中products_id =。$ _GET [products_id]; //我改变了这里 $ result = mysql_query（$ sql）; $ row = mysql_fetch_assoc（$ result）; // I MADE A CHANGE HERE $ lst_rp = explode（'，'，$ row ['related_products_ids']）; foreach（$ lst_rp as $ rp_id）{ $ query =SELECT products_id，products_name FROM .TABLE_PRODUCTS_DESCRIPTION。 WHERE products_id =。$ rp_id; $ result1 = mysql_query（$ res）; //这个和下面必须停留在循环内 $ row1 = mysql_fetch_assoc（$ result1）; echo $ row1 ['products_name']; }

I have a table naming related_products,

Where products_id is the main product.

and related_products_ids consist of product ids related to the main product.

-------------------------------------------- | products_id | related_products_ids | | ----------------------------------------- | 1 | 1,2,3,4,6, | | ----------------------------------------- | 2 | 1,2,3, | | ----------------------------------------- | 3 | 1,2, | -------------------------------------------

I have checkboxes,

<input value="1" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 1 <input value="2" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 2 <input value="3" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 3 <input value="4" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 4 <input value="5" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 5 <input value="6" type="checkbox" name="rp_product[]" id="in-category"> Microsoft IntelliMouse Pro 6

The checkboxes are generated by php,

<?php echo '<div class="categorydiv"><div id="category-all" class="tabs-panel"><ul id="categorychecklist" class="list:category categorychecklist form-no-clear">'; $rp_sql = "select products_id, products_name from ".TABLE_PRODUCTS_DESCRIPTION." order by products_id"; $rp_1 = mysql_query($rp_sql); while($rp_2 = mysql_fetch_array($rp_1)) { echo "<li id=\"category-".$rp_2['products_id']."\" class=\"popular-category\"><label class=\"selectit\"><input value=\"".$rp_2['products_id']."\" type=\"checkbox\" name=\"rp_product[]\" id=\"in-category-1\"> ".$rp_2['products_name']."</label></li>"; } mysql_free_result($rp_1); echo '</ul></div></div>'; ?>

How to add checked to these checkboxes depending on the related_products_ids values. For example im in the product id 1.

Another thing: I also want to display those related products in the product page. How to do that? Example, Im in the product page where products_id is 1, i want to display the products from a table like table_name1 by related_products_ids.

UPDATE: I have used this code for displaying data,

$sql = "SELECT related_products_ids FROM ".TABLE_RELATED_PRODUCTS." where products_id = '" . (int)$_GET["products_id"]."'"; $result = mysql_query($sql); $row = mysql_fetch_array($result); $lst_rp = explode(',', $row['related_products_ids']); foreach($lst_rp as $rp_id) { $res = "SELECT products_id, products_name FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE products_id='" . $rp_id . "'"; } $result1 = mysql_query($res); while($row1 = mysql_fetch_array($result1)) { echo $row1['products_name']; }

However, it displays nothing..

Is my code wrong?

Please help as i don't know how to do it. Thank you.

解决方案

First of all, your checkboxes should not have the same ids.

Do something like this:

foreach ($lst_product_ids as $product_id) { $checked = ''; $lst_rel_prods = explode(',', $arr_related_product_ids[$product_id]); if (in_array($product_id, $lst_rel_prods)) $checked = " checked"; echo "<input value='" . $product_id . "' type='checkbox' name='rp_product[]'" . $checked . "> }

Obviously, assuming you have $lst_product_ids = array(1,2,3,4,5,6) and $arr_related_product_ids = array(1 => '1,2,3,4,6,', 2 => etc.)

UPDATE: Change your code to this (EDIT: after also selecting 'related_products_ids'):

while($rp_2 = mysql_fetch_array($rp_1)) { $checked = ''; $lst_rp = explode(',', $rp_2['related_products_ids']); if (in_array($rp_2['products_id'], $lst_rp)) $checked = " checked"; echo "<li id=\"category-".$rp_2['products_id']."\" class=\"popular-category\"><label class=\"selectit\"><input value=\"".$rp_2['products_id']."\" type=\"checkbox\" name=\"rp_product[]\" id=\"\"" . $checked . "> ".$rp_2['products_name']."</label></li>"; }

But remember also to use different ids for each checkbox.

EDIT: You should also select 'related_products_ids':

$rp_sql = "select products_id, products_name, related_products_ids from ".TABLE_PRODUCTS_DESCRIPTION." order by products_id";

UPDATE: To display values for the comma separated ids, and with your current (probably inflexible) method of storing comma-separated relations, you will have to use an extra foreach($lst_rp as $rp_id) loop inside the while loop, and inside the foreach loop do a SQL select for each related product, something like

$query = "SELECT products_id, products_name FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE products_id=" . $rp_id;

UPDATE: If you have new issues, you should probably post a new question and link to this one as reference, otherwise this could go on forever.

Try this code. If your product id is a SQL int(), then you must not use quotes in the query:

$sql = "SELECT related_products_ids FROM ".TABLE_RELATED_PRODUCTS." where products_id = " . $_GET["products_id"]; // I MADE CHANGES HERE $result = mysql_query($sql); $row = mysql_fetch_assoc($result); // I MADE A CHANGE HERE $lst_rp = explode(',', $row['related_products_ids']); foreach($lst_rp as $rp_id) { $query = "SELECT products_id, products_name FROM ".TABLE_PRODUCTS_DESCRIPTION." WHERE products_id=" . $rp_id; $result1 = mysql_query($res); // THIS AND THE FOLLOWING MUST STAY INSIDE THE LOOP $row1 = mysql_fetch_assoc($result1); echo $row1['products_name']; }