我有一个购物车表,看起来像这样
id catid catname userid productid prodimg prodname prodsize prodcost quantity datee timee totalamount
1 1 CN1 1 1 ABC P1 small 10 1 3/10/2015 10:00am 10
2 1 CN1 1 1 ABC P1 medium 20 1 4/10/2015 10:00am 20
3 1 CN1 1 1 ABC P1 large 30 1 3/10/2015 10:00am 30
4 1 CN1 1 1 ABC P1 perpiece 5 1 3/10/2015 10:00am 5
5 1 CN1 1 2 CDF P2 small 6 1 3/10/2015 10:00am 6
6 1 CN1 1 2 CDF P2 large 14 1 4/10/2015 10:00am 14
7 1 CN1 2 1 ABC P1 small 10 2 3/10/2015 10:00am 20
我希望以一种特定的方式在数组中显示它的数据(根据userid),我想要的结果数组看起来像这样
Array
(
[0] => Array
(
[catid] => 1
[catname] => CN1
[userid] => 1
[productid] => 1
[prodimg] => ABC
[prodname] => P1
[prodsize] => Array
(
[0] => small
[1] => medium
[2] => large
[3] => perpiece
)
[prodcost] => Array
(
[0] => 10
[1] => 20
[2] => 30
[3] => 5
)
[quantity] => Array
(
[0] => 1
[1] => 1
[2] => 1
[3] => 1
)
[datee] => Array
(
[0] => 3/10/2015
[1] => 4/10/2015
[2] => 3/10/2015
[3] => 3/10/2015
)
[timee] => Array
(
[0] => 10:00am
[1] => 10:00am
[2] => 10:00am
[3] => 10:00am
)
[totalamount] => Array
(
[0] => 10
[1] => 20
[2] => 30
[3] => 5
)
)
[1] => Array
(
[catid] => 1
[catname] => CN1
[userid] => 1
[productid] => 2
[prodimg] => CDF
[prodname] => P2
[prodsize] => Array
(
[0] => small
[1] => 0
[2] => large
[3] => 0
)
[prodcost] => Array
(
[0] => 6
[1] => 0
[2] => 14
[3] => 0
)
[quantity] => Array
(
[0] => 1
[1] => 0
[2] => 1
[3] => 0
)
[datee] => Array
(
[0] => 3/10/2015
[1] => 0
[2] => 4/10/2015
[3] => 0
)
[timee] => Array
(
[0] => 10:00am
[1] => 0
[2] => 10:00am
[3] => 0
)
[totalamount] => Array
(
[0] => 6
[1] => 0
[2] => 14
[3] => 0
)
)
)
我使用的代码是
$userid = $_REQUEST['userid'];
$sql= "SELECT catid, catname, productid, prodimg, GROUP_CONCAT(prodsize ORDER BY id ASC) as prodsize, GROUP_CONCAT(prodcost ORDER BY id ASC) as prodcost, GROUP_CONCAT(quantity ORDER BY id ASC) as quantity, GROUP_CONCAT(datee ORDER BY id ASC) as datee, GROUP_CONCAT(timee ORDER BY id ASC) as timee,
GROUP_CONCAT(totalamount ORDER BY id ASC) as totalamount from cart WHERE userid ='$userid'";
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0)
{
while($result = mysqli_fetch_assoc($result))
{
echo "<pre>";
print_r($result);
echo "</pre>";
}
}
但是它没有按照我想要的方式显示数组,它显示了下面的数组
Array
(
[0] => Array
(
[catid] => 1
[catname] => CN1
[productid] => 1
[prodimg] => ABC
[prodsize] => small,medium,large
[prodcost] => 10,20,30
[quantity] => 1,1,1,1
[datee] => 3/10/2015,4/10/2015,3/10/2015,3/10/2015
[timee] => 10:00am,10:00am,10:00am,10:00am
[totalamount] => 10,20,30,5
)
)
谁能告诉我如何以正确的方式得到数组?这个数组的另一个重要方面是prodsize中的数组应该是固定的,即如果prod大小较小,那么它将存储在[0]中,中[1],大[2]和perpiece[3]中,如果这些值之一不存在,那么它应该是0,prodcost, quantity date和time也将遵循相同的方法
我认为您需要一种算法来重新格式化数据库中的结果。我可以看到你想要的是得到这些连接的结果,并把它们分开在独立的数组键。
我还没有能够尝试它,但我怀疑您可能需要或可能不需要$ I索引来分配元素。尝试。
你可以这样做:
$formatedResult = [];
$i = 0;
while($result = mysqli_fetch_assoc($result))
{
$formatedResult = $result;
$formatedResult[$i]['prodsize'] = explode(',', $result[$i]['prodsize']);
$formatedResult[$i]['prodcost'] = explode(',', $result[$i]['prodcost']);
$formatedResult[$i]['quantity'] = explode(',', $result[$i]['quantity']);
$formatedResult[$i]['datee'] = explode(',', $result[$i]['datee']);
$formatedResult[$i]['timee'] = explode(',', $result[$i]['timee']);
$formatedResult[$i]['totalamount'] = explode(',', $result[$i]['totalamount']);
//Repeat with the other elements with similar formating
$i++;
}
echo '<pre>';
print_r($formatedResult); //Now shows the array you want
echo '</pre>';
要获取数组中sql select查询的所有结果,只需使用下面的fetch all函数
$userid = $_REQUEST['userid'];
$sql= "SELECT catid, catname, productid, prodimg,
GROUP_CONCAT(prodsize ORDER BY id ASC) as prodsize,
GROUP_CONCAT(prodcost ORDER BY id ASC) as prodcost,
GROUP_CONCAT(quantity ORDER BY id ASC) as quantity,
GROUP_CONCAT(datee ORDER BY id ASC) as datee,
GROUP_CONCAT(timee ORDER BY id ASC) as timee,
GROUP_CONCAT(totalamount ORDER BY id ASC) as totalamount
from cart WHERE userid ='$userid'";
$result = mysqli_query($con, $sql);
// Fetch all
$r=mysqli_fetch_all($result,MYSQLI_ASSOC);
print_r($r);
// Free result set
mysqli_free_result($result);