php - fetch_array() 不保留查询中的 ORDER BY

Question

这是我的代码:

<?php
header("Expires: Mon, 26 Jul 1990 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");

//TESTING CONNECT
include 'connect.php';
$mysqli = connectDB();
getWells(2, $mysqli);

function getWells($company,$mysqli){
//include 'connect.php';
define("MAX_TOP", 96);//96 INCHES == 8 FEET
define("BARRELS_PER_INCH", 1.67);

//$company = $_GET['company'];
//$mysqli = connectDB();

$count = 0;
$sql = "SELECT wells.id, name, top, bottom, last_pulled, bbl_per_hour
        FROM wells, history
        WHERE company_id ='$company'
        AND wells.id = well_id
        AND last_record = 1;";

if($result = $mysqli->query($sql)) {
    while($row = $result->fetch_array()){
        $well_name = $row['name'];
        $well_id = $row['id'];
        $last_bottom = $row['bottom'];
        $last_pulled = $row['last_pulled'];
        $bbl = $row['bbl_per_hour'];

        $projected_fill = MAX_TOP - $last_bottom;
        $projected_barrels = $projected_fill * BARRELS_PER_INCH;
        $time_to_fill = $projected_barrels/$bbl; 
        $exploded_time = explode('.', $time_to_fill);
        $hours = $exploded_time[0];                     
        $frac_hours = '.'.$exploded_time[1];
        $minutes = floor($frac_hours*60);
        $projected = strtotime($last_pulled." +".$hours." hours"." +".$minutes." minutes");            
        $projected_datetime = date("Y-m-d  H:i:s",$projected);    

        $insert = "INSERT INTO temp_wells (well_id,well_name,last_pull,hrs_to_fill)
                   VALUES ('$well_id','$well_name','$last_pulled','$projected_datetime');";
        if(!$result2 = $mysqli->query($insert)){
            return "There was a problem inserting data into databse. Contact Larsoon Computer Services. - GetWells.php, Line 57 ".$mysqli->error;
        }
        else{
            $get_temp = "SELECT * FROM temp_wells
                         ORDER BY hrs_to_fill ASC;";
            if(!$result3 = $mysqli->query($get_temp)){
                return "There was a problem retrieving data. Contact Larson Computer Services. - GetWells.php, Line 63 ".$mysqli->error;
            }
            else{
                //echo var_dump($result3);
                while ($row1 = $result3->fetch_array()) {
                    $o_well_id = $row1['well_id'];
                    $o_well_name = $row1['well_name'];
                    $o_pull_date = $row1['hrs_to_fill'];

                    //TESTING DATA OUTPUT
                    echo $o_well_id;
                    echo '<br>';
                    echo $o_well_name;
                    echo '<br>';
                    echo $o_pull_date;
                    echo '<br>';
                    echo '<br>';

                    //CREATE THE COUNTDOWN   
                    $date = date('U',  strtotime($o_pull_date));
                    $difference = $date - date('U');
                    $diff_days = floor($difference/(24*60*60));
                    $diff_hours = floor($difference % (24 * 60 * 60) / 3600);
                    $diff_min = floor(($difference % (24 * 60 * 60) % 3600)/60);
                    $diff_secs = floor((($difference % (24 * 60 * 60) % 3600)%60)/1);

                    if($diff_days <= 0){
                        $countdown =  "$diff_hours hrs $diff_min min";
                    }
                    else{
                        $countdown =  "$diff_days days $diff_hours hrs $diff_min min";
                    }
                    if($count == 0) {
                        $data_str = $o_well_id.'|'.$o_well_name.'|'.$countdown;
                        $count++;
                    }
                    else {
                        $data_str = $data_str.'|'.$o_well_id.'|'.$o_well_name.'|'.$countdown;
                    }
                }
            }
        }
        $truncate = "TRUNCATE TABLE temp_wells;";
        if(!$result4 = $mysqli->query($truncate)){
            return "There was a problem truncating table. Contact Larson Computer Services. ".$mysqli->error;
        }
    }
}//END OF IF
if($data_str == null && $data_str == ""){
    $data_str = 0;
}
return $data_str;
}

?>

产生:

1
ATESTWELL
2012-08-17 14:55:37 

3
HAMLET 1-11H
2012-08-17 17:40:00

4
HAMLET 2-11H
2012-08-17 18:47:14

8
DANIEL 1-33H
2012-08-17 13:15:39

6
DANIEL 21-33H
2012-08-19 13:47:16

9
FAVER 1-29H
2012-08-17 14:31:00

表格看起来像:

well_id     int(11)

well_name   varchar(50)

last_pull   datetime

hrs_to_fill datetime

它没有像它应该的那样按日期时间字段排序。在 PHPMyadmin 上运行查询工作正常，但这段代码不是......有什么想法吗？

谢谢，

路易斯

Answer 1

您的数据实际上是有序的；如果仔细观察，您会发现每个孔都根据日期/时间进行排序。原因是您有两个循环，内部循环运行三次，每个孔一次。

您可能必须将内循环移到外循环之后，以便在第一个循环中填充 temp_wells 表并在第二个循环中查询它。