Display Upcoming Events with PHP from MySQL query

Question

I am working on a MySQL query where display only upcoming events. But I got a blank screen every time and displaying in the past event list.

Today = 22 July 2018,
Upcoming event date (eventID) = 29 July 2018

My query is:

SELECT * FROM event WHERE eventDate > CURDATE() ORDER BY STR_TO_DATE(eventDate,'%d %F %Y')

PHP Code (without css):

$result = mysqli_query($con, $sql);


                                    while($row = mysqli_fetch_assoc($result)) { ?>

<?php echo $row['eventName'] . "<br/>" ?>
<?php echo $row["eventDate"] . "<br/><br/>" ?>
<?php } ?>

Database Table:

eventID int(11) AUTO_INCREMENT
eventName varchar(255)
eventDate varchar(255)

I already tried with CURRENT_DATE() , NOW() & >=

As I told my upcoming event shows in the past list, with all other events. eventDate < CURDATE()

Your SQL suggests event date is a string rather than a date column. What are your error logs? What is your PHP? — Martin, Jul 22 '18 at 18:30
Also [Show your table](https://stackoverflow.com/questions/898688/how-to-get-database-structure-in-mysql-via-query) — Martin, Jul 22 '18 at 18:37
voting this as a typo, Martin's right about your most likely storing as anything other than a DATE type which you should and will help you a lot and won't make you tear out your hair, as it were. — Funk Forty Niner, Jul 22 '18 at 18:39
@FunkFortyNiner a recent study of developers found that those who work with MySQL / PHP have a 23% more likely chance of being bald. I think you just found a cause! — Martin, Jul 22 '18 at 18:41
@Martin Heh; I've had my share, just not "code-related" LOL! — Funk Forty Niner, Jul 22 '18 at 18:42
@FunkFortyNiner ( *Stack Overflow users above 25k rep have a 14% more likely chance of being bald!* `;-)` ) — Martin, Jul 22 '18 at 18:43
@ArnabDasguptaADG while your code is not great, it's not the cause of the issue here. Please can you show your MySQL table structure as per my second comment. Thanks. — Martin, Jul 22 '18 at 18:43
@Martin I take after my Mom ;-) my Dad was, and I'm having a pretty good run at it. — Funk Forty Niner, Jul 22 '18 at 18:44
@FunkFortyNiner best of luck with your good run. On a positive note -- baldness is [also] attributed to high testosterone levels so, take it as a plus sign `:-)` . (But like databases not working; there's usually a host of factors) — Martin, Jul 22 '18 at 18:51
Possible duplicate of [mysql date comparison with date\_format](https://stackoverflow.com/questions/13507642/mysql-date-comparison-with-date-format) — Martin, Jul 22 '18 at 18:53
Your problem is that you're comparing a `DATE` with a `STRING` . Results will be unpredictable, *at best*. You need to rewrite your table structure to set the `eventDate` as a DATE rather than a VARCHAR column. — Martin, Jul 22 '18 at 18:54

score 1 · Accepted Answer · answered Jul 22 '18 at 19:00

Your problem is that you're comparing a DATE with a STRING . Results will be unpredictable, at best. You need to rewrite your table structure to set the eventDate as a DATE rather than a VARCHAR column.

eventDate varchar(255)  <== varchar is the WRONG type here; you want DATETIME or DATE or similar.

Please READ HERE about how to convert your columns correctly to DATE type.

I have rewritten your code for you, because I feel that this place should take some tips from CodeReview.

$sql = "SELECT eventName, eventDate FROM event WHERE eventDate > CURDATE() ORDER BY eventDate";

$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)) { 
    echo htmlentities($row['eventName']) . "<br/>"
    echo $row["eventDate"] . "<br/><br/>"
 } 
?>

Display Upcoming Events with PHP from MySQL query

1 Answers1