fetch_array() not preserving ORDER BY from query

Question

this is my code:

<?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;
}

?>

Produces:

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

the table looks like:

well_id     int(11)

well_name   varchar(50)

last_pull   datetime

hrs_to_fill datetime

Which is not ordered by datetime field like it should be. Running the query on PHPMyadmin works fine, but this code is not... Any thoughts?

Thanks,

Luis

Answer 1

Your data is actually ordered; if you look closely, you will see that for each well it sorts based on date/time. The reason is that you have two loops, the inner loop gets run three times, one for each well.

You may have to move the inner loop after the outer loop, so that you populate the temp_wells table in the first loop and query it in the second.