MySQL запрос для извлечения данных с использованием диапазона дат

Этот запрос дает правильный результат в MySql Workbench —

SELECT tbl_visit_info.visitor_id,tbl_visit_info.retailer_id,tbl_retailer.retailer_shop_name,
SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment,
tbl_visit_info.date as visit_date
FROM tbl_visit_info,tbl_retailer
where tbl_visit_info.retailer_id=tbl_retailer.retailer_id
and tbl_visit_info.visitor_id=80
group by visitor_id,retailer_id
having date_format(tbl_visit_info.date, '%Y-%m-%d') BETWEEN '2018-07-01' AND '2018-09-02';

Но когда я хочу передать переменные вместо 80 и диапазон дат в качестве переменной, то это не дает никакого результата. Запрос is-

SELECT tbl_visit_info.visitor_id,tbl_visit_info.retailer_id,tbl_retailer.retailer_shop_name,
SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
FROM tbl_visit_info,tbl_retailer
where tbl_visit_info.retailer_id=tbl_retailer.retailer_id
and tbl_visit_info.visitor_id='".$_GET["sr_id"]."'
group by visitor_id,retailer_id having date_format(date, '%Y-%m-%d') BETWEEN
'".$_GET["start_date"]."' AND '".$_GET["end_date"]."'"

Этот запрос не дает никакого результата, когда я пишу код на PHP.

0

Решение

Вы должны использовать фильтр даты в предложении where вместо использования:

SELECT tbl_visit_info.visitor_id,tbl_visit_info.retailer_id,tbl_retailer.retailer_shop_name,
SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
FROM tbl_visit_info,tbl_retailer
where tbl_visit_info.retailer_id=tbl_retailer.retailer_id
and tbl_visit_info.visitor_id='".$_GET["sr_id"]."'
and date_format(date, '%Y-%m-%d') BETWEEN
'".$_GET["start_date"]."' AND '".$_GET["end_date"]."'"group by visitor_id,retailer_id
0

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]