Dispaly a future date by order

Rollo_Tamasi

I'm currently working on a events script which allows a user to enter in details about concerts into a database. All the concerts are to be sorted by the date they take place. A concert taking place on the date 20th of June will appear ahead of a concert taking place on the 27th of June.

I'm trying to figure out what is the best way to do this...

The user is going to have to enter in the future date of the event. How? Should i use drop down boxes for the day (number between 1 and 31) and for the month (jan, feb...) and do a query on them. Or would a calander system be a better choice which can input the date as a 'date' into the db, this would be a lot easier to query but no doubt would be more complex than the previous option.

What would you do?

Installer

Either way you'll need to format the input date as a MySQL date or datetime type to insert into the query string. If the calendar input you mention will post the complete date ready to use, great, but if not, constructing the date string should be fairly trivial. So I'd say it's a wash; do what fits your site design best.

Rollo_Tamasi

would the easiest way to do it so.....take in two strings, one a number between 1-31 and another a number between 1-12, and convert them to a string as a value 'date' and then input them into the db?

If that is a method...what would be the code to do it?

rvdavid

I try to get away from storing dates as MySQL dates.

Store the timestamps into the database as integers and use the PHP date function to convert it back your desired date format.

 echo date('Y - m - D', mktime());

To insert


$timestamp = mktime(0,0,0,0,$_POST['month'],$_POST['day'],$_POST['year']);
$concert = $_POST['concert'];

$sql = "INSERT INTO concerts (concert, concert_timestamp) 
            VALUES ('$concert', '$timestamp');

mysql_query($sql,$dbConn);

To retrieve

$sql = "SELECT concert, concert_timestamp 
             FROM concerts
             ORDER BY concert_timestamp ASC";
$result = mysql_query($sql,$dbConn);
$row = mysql_fetch_assoc($result);

$concert = $row['concert'];
$concert_date = date('j-m-Y',$row['concert_timestamp']);

The advantages are these:

It's faster - mysql doesn't need to convert it to unixtimestamp when inserting or updating timestamp records.
It's universal - although highly unlikely, if you needed to migrate to another database format like say Postgres, date transformation will not be a problem
No more worrying about date format when insert into db
You can format your date/time output with one php call instead of imploding the date given by mysql.

//full date - 12-06-2006
$date = date('j-m-Y',$row['concert_timestamp']); // you can use date to format the output

//broken up into day month year
$day = date('j',$row['concert_timestamp']);
$month = date('m', $row['concert_timestamp']);
$year = date('Y', $row['concert_timestamp']);

//full date - 2006-06-12
$date = $row['concert_timestamp'];

//broken up into day month year

//get pieces or you ould use strtotime to convert back to timestamp
$date = implode('-',$row['concert_timestamp']);

$day = $date[2];
$month = $date[1];
$year = $date[0];

Hope this helps.

Regards,

cahva

I woudl suggest the other way 🙂 Nowdays DATETIME is the same as TIMESTAMP which is a SQL-standard so it shoud not be a problem when moving the data to different database system if necessary. The best thing to do is move AWAY from using unixtimestamps and using the normal timestamp. Do as much as you can from mysql. Theres no point using php to manipulate dates as mysql has much more better date and time functions to do that in 99.9% of cases.

rvdavid

I understand what you're getting at 😉

But I'd rather have my SQL be read as is and not mutated using DATE_FORMAT() functions.

$sql = "SELECT DATE_FORMAT(somedatefield, '%W %M %Y') as formatted_date, concert ... ";
$result = mysql..
$row = mysql...
$date = $row['formatted_date'];
$date_array = explode('-',$date);
$day = $date_array[2];
$month = $date_array[1];
$year = $date_array[0];

$sql = "SELECT concert_timestamp, concert ... ";
$result = mysql..
$row = mysql...
$date = date('Y-m-j',$row['concert_timestamp']);
$day = date('j',$row['concert_timestamp']);
$month = date('m',$row['concert_timestamp']);
$year = date('Y',$row['concert_timestamp']);

I like the second approach better. It's cleaner and may be a little faster (I don't have the stats) due to MySQL not needing to do any formatting.

Not only that, but just to be "nitpicky" about it, the storage requirement for DATETIME (8b) is double the storage requirements for an INTEGER (4b).

I know about the MYSQL Date Time functions, and I'm for it if you're doing complex date operations (can't think of any scenars at this point in time) but if I had the option, I'd always use the approach which does not use DATETIME.

Regards,

Rollo_Tamasi

I have never worked with the timestamps before, would this be the correct mysql?

CREATE TABLE `concert` (
`concert_timestamp` TIMESTAMP
)

and the following is the code i'm working with. i'm only usingthe concert_timestamp field btw

<?php
include 'db.php';

$timestamp = mktime(0,0,0,0,$_POST['month'],$_POST['day'],$_POST['year']);
$year = '2006';
$sql = "INSERT INTO concert (concert_timestamp) VALUES ('$timestamp')";
$query = mysql_query($sql) or die("Cannot query the database.<br>" . mysql_error());
echo "Database Updated.";

?>

<form name="event" method="post" action="<?php echo $PHP_SELF; ?>">
Day:
   <select name="day" id="day">
     <option value="1">1</option>
     <option value="2">2</option>
     <option value="3">3</option>
   </select>
Month
   <select name="month" id="month">
     <option value="June">June</option>
     <option value="July">July</option>
	 <option value="August">August</option>
   </select>
   <input type="submit" name="Submit" value="Submit">

When i run the script and check my entry in the database i get '00000000000000' each time i attempt to enter a value.

Thanks for all the help btw

rvdavid

You need an integer field...

CREATE TABLE `concert` (
`concert_timestamp` INT(10) DEFAULT 0 NOT NULL
)

🙂

Thanks for all the help btw

No problems.

rvdavid

Ooops sorry forgot to declare the field as unsigned.


 CREATE TABLE `concert` (
`concert_timestamp` INT(10) UNSIGNED DEFAULT 0 NOT NULL
)

regards,

Rollo_Tamasi

cool thanks

I don't suppose anybody could recommend a decent javascript validator which can ensure that the amount of days in the month suscribe to the month. I have come across one or two myself but they aren't exactly what i'm looking for.

Rollo_Tamasi

found one here that works on page 8 of google! 🙂
http://jeffhowden.com/code/javascript/select_calendar/

Rollo_Tamasi

i just did some testing on the above script using the proper mysql table for it and the entries i'm getting are quite odd. For example when i select the 1st of June the entry in the database is "976060800". Anybody know why this is?

Installer

Follow cahva's advice.