我在使用mysql时遇到小问题。我有一组日期范围。例如:从2011年1月1日至2011年6月1日,该价格范围为:每天4500美元。下一个日期范围从2001年1月7日到2011年12月1日,该价格范围是:每天$ 300

如果有人在例如05/05/2011和10-07-2011之间注册了一天,我需要添加天数的价格。在这两个日期范围之间,价格不同。如何分别添加

这是



谢谢

最佳答案

你可以做一个循环...

并且每次您增加日期,然后进行查询以知道此日期值落在哪个列中。因此,您可以添加相应的金额。

为此,您需要管理日期,例如一个月中有多少天。如果年份更改,则还需要将月份设置为1。

我已经编写了一个代码....以显示日历。你可以按照要求进行大修



$strt_date = '01-04-2011';
$end_date = '05-07-2011';
$date_array = array();
$date_array = explode('-',$strt_date);
$back_strt_date = $date_array[2]."-".$date_array[0]."-".$date_array[1];// YYYY-MM-DD
$front_strt_date = $date_array[1]."-".$date_array[0]."-".$date_array[2];// MM-DD-YYYY
$date_array = array();
$date_array = explode('-',$end_date);
$back_bk_date = $date_array[2]."-".$date_array[0]."-".$date_array[1]; // YYYY-MM-DD
$front_end_date = $date_array[1]."-".$date_array[0]."-".$date_array[2]; // MM-DD-YYYY
// fetching data for selected date interval
//$rs_couse_cnt = get_course_cnt($back_strt_date,$back_bk_date);
if($rs_couse_cnt =='No record Found'){
    echo "No Records Found.";
    exit;
}
// converting string in to TIMESTAMP
$sdate = strtotime($front_strt_date);
$edate = strtotime($front_end_date);

//getting starting and ending month of date...
$st_dt = array();
$en_dt = array();
    // exploding date string in to array
    $st_dt = explode('-',$strt_date);
    $en_dt = explode('-',$end_date);

    // assigning month value to variables...
    $st_mon = $st_dt[0];
    $en_mon = $en_dt[0];

    // calculating month diff..
    $mon_diff = $en_mon - $st_mon;

    // assinging year value to variables....
    $st_year = $st_dt[2];
    $en_year = $en_dt[2];

//converting TIMESTAMP into desired date format...
$st_date = date('F jS Y',$sdate);
$en_date = date('F jS Y',$edate);

echo " <center><h2>$st_date - $en_date</h2></center>";
//echo " <br />start month is $st_mon & end month is $en_mon & month diff is $mon_diff";
//echo " <br />start year is $st_year & end year is $en_year";
$ttl_cnt =0;
for($i=$st_mon;($st_year<=$en_year);$i++){
    $end = $en_mon+1;
    if($i == $end && $st_year == $en_year){
        break;
    }
    // this will continue until all months are completed
    $st_mon_time_stamp = mktime(0,0,0,$i,1,$st_year);
    echo "<br /><br /><center> <h3> ".date("F Y",$st_mon_time_stamp)."</h3></center>";
    $no_of_days = date("t",$st_mon_time_stamp);
    $str_cal ='<table border="1px" width="100%">
                <thead>
                    <th>Sunday</th>
                    <th>Monday</th>
                    <th>Tuesday</th>
                    <th>Wednesday</th>
                    <th>Thursday</th>
                    <th>Friday</th>
                    <th>Saturday</th>
                </thead>
                <tbody>';
    $cnt = 1;
    $week = 0;
    $mon_cnt = 0;
    for($j=1;$j<=$no_of_days;$j++){
        if($cnt >= $no_of_days){
            break;
        }
        if($cnt == 1){
            // for first row....
            $time_stamp = mktime(0,0,0,$i,$cnt,$st_year);
            $str_cal .='<tr>';
            if(date("l",$time_stamp) == 'Sunday'){
                $space = 0;
            }
            else if(date("l",$time_stamp) == 'Monday'){
                $space = 1;
            }
            else if(date("l",$time_stamp) == 'Tuesday'){
                $space = 2;
            }
            else if(date("l",$time_stamp) == 'Wednesday'){
                $space = 3;
            }
            else if(date("l",$time_stamp) == 'Thursday'){
                $space = 4;
            }
            else if(date("l",$time_stamp) == 'Friday'){
                $space = 5;
            }
            else if(date("l",$time_stamp) == 'Saturday'){
                $space = 6;
            }
            $dis = 7 - $space; //exit;
            while($space != 0){
                // this will display spaces in first row...
                $str_cal .='<td>&nbsp;</td>';
                $space--;
            }
            while($dis != 0){
                // this will display calender date in first row...
                if(strlen($i) == 1 && strlen($cnt)==1){
                    $date = $st_year.'-0'.$i.'-0'.$cnt;
                }
                else{
                    $date = $st_year.'-'.$i.'-'.$cnt;
                }
                $flg = 0;
                for($rs=0;$rs<count($rs_couse_cnt);$rs++){
                    if($rs_couse_cnt[$rs]['EnrolledDate'] == $date){
                        $time_stmp = strtotime($date);
                        $str_cal .='<td>'.date('j S',$time_stmp).'<br /><br />Count : <a href="light_box_course.php" onClick="$.showAkModal(\'light_box_course.php?type=list&session_id='.$session_id.'&campus_id='.$campus_id.'&strt_date='.$rs_couse_cnt[$rs]['EnrolledDate'].'&end_date='.$rs_couse_cnt[$rs]['EnrolledDate'].'\',\'\',500,500);return false;">'.$rs_couse_cnt[$rs]['CourseCount'].'</a></td>';
                        $cnt++;
                        $mon_cnt += $rs_couse_cnt[$rs]['CourseCount'];
                        $flg = 1;
                        break;
                    }
                }

                if($flg == 0){
                    $time_stmp = strtotime($date);
                    $str_cal .='<td>'.date('j S',$time_stmp).'<br /><br />&nbsp;</td>';
                    $cnt++;
                }
                $dis--;
            }
            $str_cal .='</tr>';
        }
        else{
            // for rest of the rows...
            $str_cal .='<tr>';
            for($w=0;$w<7;$w++){
                if($cnt<=$no_of_days){
                    if(strlen($i) == 1){
                        $date = $st_year.'-0'.$i.'-'.$cnt;
                    }
                    else{
                        $date = $st_year.'-'.$i.'-'.$cnt;
                    }
                    $flg = 0;
                    for($rs=0;$rs<count($rs_couse_cnt);$rs++){
                        if($rs_couse_cnt[$rs]['EnrolledDate'] == $date){
                            $time_stmp = strtotime($date);
                            $str_cal .='<td>'.date('j S',$time_stmp).'<br /><br />Count :<a href="light_box_course.php" onClick="$.showAkModal(\'light_box_course.php?type=list&session_id='.$session_id.'&campus_id='.$campus_id.'&strt_date='.$rs_couse_cnt[$rs]['EnrolledDate'].'&end_date='.$rs_couse_cnt[$rs]['EnrolledDate'].'\',\'\',500,500);return false;">'.$rs_couse_cnt[$rs]['CourseCount'].'</a></td>';
                            $flg = 1;
                            $cnt++;
                            $mon_cnt += $rs_couse_cnt[$rs]['CourseCount'];
                            break;
                        }
                    }
                    if($flg == 0){
                        $time_stmp = strtotime($date);
                        $str_cal .='<td>'.date('j S',$time_stmp).'<br /><br />&nbsp;</td>';
                        $cnt++;
                    }
                }
                else{
                    $str_cal .='<td>&nbsp;</td>';
                }
            }
            $str_cal .='</tr>';
        }
    }
    $ttl_cnt += $mon_cnt;
    if($mon_cnt != 0){
        $str_cal .='<tr>
                        <td> Monthly Count :</td>
                        <td colspan="6"><a href="light_box_course.php" onClick="$.showAkModal(\'light_box_course.php?type=list&session_id='.$session_id.'&campus_id='.$campus_id.'&strt_date='.$st_year."-".(strlen($i)==1?"0".$i:$i)."-"."01".'&end_date='.$st_year."-".(strlen($i)==1?"0".$i:$i)."-".$no_of_days.'\',\'\',500,500);return false;">'.$mon_cnt.'</td>
                    </tr>';
    }else{
        $str_cal .='<tr>
                        <td> Monthly Count :</td>
                        <td colspan="6">'.$mon_cnt.'</td>
                    </tr>';
    }
        $str_cal .='</tbody>
                </table>';
    if($i == 12){
        $i=0;
        $st_year ++;
    }
    echo $str_cal;
}


?>

关于php - 如何在MySQL中求和不同的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6591741/

10-12 01:28
查看更多