i need an advise

[phex]

i want to create a table called events whereby visitors can enter upcoming events. Database is mysql.

Should i use a single column or more to hold the dates of the events?

I which format should visitors enter the events dates?

What is the mysql syntax for selecting only records which their dates has not passed like tommorows or next month events, not yesterdays?

[LifeinAsia]

Should i use a single column or more to hold the dates of the events?

You probably want at least 2 DATETIME columns (1 for the start date, 1 for the end date). Even if the event only lasts one day, it will still have a start time and end time.

which format should visitors enter the events dates

I consider the best way to provide drop-down selects for the date, month, and year. That way there is less work on the backend checking for valid dates. If you allow people to enter dates manually, you're going to get a whole range of ways for entering the same date: "1/10/2010" and "10-JAN-2010" and "1.10.2010" are all ways people could enter January 10, 2010. With drop-down selects, it's much easier to parse the dates and check for invalid dates (e.g., June 31st).

What is the mysql syntax for selecting only records which their dates has not passed

This was basically answered in your previous question [webmasterworld.com].

[phex]

LifeinAsia

i have never before used a date drop-down menu to insert data into DATETIME datatype column.


Help pleeeeeeas!

[LifeinAsia]

You have one select for the date (1-31), 1 select for the month (1-12) and 1 select for the year (2010-whenever):
<select name="EventDate">
<option value="1">1</option>
<option value="2">2</option>
...
<option value="31">31</option>
</select>
<select name="EventMonth">
<option value="1">January</option>
<option value="2">February</option>
...
<option value="12">December</option>
</select>
<select name="EventYear">
<option value="2010">2010</option>
<option value="2011">2011</option>
...
</select>

After the form is submitted, you take those values and create a date (the specifics of this depend on which language you're using to process the form) and insert it into your database. For example, the following would work in Cold Fusion:
<cfset ThisDate=CreateDate(FORM.EventYear,FORM.EventMonth,FORM.EventDate)>

[rocknbil]

Ooops, sorry LIA, missed it by ->||<-- that much . . .

What are you using, PHP? **something** like this. This is not working code (but it might work, typed on the fly), just to give you an idea.

// Set your start year. If it's something like a date for credit card
// or "current years," you can use PHP time() or mysql date() to get it so it
// needs maintenance. For birthdays, etc., you'll need something else
$start_year = date('Y');

// Set the end year OR the number of years for the year list.
// For birthdays of course it would end "this year."
$end_year = '2020';

// call the function. It can be used for multiple lists by changing
// the first parameter, "list name." It will append _month, _day,
// _year to all three selects.

$date_list = date_list('event_date',$start_year,$end_year);

function date_list($name,$sy,$ey) { 
 // 
 $mm = $name . '_month'; 
 $dd = $name . '_day'; 
 $yy = $name . '_year'; 
 $yearlen=$ey-$sy; 
 // Though $yearlen is not used, always good to error check 
 if (! ($yearlen>0)) { die("Invalid start or end year in date list"); } 
 // 
 $list .= '<select name="'.$mm.'" id="'.$mm.'"> 
 <option value="">-</option>'; 
 for ($i=1;$i<=12;$i++) { 
 $val=(strlen($i)==1)?'0'.$i:$i; // to get 01,02, etc. 
 $list .= '<option value="'.$val.'"'; 
 if (isset($_POST[$mm]) and ($_POST[$mm]==$val)) { 
 // checked="checked" for XHTML 
 $list .= ' checked'; 
 } 
 $list .= '>'.$val.'</option>'; 
 } 
 $list .= '</select> 
 <select name="'.$dd.'" id="'.$dd.'"> 
 <option value="">-</option> 
 '; 
 // Don't care about invalid dates here, mySQL will 
 // handle it, if you want you can make this more robust 
 for ($i=1;$i<=31;$i++) { 
 $val=(strlen($i)==1)?'0'.$i:$i; 
 $list .= '<option value="'.$val.'"'; 
 if (isset($_POST[$dd]) and ($_POST[$dd]==$val)) { 
 $list .= ' checked'; 
 } 
 $list .= '>'.$val.'</option>'; 
 } 
 $list .= '</select> 
 <select name="'.$yy.'" id="'.$yy.'"> 
 <option value="">-</option> 
 '; 
 for ($i=$sy;$i<=$ey;$i++) { 
 $list .= '<option value="'.$i.'"'; 
 if (isset($_POST[$yy]) and ($_POST[$yy]==$i)) { 
 $list .= ' checked'; 
 } 
 $list .= '>'.$i.'</option>'; 
 } 
 $list .= '</select>'; 
 return $list; 
} 

OK, so incoming you have three values, compose a valid date string, which is why a date format column is much easier to work with in mySQL than a timestamp. Of course, you need to error check and cleanse them first, but then,

$event_date = $_POST['event_date_month'] . '-' . $_POST['event_date_day'] . '-' $_POST['event_date_year'];

$query = "insert into table (eventDate) values('$event_date')";

From that you should be able to construct something to get this working for you.

[phex]

LifeinAsia, this is what confuses me a little bit
*After the form is submitted, you
take those values and create a date*

I am using php to insert data into mysql db.

How do i create a date with the drop down menu values?

What is the mysql query for inserting the date into the datetime column?

I am learning php/mysql on my own so please ber with me.

[LifeinAsia]

How do i create a date with the drop down menu values?

Like I mentioned- some function like CreateDate(FORM.EventYear,FORM.EventMonth,FORM.EventDate)

See rocknbil's post for additional specifics.