合并两个表并将它们的数据显示在数组中
卫乐童
问题内容：
问题答案：
我有2个表：product和cart，我希望结合这2个表并根据特定条件以数组形式显示数据，如下所示：
应显示特定类别下的所有产品，如果特定用户购买了给定产品中的任何产品，则其详细信息也应显示在该产品的前面
我到目前为止所做的代码是
$catid = $_REQUEST['catid'];
$userid = $_REQUEST['userid'];
$sql = "select * from productsize where catid = '".$catid."' GROUP BY productid";
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_assoc($result))
{
$rows['catid'] = $row['catid'];
$rows['catname'] = $row['catname'];
$rows['productid'] = $row['productid'];
$rows['prodname'] = $row['prodname'];
$rows['prodimg'] = $row['prodimg'];
$row2[]=$rows;
}
}
echo "<pre>";
print_r($row2);
echo "</pre>";
它给出了这样的数组
Array
(
[0] => Array
(
[catid] => 2
[catname] => C1
[productid] => 13
[prodname] => P1
[prodimg] =>
)
[1] => Array
(
[catid] => 2
[catname] => C1
[productid] => 14
[prodname] => P1
[prodimg] =>
)
[2] => Array
(
[catid] => 2
[catname] => C1
[productid] => 15
[prodname] => P3
[prodimg] =>
)
)
但是我想要代替上面的数组的最后一个数组是
Array
(
[0] => Array
(
[catid] => 2
[catname] => C1
[productid] => 13
[prodname] => P1
[prodimg] =>
[size] => Array
(
[0] => small
[1] => medium
[2] => large
[3] => perpiece
)
[cost] => Array
(
[0] => 10
[1] => 20
[2] => 30
[3] => 12
)
[purchasedsize] => Array
(
[0] => small
[1] => 0
[2] => large
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 2
[1] => 0
[2] => 1
[3] => 0
)
[userid] => 1
)
[1] => Array
(
[catid] => 2
[catname] => C1
[productid] => 14
[prodname] => P1
[prodimg] =>
[size] => Array
(
[0] => small
[1] => medium
[2] => large
[3] => 0
)
[cost] => Array
(
[0] => 15
[1] => 20
[2] => 25
[3] => 0
)
[purchasedsize] => Array
(
[0] => 0
[1] => medium
[2] => 0
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 0
[1] => 1
[2] => 0
[3] => 0
)
[userid] => 1
)
[2] => Array
(
[catid] => 2
[catname] => C1
[productid] => 15
[prodname] => P3
[prodimg] =>
[size] => Array
(
[0] => 0
[1] => medium
[2] => 0
[3] => perpiece
)
[cost] => Array
(
[0] => 0
[1] => 20
[2] => 0
[3] => 18
)
[purchasedsize] => Array
(
[0] => 0
[1] => 0
[2] => 0
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 0
[1] => 0
[2] => 0
[3] => 0
)
[userid] => 0
)
)
产品表视图（如您所见，产品表中包含一个productid，在每个productid下最多可以有4个尺寸（不会超过4个））
id catid catname productid prodsize cost prodname prodimg
1 2 C1 13 small 10 P1
2 2 C1 13 medium 20 P1
3 2 C1 13 large 30 P1
4 2 C1 13 perpiece 12 P1
5 2 C1 14 small 15 P2
6 2 C1 14 medium 20 P2
7 2 C1 14 large 25 P2
8 2 C1 15 perpiece 18 P3
9 2 C1 15 medium 20 P3
推车表的视图
id catid catname userid productid prodname prodsize quantity prodcost
1 2 C1 1 13 P1 large 1 30
2 2 C1 1 13 P1 small 2 10
3 2 C1 1 14 P2 medium 1 20
任何人都可以帮助我获得所需的数组结果吗？
问题答案：
试试这个
$catid = $_REQUEST['catid'];
$userid = $_REQUEST['userid'];
$sql= "SELECT p.catid, p.catname, p.productid, p.prodimg,
GROUP_CONCAT(p.prodsize ORDER BY p.id ASC) as size,
GROUP_CONCAT(p.cost ORDER BY p.id ASC) as cost, p.prodname,
GROUP_CONCAT(c.prodsize,'-',c.quantity) as cart_details, GROUP_CONCAT(DISTINCT(c.userid)) as user_id
FROM products p
LEFT JOIN cart c ON(c.productid = p.productid AND c.userid = '$userid' AND p.prodsize = c.prodsize)
WHERE p.catid ='$catid'
GROUP BY p.productid
ORDER BY user_id DESC, p.productid ASC";
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0)
{
$i = 0;
while($row = mysql_fetch_assoc($result))
{
$rows[$i]['catid'] = $row['catid'];
$rows[$i]['catname'] = $row['catname'];
$rows[$i]['productid'] = $row['productid'];
$rows[$i]['prodname'] = $row['prodname'];
$rows[$i]['prodimg'] = $row['prodimg'];
$final_size = array_fill(0, 4, '0');
$final_cost = array_fill(0, 4, '0');
$size = explode(',', $row['size']);
$cost = explode(',', $row['cost']);
foreach($size as $k=>$sizecol) {
switch($sizecol) {
case 'small':
$array_key = '0';
break;
case 'medium':
$array_key = '1';
break;
case 'large':
$array_key = '2';
break;
case 'perpiece':
$array_key = '3';
break;
}
$final_size[$array_key] = $sizecol;
$final_cost[$array_key] = $cost[$k];
}
$cart_details = explode(',', $row['cart_details']);
$purchasedsize = array_fill(0, 4, '0'); //Since you displayed this array has 4 values only
$purchasedquantity = array_fill(0, 4, '0');
foreach($cart_details as $cart) {
if($cart != '') {
$details = explode('-', $cart);
$key = array_search($details[0], $size);
$purchasedsize[$key] = $details[0];
$purchasedquantity[$key] = $details[1];
}
}
$rows[$i]['size'] = $final_size;
$rows[$i]['cost'] = $final_cost;
$rows[$i]['purchasedsize'] = $purchasedsize;
$rows[$i]['purchasedquantity'] = $purchasedquantity;
$rows[$i]['userid'] = $row['user_id'];
$i++;
}
}
echo "<pre>";
print_r($rows);
echo "</pre>";
输出数组
Array
(
[0] => Array
(
[catid] => 2
[catname] => c1
[productid] => 13
[prodname] => P1
[prodimg] =>
[size] => Array
(
[0] => small
[1] => medium
[2] => large
[3] => perpiece
)
[cost] => Array
(
[0] => 10
[1] => 20
[2] => 30
[3] => 12
)
[purchasedsize] => Array
(
[0] => small
[1] => 0
[2] => large
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 2
[1] => 0
[2] => 1
[3] => 0
)
[userid] => 1
)
[1] => Array
(
[catid] => 2
[catname] => c1
[productid] => 14
[prodname] => P2
[prodimg] =>
[size] => Array
(
[0] => small
[1] => medium
[2] => large
[3] => 0
)
[cost] => Array
(
[0] => 15
[1] => 20
[2] => 25
[3] => 0
)
[purchasedsize] => Array
(
[0] => 0
[1] => medium
[2] => 0
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 0
[1] => 1
[2] => 0
[3] => 0
)
[userid] => 1
)
[2] => Array
(
[catid] => 2
[catname] => C1
[productid] => 15
[prodname] => P3
[prodimg] =>
[size] => Array
(
[0] => 0
[1] => medium
[2] => 0
[3] => perpiece
)
[cost] => Array
(
[0] => 0
[1] => 20
[2] => 0
[3] => 18
)
[purchasedsize] => Array
(
[0] => 0
[1] => 0
[2] => 0
[3] => 0
)
[purchasedquantity] => Array
(
[0] => 0
[1] => 0
[2] => 0
[3] => 0
)
[userid] =>
)
)
类似资料：
-
问题内容： 我有两个像这样的数组： 我想结合这两个数组，使其不包含重复项，并保留其原始键。例如，输出应为： 我已经尝试过了，但是它正在更改其原始键： 有什么办法吗？ 问题答案： 只需使用： 那应该解决。因为如果一个键出现多次（例如在您的示例中），则使用字符串键，因此一个键将覆盖具有相同名称的处理键。因为在您的情况下，它们两者都具有相同的值，但这无关紧要，并且还会删除重复项。 更新：我刚刚意识到，P
-
问题内容： 我需要将供体表中的数据合并到两个目标表中。结构如下。如果在跟踪表中找不到projid，则需要在组件表中创建新组件，并使用新的ID插入到跟踪表中。此外，对于供体表中不再存在的那些项目，跟踪表的“活动”列应标记为0。我可以在单个merge语句中实现此功能吗？ 供体表 跟踪表 成分表 合并后的输出表： 成分表 跟踪表 问题答案： 从理论上讲，应该有一个解决方案可以在单个语句中执行此操作，但是
-
问题内容： 我见过类似的问题，但没有一个提供我所要的答案，因此，如果这被认为是重复的，我在此致歉。我正在尝试将数组{1,2,3}和{4,5,6}合并为{1,2,3,4,5,6}。我做错了什么？我是java的新手。抱歉，问题很愚蠢。 问题答案： 代替 您需要调用merge方法，并将结果分配给数组，例如： 您的for循环也应该是：
-
问题内容： 我有两个数组，我想将这两个数组合并为一个数组。请查看以下详细信息： 第一个数组： 第二个数组： 我想要这个结果。有人知道该怎么做吗？ 希望你理解这个问题。先感谢您。 问题答案： 已修正 （再次） 接受无限数量的输入数组，将所有子数组作为索引数组合并到一个容器中，然后返回结果。 编辑03/2014： 提高可读性和效率
-
问题内容： 我有两个像 我希望它们合并成单个数组 问题答案： 您需要该方法。
-
我在从两个表中检索数据然后列出它们时遇到了一些问题。我想将用户的提要帖子和他们喜欢的活动全部列在一个列表中。 提要-用户帖子表 我想做的是：在活动墙中列出提要和类似用户的活动。 所以它应该输出如下（按时间戳desc排序）： “这是用户A的帖子” 我的当前SQL： 然而，我的问题是我不知道如何链接这两个表，因为我的“feed”中的ID与“likes”中的ID不同