Java吧»社区 › 技术频道 › 技术问答 › 合并两个表并将它们的数据显示在数组中 ...

赞

回答

收藏

合并两个表并将它们的数据显示在数组中

技术问答 487 人阅读 | 0 人回复 | 2023-09-14

我有2个表：product和cart，我希望结合这2个表并根据特定条件以数组形式显示数据，如下所示：
" C" M8 A! \4 W6 _' |
应显示特定类别下的所有产品，如果特定用户购买了给定产品中的任何产品，则其详细信息也应显示在该产品的前面$ |, d- x  I2 b5 s

我到目前为止所做的代码是
$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 "";
print_r($row2);
echo "";
它给出了这样的数组
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 "";
print_r($rows);
echo "";
输出数组
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] =>
)
)

分享到：

回复

漁者 LV1

36 积分

6 主题

热门推荐

【S0457】鲁班学院java架构师二期视频教程
java8 2025-01-11
2025年1月11日签到记录贴
fringellm 2025-01-11
S0221 已失效，麻烦补一下
wuxi429785208 2025-01-10
S0238失效，麻烦补一下，感谢
javatao 2025-01-10
【S0456】晖哥讲编程视频教程
java8 2025-01-10
2025年1月10日签到记录贴
fringellm 2025-01-10
Netty 4核心原理与手写RPC框架实战 PDF 电子书【X0260】
java8 2025-01-09
【S0455】OpenCV入门到进阶：实战三大典型项目视频教程
java8 2025-01-09
2025年1月9日签到记录贴
xsfx 2025-01-09
跟闪电侠学 Netty：Netty 即时聊天实战与底层原理 PDF 电子书【X0259】
java8 2025-01-08