Actually while writing this I just realized that this will be a horrendous task to do if you keep these day month and year columns separated. Finding a >= current date means you have to constantly check the year, day and month and will create a horrible query. The problem is you cannot check for a day >= 25 for example, since Feb 1 is considered > Jan 25. You will likely want to create a datetime datatype to use instead.
This is still doable, the SQL itself would be a nightmare to write, and if PHP is chosen instead it will effectively create what the SQL should be anyway with a mktime to create the datetime, and then compare it to today. This would be easier than the query IMO.
So you need to decide first if you want to convert the structure of the database to use a datetime type, or if you want to put the work on PHP to do the comparisons. IMO I'd convert the SQL as you can then issue a simple WHERE event_date >= NOW() (where event_date would be the new datetime type).
I realize again I just had a blond moment. It doesn't really matter if they are separate either since we can query the group together to construct an actual datetime.
$query = "SELECT event, company, location, city, month, day, year FROM calendar_event WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%m%e') >= NOW() ORDER by month,day ASC";
The format is very specific. YYYYmmd is exactly what that wants. So the month must be 0 led and with the above format the day must not be 0 led (use %d for 0 led day instead of %e, and %c will go without the 0 lead month instead of the %m).
That should work so long as you match your format
%Y%m%e is the format specifier. That means 2012011 is valid for a date January 1 2012. The %m and %e is what may need to be changed depending on if you are working with direct numbers or strings for those. If they are integers, you likely want the format %Y%c%e, while if they are strings it could be %Y%m%d.
Add into the select , STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') AS convertedDate, and print the $query_row['convertedDate'] somewhere. Does this show the date as defined in the year month and date, or does this show nothing, January 1, 1970/December 31, 1969, or some other bad datetime?
No, that needs to be added to the selection fields, not the where clause. The entire where clause can be removed if necessary.
My assumption is that it will return either nothing or epoch. This indicates that the format is not valid for the specifier provided, and for that we'd need to see what exactly the month, day, year is and its datatypes.
since i'm not as good at PHP and queries Ive added a column in my table called 'sdate' which is the start date in regular mm/dd/yyyy format.
Does this make the query easier??
how should I adjust it - thank you in advance for all your help
Is this datatype datetime type, or is it a text/varchar type? Make it a datetime type if you can, since then you can use a simple WHERE sdate >= NOW() in the where clause, while a string would need converting to a date.