CODEWITHSUNDEEP
phpmysqlalgorithmdate
Gabriel
Gabriel

Reputation: 792

Select latest date with non-null values, given weekends and work-free days in PHP

So, my MySQL data is organized like this:

>   id_nom  simbol  data              val             close           min       
>   3        BET    2010-02-26  5328.4502          5328.4502    5317.2400

And a form from which to choose the starting and ending date, based on selects: first select is date, second select is month and third select is year.

The problem is with the dates that are weekends and holidays, the values for these days are the last reported value on business days. So, for example, friday for BET is 5328.4502 and sunday, it will be the same value as friday, although there is no date for Sunday or Saturday or any other Holiday in the DB.

My solution was the following:

I`ve made a function to select the starting and ending date given a date. Here it is:

function x_week_range($date) {
  $ts = strtotime($date);
  $start = (date('N', $ts) == 1) ? $ts : strtotime('Last Monday', $ts);
  return array(date('Y-m-d', $start),
    date('Y-m-d', strtotime('Next Sunday', $start)));
}

And a function to extract the dates out of a week, using array slice. Here it is:

function array_slice_assoc ($array, $key, $length, $preserve_keys = true)
{
    $offset = array_search($key, array_keys($array));

    if (is_string($length))
        $length = array_search($length, array_keys($array)) - $offset;

    return array_slice($array, $offset, $length, $preserve_keys);
}

And used them both like this:

list($start_day_week,$end_day_week)=x_week_range($data_start);
list($start_day_week2,$end_day_week2)=x_week_range($data_end);


$strSql="SELECT * FROM ind_cotatii WHERE id_nom=3 AND `data` >= '".$data_start."' AND `data` <= '".$data_end."' ";

$objDbResult = $objDatabase->Query($strSql);

while($mixRow = $objDbResult->FetchArray())
    {
        $datInd[$mixRow['data']]=$mixRow['val'];
    }
    if(isset($datInd[$data_start]))
    {
        $first_value=$datInd[$data_start];
    }
    if(isset($datInd[$data_end]))
    {
        $last_value=$datInd[$data_end];
    }


    if(!isset($first_value))
    {
        $arr=array_slice_assoc($datInd,$start_day_week,6,true);
        $first_value=end(array_filter($arr));
        var_dump('calc_we1',$arr,$start_day_week,$first_value);
    }

    if(!isset($last_value))
    {
        $arr2=array_slice_assoc($datInd,$start_day_week2,6,true);
        $last_value=end(array_filter($arr2));
        var_dump('calc_we2',$arr2,$start_day_week2,$last_value);                
    }

The $arr specifically $arr2 will contain an array as follows:

Array(6) {
  ["2010-12-13"]=>
  string(9) "5155.9800"
  ["2010-12-14"]=>
  string(9) "5154.6802"
  ["2010-12-15"]=>
  string(9) "5146.3398"
  ["2010-12-16"]=>
  string(9) "5138.5098"
  ["2010-12-17"]=>
  string(9) "5149.9800"
  ["2010-12-20"]=>
  string(9) "5133.9302"
}

So basically, I choose the last day of a week which is not null using end and array_filter.

My problem, more specifically, is as follows:

If I choose the starting date: 12-12-2010 sunday, the $arr will contain dates for the week starting with 13 monday, whereas I need dates starting with the last monday which is 6.

Any ideas why this happens, also do you think I`ve over complicated it a little bit? Do you have any easy ways?

Upvotes: 1

Views: 220

Answers (1)

Gabriel
Gabriel

Reputation: 792

I've modified my query as follows:

SELECT * FROM ind_cotatii WHERE id_nom=3 AND `data` >= 'DATE_SUB('".$data_start."',INTERVAL 1 WEEK)' AND `data` <= '".$data_end."' ";

It works because it takes into account the previous week, thus the last value from that week, the logic is obvious. Like it was in the original form, if I entered sunday, due to the query, it would have chosen 13 and thats next monday, totaly wrong.

Upvotes: 0

Related Questions