displaying startdate and enddate from a table

thread_PHP

HI All
I have a table
like this below

CREATE TABLE newevent (
event_id int(6) unsigned NOT NULL auto_increment,
eventtitle varchar(40) default NULL,
eventtype varchar(40) default NULL,
eventdescription varchar(255) default NULL,
venuename varchar(40) default NULL,
streetaddress1 varchar(100) default NULL,
streetaddress2 varchar(100) default NULL,
city varchar(50) default NULL,
state varchar(40) default NULL,
zipcode varchar(20) default NULL,
country varchar(40) default NULL,
startdate varchar(40) default NULL,
enddate varchar(40) default NULL,
starttimehh varchar(40) default NULL,
starttimemm varchar(40) default NULL,
starttimeam varchar(40) default NULL,
endtimehh varchar(40) default NULL,
endtimemm varchar(40) default NULL,
endtimeam varchar(40) default NULL,
total varchar(20) default NULL,
males varchar(40) default NULL,
females varchar(40) default NULL,
PRIMARY KEY (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

i want to display the value in startdate,enddate with the event exists

for example my event runs from startdate 1-july-2007 till 5-july-2007 i want to display my eventtitle till it ends on 5th of July 2007

How can i write this query

for example when i write the query to see the

select eventtitle,startdate,enddate from newevent is the query that will return the following rows

Eventtitle StartDate Enddate
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'
'Social Lunch','Wednesday, June 13, 2007','Saturday, June 30, 2007'

so in the above query social lunch is occuring from 13th june till 30th july2007
with the above query i am getting displayed start of event and end of the event

i want to display all the dates from 13th june 2007 till 30th july 2007 in the output

Anybody please help me

alimadzi

Instead of VARCHAR, you should really be using MySQL's built-in types: DATE, TIME, and DATETIME. They'll make your life a heckuva lot easier for stuff like what you are wanting to do.

thread_PHP

hi alimadzi
Thanks for your reply
will DATETIME field takes ''Wednesday, June 13, 2007'' this kind of input as i need to display the eventstartdate output as above can you explain me with changing the table and query.

alimadzi

It doesn't store the day of the week (like Wednesday for example). Here is the documentation you need to get started...

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

bradgrafelman

thread_PHP wrote:
this kind of input as i need to display

Who ever said that the data must be stored in the same format as which you want it to be displayed?

For storing purposes, it should be easier to sort/compare/etc., which is why MySQL has the DATE/DATETIME/etc. data types. If you want it displayed differently, that's where functions such as MySQL's DATE_FORMAT() come in handy.

thread_PHP

hi bradgrafelman
thanks for your reply i get date formated using the following query
SELECT eventtitle,DATE_FORMAT(startdate, '%W %d %M %Y') as startdate, DATE_FORMAT(enddate, '%W %d %M %Y') as enddate from newevents;

output
event Wednesday 04 April 2007 Tuesday 10 April 2007 - on 4th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007 - on 5th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007 - on 6th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007 - on 7th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007- on 8th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007- on 9th april 2007
event Wednesday 04 April 2007 Tuesday 10 April 2007- on 10th april 2007

but i want to display the events in the above manner from 4th april 2007 till 10th april 2007

bradgrafelman

thread_PHP wrote:
but i want to display the events in the above manner from 4th april 2007 till 10th april 2007

So look at the table of format strings on the MySQL page I linked above, and then construct the string that matches that format.

Example: You start with the day number with English suffixes, so first you'd have %D . Just match up each part of your example output with the proper specifier from the table.