There is an array of dates of this type:

Array ( [0] => 2016-08-08 [1] => 2016-08-15 [2] => 2016-08-15 [3] => 2016-08-22 [4] => 2016-08-22 [5] => 2016-08-29 [6] => 2016-08-29 [7] => 2016-09-05 [8] => 2016-09-05 [9] => 2016-09-12 [10] => 2016-09-12 [11] => 2016-09-19 [12] => 2016-09-19 [13] => 2016-09-26 [14] => 2016-09-26 [15] => 2016-10-03 [16] => 2016-10-03 [17] => 2016-10-10 [18] => 2016-10-10 [19] => 2016-10-17 [20] => 2016-10-17 [21] => 2016-10-24 [22] => 2016-10-24 [23] => 2016-10-31 [24] => 2016-10-31 [25] => 2016-11-07 [26] => 2016-11-07 [27] => 2016-11-14)

There is a request of this type:

 SELECT * FROM tables WHERE filter1 AND filter2 BETWEEN '{$date_s}' AND '{$date_do}' while($row = mysql_fetch_array($res)) { <?=echo 'Всего: '.$row['txt_tip']?> }

How to make it so that the dates are substituted in pairs in the request, and the number of records is displayed according to the runs. Those. the first date from the array is the beginning, the second is the end, the third is the beginning, the fourth is the end, etc.

As a result, we should get something like everything: 1, total: 15, total: ... I tried to deduce through for , but somehow it turned out to be very cumbersome and did not work quite correctly.

  • What does filter1 do in the query? - Arnial
  • Different conditions may be added by filters, but they may not if filters are not selected - ASYOU

3 answers 3

 $dates = ['2016-08-08', /*etc*/]; //изначальне представление дат $chunked_dates = array_chunk( $dates, 2 ); //попарно разбиваем даты $db = new mysqli( /*db connection params */); //подключаемся к бд //создаём подготовленное выражение $stmt = $db->prepare( "SELECT count(*) FROM tables WHERE filter2 BETWEEN ? AND ?" ); //привязаваем переменные к выражению $from = $to = null; $stmt->bind_param( 'ss', $from, $to ); foreach( $chunked_dates as list( $from, $to )){ // для каждой пары дат, выполняем запрос и выводим число строк. $stmt->execute(); $count = $stmt->get_result()->fetch_assoc()['count']; echo "от $from до $to всего $count.\n"; } //закрываем подготовленный запрос $stmt->close();

For brevity, I missed checking return values.

Addition:

stmt-> bind_param is a side effect function. It binds variable references to the prepared expression.

To work in a functional style, I can offer 2 options: limit this side effect, or get rid of it altogether.

The restriction option is to move the bind_param into the loop and unlink the parameters at the end of each iteration:

 foreach( $chunked_dates as list( $from, $to )){ $stmt->bind_param( 'ss', $from, $to ); $stmt->execute(); $count = $stmt->get_result()->fetch_assoc()['count']; echo "от $from до $to всего $count.\n"; $stmt->reset(); }

The second option is to reject prepared expressions and manually create a request for a couple of dates, but then you need to secure the parameters in advance.

 $dates = array_map( [ $db, 'real_escape_string' ], $dates ); //rest of code foreach( $chunked_dates as list( $from, $to ) ){ $query = "SELECT count(*) FROM tables WHERE filter2 BETWEEN $from and $to"; //execute query and print result }

The second option is easier to transfer to the functional style, but it dates additional loading on php (repeated interpolation of lines) and on the database (each time you need to re-parse sql).

  • and how to do it: //привязаваем переменные к выражению $from = $to = null; $stmt->bind_param( 'ss', $from, $to ); //привязаваем переменные к выражению $from = $to = null; $stmt->bind_param( 'ss', $from, $to ); Porsto, all the code in my functionality is more or less clear how to remake as a functional and I don’t understand how to do it - ASYOU
  • Completed the answer. - Arnial

How is it done humanly

 SELECT min(filter2) week, count(*) cnt FROM tables WHERE filter1 AND filter2 BETWEEN '2016-08-08' AND '2016-11-14' GROUP BY date_format(filter1, "%Y-%u")

(here it is assumed that the dates do not overlap, in contrast to the conditions set out in the question)

    Not quite the correct form of data at the entrance, you can easily make a mistake, it is more correct to have something like this:

     Array ( [0] => Array ( [from] => 2016-08-08 [to] => 2016-08-15 ) [1] => Array ( [from] => 2016-08-15 [to] => 2016-08-22 ) ... )

    In your case, I would do that.

     for ($i=0, $k = count($d); $i < $k; $i=$i+2) { echo "from ".$d[$i]." to ".$d[$i+1]."\n"; }

    Result

     from 2016-08-08 to 2016-08-15 from 2016-08-15 to 2016-08-22 from 2016-08-22 to 2016-08-29 from 2016-08-29 to 2016-09-05 from 2016-09-05 to 2016-09-12 from 2016-09-12 to 2016-09-19 from 2016-09-19 to 2016-09-26 from 2016-09-26 to 2016-10-03 from 2016-10-03 to 2016-10-10 from 2016-10-10 to 2016-10-17 from 2016-10-17 to 2016-10-24 from 2016-10-24 to 2016-10-31 from 2016-10-31 to 2016-11-07 from 2016-11-07 to 2016-11-14