I'm using Mysql, Pdo, Apache, InnoDB, php 5.4.16.
I need to display events >=CURDATE(). The events could have a duration of 1 day (tipo_id=1) or more (tipo_id=4). Each event has a date I stored in data_evento column as start date. For events having a duration of more than one day I use a fine_data_evento column where I stored the end date. Because not all the events have an end date I set fine_data_evento as NULL. So I have no problem to display every kind of event starting from today with single date or a "from-to" range of date. For example:
Title:Event 01
Date: 12 September 2019
========================
Title:Event 02
Date: From 12/09/2019 to 10/10/2019
========================
Title:Event 03
Date: 13 September 2019
The problem is when I try to display Event 02 the day after (13/09) its starting date (12/09 in the example), as SQL condition is data_evento>=CURDATE().
So the question is: how to continue to display events which starts before today and ends some days later, where all the others events start from today?
Maybe Do i need some if in SQL? I red other posts but I could not find an answer. So I can't understand which is a way to work well. I know it is simple but I can't. Thank you.
On the following I have some code.
tutti_eventi.php
....
$sql = 'SELECT `id_evento`,`titolo_evento`, `desc_evento`,`data_evento`,`fine_data_evento`, `nome_image`, `nome_categoria`,`comune_nome`,`nome_provincia`,`tipo_id` FROM `eventi`, `images`,`categoria`,`comuni`,`provincia`
WHERE
`id_image`=`id_img`
AND
`id_categoria`=`cat_id`
AND
`id_comune`=`com_id`
AND
`id_provincia`=`id_prov`
AND
`tipo_id` in (1,4)
AND
`data_evento` >= CURDATE()
ORDER BY
`data_evento` ASC';
....
tutti_eventi.html.php
....
while ($evento = $eventi->fetch(PDO::FETCH_ASSOC)) {
....
<div class="col-sm-12 col-md-12" style="padding-top:5px;">
<?php
if (isset($evento['fine_data_evento'])) {
$tipo=$evento['tipo_id'];
if ($tipo = 4) {
echo '<p><span class="glyphicon glyphicon-calendar" aria-hidden="true"></span>
<span class="glyphicon-class">';
echo '<span>Dal ';
Setlocale(LC_ALL, 'it_IT');
$date3= new DateTime($evento['data_evento']);
echo $date3->format('d/m/Y');
echo '</span>';
echo '<span> al ';
Setlocale(LC_ALL, 'it_IT');
$date4= new DateTime($evento['fine_data_evento']);
echo $date4->format('d/m/Y');
echo '</span>';
echo '</span></p>';
}
} else {
echo '<p><span class="glyphicon glyphicon-calendar" aria-hidden="true"></span>
<span class="glyphicon-class">';
echo '<span>';
Setlocale(LC_ALL, 'it_IT');
$date= new DateTime($evento['data_evento']);
echo $date->format('l');
echo '</span><br>';
echo '<span>';
Setlocale(LC_ALL, 'it_IT');
$date1= new DateTime($evento['data_evento']);
echo $date1->format('d F Y');
echo '</span>';
echo '</span><br>';
echo '<span class="glyphicon glyphicon-time" aria-hidden="true"></span>
<span class="glyphicon-class">';
$date2= new DateTime($evento['data_evento']);
echo $date2->format('H'.':'.'i');
echo '</span></p>';
}
echo '</div>';
echo '<div class="col-sm-12 col-md-12">'.$evento['desc_evento'].'</div>';
echo '</div>';
}
?>
Thanks to @GMB in particular I am very close to the solution. I added COALESCE(fine_data_evento, data_evento) >= CURDATE() in my SQL query. But I need to ORDER BY ASC. When I use DESC it works fine: Event 2 is displayed and Events are displayed starting by the last date on the top of the page, while Event 2 is the first at the bottom. When I use ASC instead Event 2 can't display and the events are correctly displayed starting by today on the top. Watch at the new query:
$sql = 'SELECT `id_evento`, `titolo_evento`, `desc_evento`, `data_evento`, `fine_data_evento`, `nome_image`, `nome_categoria`, `comune_nome`, `nome_provincia`, `tipo_id` FROM `eventi`, `images`, `categoria`, `comuni`, `provincia`
WHERE
`id_image`=`id_img`
AND
`id_categoria`=`cat_id`
AND
`id_comune`=`com_id`
AND
`id_provincia`=`id_prov`
AND
`tipo_id` in (1,4)
AND
COALESCE(`fine_data_evento`, `data_evento`) >= CURDATE()
ORDER BY `data_evento` ASC';
And the following is the solution. I red at this post:
How to order by 2 columns combining COALESCE?
The solution is to add:
COALESCE(fine_data_evento, data_evento) >= CURDATE() - INTERVAL 1 DAY
ORDER BY COALESCE (fine_data_evento) ASC, data_evento ASC
It works: the Event 2 is placed at the bottom of the page when its start date is before today, while events with null end date are sorted by today on the top of the page.
Thank you to all
