从mysql表中分离逗号分隔的值 [英] Separate comma separated values from mysql table
问题描述
我有一个表命名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'];
}
这篇关于从mysql表中分离逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
