homepage Welcome to WebmasterWorld Guest from 50.16.130.188
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
i need an advise
phex




msg:4172486
 7:41 pm on Jul 18, 2010 (gmt 0)

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




msg:4172849
 3:43 pm on Jul 19, 2010 (gmt 0)

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




msg:4173228
 8:44 am on Jul 20, 2010 (gmt 0)

LifeinAsia

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


Help pleeeeeeas!

LifeinAsia




msg:4173414
 3:55 pm on Jul 20, 2010 (gmt 0)

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




msg:4173434
 4:26 pm on Jul 20, 2010 (gmt 0)

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




msg:4173844
 6:22 am on Jul 21, 2010 (gmt 0)

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




msg:4174130
 3:54 pm on Jul 21, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved