Show future event dates

[p2492]

I am trying to only show the events occurring today and on future dates. The problem is that the day, month, and year in my database are separate and with the code I have now it isn't treating them as one, so the month will show future months, the day will show future days, and the year will show future years. For example, today is 01.17.2011 and the next day (that should be displayed as well) is 02.03.2011, but with the code I have it only shows 01.17.2011 because the day in the other date is less than 17. I hope I am making sense.. any help would be great

 

<?php
    // connect to the RDBMS
    $db = mysql_connect("$site","$user","$pass") 
        or die_now("Could not connect to database server. Check passwords and sockets");

    // select the database
    mysql_select_db("$database",$db) 
        or die_now("Could not select database $database. Check database name.");

    // select all the shows in the database
    $result = mysql_query("select show_id, month, day, year, location, venue
        from $database_table
	where month >= month( now())
	and day >= day(now())
	and year >= year(now())
	order by year, month, day limit 5",$db) 
        or die_now("Could not select shows");

    // output the current shows
echo("<div class='shows_place'>\n");        
    while($row = mysql_fetch_array($result)) {
        $the_id = $row["show_id"];
        $the_month = $row["month"];
        $the_day = $row["day"];
        $the_year = $row["year"];
        $the_location = $row["location"];
        $the_venue = $row["venue"];

        // shows
        echo("<table><tr><td>" . "$the_month" . "." . "$the_day" . "." . "$the_year" . " - </td>");
        echo("<td>" . "$the_venue" . " - </td>");
        echo("<td>" . "$the_location" . "</td>");
    }
    echo("</tr></table></div>");
?>

[requinix]

Any particular reason you split the date up? If you combined them in YYYY-MM-DD format this query would be really easy.

 

But otherwise,

year > this year OR
year = this year AND month > this month OR
year = this year AND month = this month AND day >= this day

[p2492]

Any particular reason you split the date up? If you combined them in YYYY-MM-DD format this query would be really easy.

 

But otherwise,

year > this year OR
year = this year AND month > this month OR
year = this year AND month = this month AND day >= this day

 

The script was already set up this way, I am just editing it. I don't know much about PHP and databases if you couldn't tell. That didn't seem to work, it didn't show anything. Thanks for your time!

[p2492]

Any particular reason you split the date up? If you combined them in YYYY-MM-DD format this query would be really easy.

 

I changed the date to YYYY-MM-DD. How do I go about showing only current and future dates now? Thanks

[Pikachu2000]

SELECT `field1`, field2` FROM `table` WHERE `date_field` > CURDATE()

[p2492]

SELECT `field1`, field2` FROM `table` WHERE `date_field` > CURDATE()

 

Hmm that doesn't seem to be working either, it's still showing the past dates.

'date' is the name of the date field. This is what I have..

 

    $result = mysql_query("select show_id, month, day, year, location, venue, DATE_FORMAT(`date`,'%m.%d.%y') AS date
        from $database_table
	WHERE 'date' > CURDATE()
	ORDER BY date ASC limit 5",$db) 
        or die_now("Could not select shows");

[Pikachu2000]

I just tested that query structure and syntax locally, and it returns the expected values. Is the field set to DATE or DATETIME data type?

[PFMaBiSmAd]

WHERE 'date' > CURDATE()

 

^^^ That's testing if the string 'date' (not your date column) is greater than the curdate(). Remove the single-quotes from that. Also, by using date as the alias name for the date_format() term, your WHERE and ORDER BY terms will use the formatted value. Pick a different alias name for the date_format() term.

[PFMaBiSmAd]

Slight correction to the above post. The WHERE clause will use your date column (after removing the single-quotes.) The ORDER BY will use the date_format() value through the alias date, which won't give the desired results.

[p2492]

Okay I got it working, thanks for your help guys! I'm slowly learning