...

View Full Version : how to echo only certain dates



jchrisphonte
01-25-2012, 04:34 PM
Hello All

Can anyone shed some light into how I could adjust the PHP below to show only events that are today's date or later?

Essentially I'm looking to adjust the first script to only show events occurring today or later, currently it shows all events in the table(past,present, & future).




<?php
require 'connect.inc.php';

$query = "SELECT event, company, location, city, month, day, year FROM calendar_event ORDER by month,day ASC";

if ($query_run = mysql_query($query)) {
while ($query_row = mysql_fetch_assoc($query_run) ) {
$event= $query_row ['event'];
$company= $query_row ['company'];
$location= $query_row ['location'];
$city= $query_row ['city'];
$month= $query_row ['month'];
$day= $query_row ['day'];
$year= $query_row ['year'];



echo '<div class="where">'.$event.'</div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style=margin-top:5px;margin-bottom:5px;"/>';
}

}
else{
echo mysql_error();
}
?>

Fou-Lu
01-25-2012, 04:47 PM
What datatype are month, day, year stored as in the database?

jchrisphonte
01-25-2012, 06:56 PM
hello Fou lu,

So the date is save in 3 columns - 2digit day , 2 digit month, and 4 digit year.

day, month, & year

25,1,2012

Fou-Lu
01-25-2012, 07:13 PM
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).

jchrisphonte
01-25-2012, 07:21 PM
i have a column with the date in one cell if that helps -- I have a column in excel that i can re-add that cells format is mm/dd/yyyy

I can add a column in mytable just for this if it makes this query easier.

Fou-Lu
01-25-2012, 07:43 PM
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

jchrisphonte
01-25-2012, 07:56 PM
thanks for this --

I'm trying to implement this now, I'm a newbie of sorts.

do i rename "%Y%m%e" what ever i would like?

also i dont understand the %d, %e....etc

also what do you mean by match format?? I copied and pasted this:

WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%m%e') >= NOW() into my

$query= "SELECT...so it appears as your example above


thank you for your help on this.

Fou-Lu
01-25-2012, 08:19 PM
%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.

jchrisphonte
01-25-2012, 08:26 PM
they are integers - ive changed it to %Y%c%e and now the data shows on my local host yet it still shows the older dated events...

Here is what I have..



<?php
require 'connect.inc.php';

$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') >= NOW()ORDER by month,day ASC";

if ($query_run = mysql_query($query)) {
while ($query_row = mysql_fetch_assoc($query_run) ) {
$id= $query_row ['id'];
$event= $query_row ['event'];
$company= $query_row ['company'];
$location= $query_row ['location'];
$city= $query_row ['city'];
$month= $query_row ['month'];
$day= $query_row ['day'];
$year= $query_row ['year'];


$urlevent = str_replace(" ","_", $event);

echo '<div class="where"><a href="http://www.hedgevent.com/details/'.$urlevent.'-'.$id.'.html" class="event">'.$event.'</a></div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style= margin-top:5px;margin-bottom:5px;/>';
}

Fou-Lu
01-25-2012, 08:30 PM
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?

jchrisphonte
01-25-2012, 08:43 PM
i get an error message.. it says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS convertedDate, >= NOW()ORDER by month,day ASC' at line 1



$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') AS convertedDate, >= NOW()ORDER by month,day ASC";



if ($query_run = mysql_query($query)) {
while ($query_row = mysql_fetch_assoc($query_run) ) {
$id= $query_row ['id'];
$event= $query_row ['event'];
$company= $query_row ['company'];
$location= $query_row ['location'];
$city= $query_row ['city'];
$month= $query_row ['month'];
$day= $query_row ['day'];
$year= $query_row ['year'];
$converteddate= $query_row['convertedDate'];


$urlevent = str_replace(" ","_", $event);

echo '<div class="where"><a href="http://www.hedgevent.com/details/'.$urlevent.'-'.$id.'.html" class="event">'.$event.'</a></div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style= margin-top:5px;margin-bottom:5px;/>';
}

Fou-Lu
01-25-2012, 08:50 PM
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.

jchrisphonte
01-25-2012, 09:09 PM
hey Fou-Lu

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

jchrisphonte
01-25-2012, 09:35 PM
just as a note -- was researching date and my data does not have leading zeros in the date

Fou-Lu
01-25-2012, 09:40 PM
hey Fou-Lu

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.

jchrisphonte
01-25-2012, 09:47 PM
updated the type to datetime...

getting a blank page with the below code..



$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE sdate >= NOW()ORDER by month,day ASC";

Fou-Lu
01-25-2012, 10:12 PM
Put a space between now() and order. Methinks it will not like having no space there, so the white screen results are caused by a failed query on a syntax error, but your error reporting is closed so it will not show that on screen.
Also, I assume sdate has data in it right?

jchrisphonte
01-25-2012, 10:17 PM
added the space and refreshed -- still nothing.

all sdate fields are filled in the table.

Fou-Lu
01-25-2012, 10:20 PM
Issue a create table statement against your database and post it here: SHOW CREATE TABLE calendar_event

jchrisphonte
01-25-2012, 10:27 PM
im sorry im such a newb but i dont know what that is... :(

jchrisphonte
01-25-2012, 10:53 PM
http://hedgevent.com/tablesql.png perhaps this is what you were after...

Fou-Lu
01-26-2012, 01:01 AM
Yep that will do.
This query should work then, sdate is listed as a datetime datatype, so it should be comparable to NOW(). Its also not null.
So directly from PHPMyAdmin then, execute SELECT * FROM calendar_event WHERE sdate >= NOW() to see if you pull up all records from this point forward. Sorry, NOW() literally means now though, so that will not include any records from today except for after the current time. Using CURRENT_DATE() should work though.

jchrisphonte
01-26-2012, 01:14 AM
got this :

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0009 sec )

after entering



$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE sdate >= NOW() ORDER by month,day ASC";


I added hyphens around 'sdate' and it then pulls data in sql and on the localhost yet it still includes the dates that are earlier than today 1/7/2012 etc...

jchrisphonte
01-26-2012, 01:26 AM
I think i may have found the problem im getting

0000-00-00 in all my sdate fields..

i have dates like 1/25/2012.

how can i make them import properly??

jchrisphonte
01-26-2012, 01:30 AM
changed the date to UK formatting so it matches 0000-00-00 and it uploads now... sadly past events are still showing in the echo...

jmj001
01-26-2012, 06:22 AM
this query should work

$query = "SELECT event, company, location, city, month, day, year FROM calendar_event WHERE
year > '".date("Y")."' ||
(year = '".date("Y")."' && month > '".date("m")."') ||
(year = '".date("Y")."' && month = '".date("m")."' && day >= '".date("d")."')
ORDER by month,day ASC";

jchrisphonte
01-26-2012, 06:39 AM
WHOOOOOOOO HOOOOOO!!!!!

JMJ - You are the man/ or woman!!!!


Thank you soooooooooooooooooooooo much... works perfectly.

Jerry

jchrisphonte
01-26-2012, 06:51 AM
I was trying to do the reverse on another page..

This query was for upcoming events... How would I tweak this code to show the last 30 recently past events in descending order (meaning most recently past events first)??

jmj001
01-26-2012, 06:51 AM
just thought I might expand on this a little...

you could set a time value too so it's not only working on right now...


$plusDays = 0;
$timeRef = date("Y-m-d", strtotime($plusDays. " days " . date("Y-m-d")));
$query = "SELECT event, company, location, city, month, day, year FROM calendar_event WHERE
year > '".date("Y", strtotime($timeRef))."' ||
(year = '".date("Y", strtotime($timeRef))."' && month > '".date("m", strtotime($timeRef))."') ||
(year = '".date("Y", strtotime($timeRef))."' && month = '".date("m", strtotime($timeRef))."' && day >= '".date("d", strtotime($timeRef))."')
ORDER by month,day ASC";

plusDays can be any value including -(minus) for a past date

I've not tested this, let me know if there's any errors...

jmj001
01-26-2012, 07:23 AM
for past events try this


$query = "SELECT event, company, location, city, month, day, year FROM calendar_event WHERE
year < '".date("Y")."' ||
(year = '".date("Y")."' && month < '".date("m")."') ||
(year = '".date("Y")."' && month = '".date("m")."' && day <= '".date("d")."')
ORDER by month,day DESC limit 30";

keep in mind though this is not an ideal way to retrieve this kind of data....

what you were working on earlier in the thread to change the dataset to date is a much better option but this will get you going anyway

you should be storing the entire date in a single column as a 'date' type or 'datetime' type if you want hours,min,sec too...

then when you retrieve the data you can format it into specific day, month, year using php date() function

eg; retrieve the date value which comes out of the database as 2012-01-20 and format it to specific day, month, year... like..


$thisEventDate = "2012-01-20"
$thisEventDay = date("d",strtotime($thisEventDate));
$thisEventMonth = date("m",strtotime($thisEventDate));
$thisEventYear = date("Y",strtotime($thisEventDate));

hopefully this will give you a kickstart anyway...

:thumbsup:

jchrisphonte
01-26-2012, 07:29 AM
this is great -- how can I limit the # of results??

jmj001
01-26-2012, 07:35 AM
you can just add LIMIT x at the end of the query, see my last post before this one... it has LIMIT 30 at the end of the query...

jchrisphonte
01-26-2012, 07:40 AM
:) i see it now -- works like a charm!!

Welcome to CF JMJ.

jmj001
01-26-2012, 07:45 AM
:)

Cheers...

oh btw.. man :thumbsup:

Fou-Lu
01-26-2012, 02:50 PM
Its queries like these I was hoping to avoid.
For your sdate, if they are all 0000-00-00, then yeah that will be a problem. You can update those based off of the other fields if you want.
As for str_to_date, the problem with a couple of tests I did was simple. %c is greedy; it will take as many tokens as it can to try and build a date. 2012117 from my perspective is 2012-1-17, but from str_to_date it is 2012-11-07.

So all you need to do is delimit the dates:


$query = "SELECT id, event, company, location, city, month, day, year
FROM calendar_event
WHERE STR_TO_DATE(CONCAT(`year`, '-', `month`, '-', `day`), '%Y-%c-%e') >= CURRENT_DATE()
ORDER by month,day ASC
LIMIT 30";


Past events would just be a swap of >= to < or <= depending on if you want to include today.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum