代码之家 › 专栏 › 技术社区 › usman610

Php Mysqli如何求两个不同条件的结果的和

mysqli php

-1

usman610 · 技术社区 · 6 年前

下面的查询工作,但它显示了两个不同的不同的命令完美的总和,我所做的 GROUP BY ORDER_ID (1000, 2000)

但我想要这个 (1000, 2000)

每个订单有不同的折扣率和不同的送货费用。 所以我才这么做 group by order_id 如果我删除 按订单id分组 按订单id分组

下面是我的问题我想做什么。

$totalX = "select sum(price) as price, coupon, city, delivery_type, order_id
from orders where date between '2018-09-12' and '2018-09-13' group by order_id";
$totalXx = $dba2->query($totalX);
while ($total = $totalXx->fetch_assoc()) {

    $couponX = "select coupon, price, percent from couponsAll where id = '".$total['coupon']."'";
    $couponXx = $dba->query($couponX);
    $coupon = $couponXx->fetch_assoc();

    $areaBX = "select * from countries_citiesALL where id = '".$total['city']."' ";
    $areaBXx = $dba->query($areaBX);
    $areaBXxx = $areaBXx->fetch_assoc();

    $area6X = "select * from delivery_typeALL where area = '".$total['city']."' and id = '".$total['delivery_type']."'";
    $area6Xx = $dba->query($area6X);
    $area6xXx = $area6Xx->fetch_assoc();
    $dchargezQ = $area6xXx['price'];

    if ($coupon['price'] >= 1 && $coupon['percent'] < 1) {
        /// this condition is if price based discount
        $priceAcoup = $total['price'] - $coupon['price'];
        $gtotalx = $priceAcoup + $areaBXxx['price'] + $dchargezQ;
        $gtotal = number_format($gtotalx, 3);
        echo '<font color="black" style=""><b>'.$gtotal.'</b></font>';
    } else {
        if ($coupon['price'] < 1 && $coupon['percent'] >= 1) {
            /// this condition is if percentage based discount
            $priceAcoup = $total['price'] - (($total['price'] * $coupon['percent']) / 100);
            $gtotalx = $priceAcoup + $areaBXxx['price'] + $dchargezQ;
            $gtotal = number_format($gtotalx, 3);
            echo '<font color="black" style=""><b>'.$gtotal.'</b></font>';
        } else {
            /// this condition is if there is no percentage or price based discount
            $gtotalx = $total['price'] + $areaBXxx['price'] + $dchargezQ;
            $gtotal = number_format($gtotalx, 3);
            echo '<font color="black" style=""><b>'.$gtotal.'</b></font>';
        }
    }
}

1 回复 | 直到 6 年前

usman610 6 年前

问题解决如下。

我变了 $weekTotal[] 在任何情况下 $weekTotal1[], $weekTotal2[], $weekTotal3[] $weekTotal1=array();, $weekTotal2=array();, $weekTotal3=array(); 上面的第二个查询循环,并得到它的工作。

谢谢你的帮助。

$__xz_rTorders1     = $dba2->query("SELECT count(distinct(order_id)) as count, order_id, date FROM orders_confirmed
group by WEEK(date), MONTH(date)
order by WEEK(date), MONTH(date)
");
while($__xz_rTordersa4 = $__xz_rTorders1->fetch_assoc()) {

$__xz_signupweek = $__xz_rTordersa4['date'];
/*start day*/
for($__xz_i = 0; $__xz_i <7 ; $__xz_i++) {
 $__xz_date = date('Y-m-d', strtotime("-".$__xz_i."days", strtotime($__xz_signupweek)));
 $__xz_dayName = date('D', strtotime($__xz_date));
 if($__xz_dayName == "Sun") {
//echo "<b>From:</b> ". date( "d/m/Y", strtotime($__xz_date))." / ";
$mstart_date=date( "Y-m-d", strtotime($__xz_date));
 }
}
/*end day*/
 for($__xz_i = 0; $__xz_i <7 ; $__xz_i++) {
 $__xz_date = date('Y-m-d', strtotime("+".$__xz_i."days", strtotime($__xz_signupweek)));
 $__xz_dayName = date('D', strtotime($__xz_date));
 if($__xz_dayName == "Sat") {
//echo "<b>To:</b> ". date( "d/m/Y", strtotime($__xz_date));
$mend_date=date( "Y-m-d", strtotime($__xz_date));
 }
 }

///////////////////***********///////////////////
$weekTotal1=array();
$weekTotal2=array();
$weekTotal3=array();
///////////////////***********///////////////////

$totalX= "select sum(price) as price, coupon, city, delivery_type, order_id
from orders_confirmed where date between '".$mstart_date."' and '".$mend_date."'
group by order_id
";
$totalXx= $dba2->query($totalX);
while ($total = $totalXx->fetch_assoc()){

$couponX= "select coupon, price, percent
from coupons
where id = '".$total['coupon']."'
";
$couponXx= $dba->query($couponX);
$coupon = $couponXx->fetch_assoc();

$areaBX= "select * from countries_cities
where id = '".$total['city']."' ";
$areaBXx= $dba->query($areaBX);
$areaBXxx= $areaBXx->fetch_assoc();

$area6X= "select * from delivery_type
where area = '".$total['city']."'
and id = '".$total['delivery_type']."'
";
$area6Xx= $dba->query($area6X);
$area6xXx= $area6Xx->fetch_assoc();
$dchargezQ= $area6xXx['price'];


if ($coupon['price'] >= 1 && $coupon['percent'] < 1) {
$priceAcoup1=$total['price']-$coupon['price'];
///////////////////***********///////////////////
$weekTotal1[] = $priceAcoup1+$areaBXxx['price']+$dchargezQ;
///////////////////***********///////////////////

}else if ($coupon['price'] < 1 && $coupon['percent'] >= 1) {
$priceAcoup2=$total['price']-(($total['price']*$coupon['percent'])/100);

///////////////////***********///////////////////
$weekTotal2[] = $priceAcoup2+$areaBXxx['price']+$dchargezQ;
///////////////////***********///////////////////

}else{
///////////////////***********///////////////////
$weekTotal3[] = $total['price']+$areaBXxx['price']+$dchargezQ;
///////////////////***********///////////////////

} 

} // End Of While Loop (2nd)
$weekTotal_val3 =  array_sum($weekTotal1);
$weekTotal_val4 =  array_sum($weekTotal2);
$weekTotal_val5 =  array_sum($weekTotal3);

$finalTOTAL=number_format($weekTotal_val3+$weekTotal_val4+$weekTotal_val5,3); 
echo '<font color="red" style=""><b>'.$finalTOTAL.'</b></font>';
} // End Of While Loop (1st)