在一个阵列结合两个表并显示它们的数据 [英] combine two tables and display their data in an array
本文介绍了在一个阵列结合两个表并显示它们的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有2个表：产品和车，我想根据特定条件数组这两个表的组合和显示数据，如下：
I have 2 tables: product and cart, I wish to combine these 2 tables and display the data in array according to a particular condition, as follows:
在所有特定类别的产品应该得到显示，如果
特定用户已购买了给定产品中的任何产品
那么它的细节也应该得到显示在该产品的前
All the products under a specific category should get displayed and if a particular user have purchased any product among the given products then its details should also get displayed in front of that product
这是我做过直到现在code是
The code that I have done until now is
$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>";
它给出了一个这样的数组
It gives an array like this
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] =>
)
)
不过，我想代替上述阵列最终阵列
But the final array that I want in place of the above array is
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））
View of product table (As you will see the product table carries a productid and under each productid there are max 4 size (there won't be more than 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
任何人都可以请帮我获得所需的数组作为结果呢？
Can anyone please help me to get the required array as the result?
推荐答案
试试这个
$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] =>
)
)
这篇关于在一个阵列结合两个表并显示它们的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
