[Resolved] Date issues

[Zebbedy]

I've trolled thru the newbie and code sections and cant really seem to find what im after. I have also gone thru the 4 books i have here and the php online manual and still cant really understand what im doing.

I have a dbase which has to store a date entered by some computer illiterate data entry people. So i wanted to make it as idiot proof as possible. With this in mind i have broken the date up into 3 entry fields: Day, Month (both pulldown menu options) and a 4 int Year field. The code for these seems to be no problem.

I have 4 pages: addrecord, updaterecord, displayrecord, and recordlist.

My main problem is whether to store the data as a MySQL date or a Unix timestamp. I need to be able to sort and search for specific dates, but display them as date('jS m Y') (im in the UK). Also once stored i need to be able to break them up into component day, month and year variables so that i can display them in the date field/menus when i want a user to update.

I have a little function which doesnt seem to be working because of the formatting i place in the date() function, but im not sure how to correctly format it so that it stores the data correctly for the MySQL format.

function GetDateString($theday, $themonth, $theyear) 
{
  $theUnixdate = mktime(0,0,0,$themonth,$theday,$theyear);
  $thedate = date('Y-m-d',$theUnixdate);
  return $thedate;
}

Another portion of this problem is how do i use the date function to display data stored as a MySQL date format as the date() function uses the unix timestamp eg date(string, unixtimestamp) and not date(string, MySQLdateformat).

Eventually this data will be searchable for events that occur on a particular date (more than likely also selected via pulldown menus similar to how the data is entered).

Is anyone able to offer a suggestion for this or am i better off storing dates as a Unix timestamp? This also poses more questions as i will still need to extract the day, month, year portions at some stage.

[devinemke]

just use mySQL's DATETIME field type. you can easily format/break apart the dates using any number of mySQL's Date and Time Functions.

[bpat1434]

Yes, Devin that would work; however, if you use a Unix Timestamp, you can do this:

Insert, update records and update the tiem field with time(). That will give you the unix timestamp.

Then when you want to display it, just use the date() function.

date("jS, Y", $row['time']);

It will grab the Unix Timestamp and create the date, formatted as you want, and you can use it as you want.

~Brett

[devinemke]

Originally posted by bpat1434
Yes, Devin that would work; however, if you use a Unix Timestamp, you can do this:

Insert, update records and update the tiem field with time(). That will give you the unix timestamp.

Then when you want to display it, just use the date() function.

date("jS, Y", $row['time']);

It will grab the Unix Timestamp and create the date, formatted as you want, and you can use it as you want.
~Brett

i have to respectfully disagree with you Brett. IMHO you should always use the database engine do the formatting (if possible) rather than using PHP. your suggestion requires pulling all of the data out and then formatting each row in a loop. why bother? pull the data out already fornatted using the database engine's own built-in fornatting functions.

[bpat1434]

Options and opinions: that's what a forum's for. You make a valid argument, and so do I. It depends all upon what they want out of it.

~Brett

[Zebbedy]

Thanks guys. Both ways seem to have their merits. After i posted yesterday i bashed my head against the wall a bit longer and made the executive decision to go with the MySQL Date field. Mainly because i have other sections of this project to work on and had to make a decision.

What i originally wanted to do was have 3 fields that led the data entry people by the hand so they couldnt stuff it up. Now i have had to trust them to do the right thing and just have a single text entry field for them to enter the date in the MySQL format YYYY-MM-DD. The data entry is just the backend of things after all and i can go back later and try to 'pretty it up'.

I think the most annoying thing is that in most cases the record will be taken as a whole and so i will probably have to make a second call to the db just to format the date as my initial query will be something like SELECT * FROM table where id=X.

The front end will be fun as i will definately have the 3 fields because you cant trust users (at least i can go downstairs to my data entry team and bash some heads if they get it wrong! 😛)

Any suggestions on how i can concatonate 3 fields into something i can compare with the MySQL date format? Should it be something like:

$variable = ($year . '-' . $month . '-' . $day)

...or should i create a string, and if so how? Im still coming to grips with the different ' and " and when to use them for php. Only been at this for 3 weeks! 😛

Thanks again for the suggestions.

[Zebbedy]

Hmmm..... things arent happening as they should.

$recordID = $_GET['recordID'];
$query_DetailRS1 = "SELECT * FROM murder WHERE id_murder = $recordID";
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $conn) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

$query_dateRS1 = "SELECT DATE_FORMAT(date, '%D %M %Y') FROM murder WHERE id_murder = $recordID";
$dateRS1 = mysql_query($query_dateRS1, $conn) or die(mysql_error());
$row_DateRS1 = mysql_fetch_assoc($dateRS1);

echo $row_dateRS1['date'];

note: The $query_limit_DetailRS1 is some code written by my offsider and has something to do with the previous page. Im still trying to understand it, but apparently it is not relevant here other than to explain the variable useage on the next line.

This unfortunately returns a blank echo. I tried it as:

$query_dateRS1 = "SELECT DATE_FORMAT(date, '%D %M %Y') FROM murder WHERE id_murder = $recordID";
$dateRS1 = mysql_query($query_dateRS1, $conn) or die(mysql_error());
$row_DateRS1 = mysql_fetch_assoc($dateRS1);

echo $dateRS1;

But that returned something like 'Resource id#6' as a result. What is it im doing incorrectly? Im beginning to REALLY dislike dates! 🙂

[devinemke]

Originally posted by Zebbedy
Any suggestions on how i can concatonate 3 fields into something i can compare with the MySQL date format?

you can collect the date via multiple form fields and concatenate them together or simply offer up one text field that the user can enter any style date they want and do something like:

$mysql_date = date('Y-m-d', strtotime($_POST['date']));

you will still need to do some validation of $_POST['date'] first. you should look at [man]checkdate[/man].

[Zebbedy]

Thanks Devinemke ill give that a go.

I've gone with the MySQL format as mentioned above. Mainly because a decision had to be made. So now i have come up against a MySQL syntax error and cant really see what is wrong. The error is:

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 'LIMIT 0, 100' at line 1

... and cannot see where i have gone wrong with my query according to what is written in the MySQL online manual.

My query is: (variable names changed at mo cause this will be used for several different tables)

SELECT id, DATE_FORMAT(date, '%D %M %Y'), var1, var2, var3, var4, var5, var6, var7, var8, var9 FROM tablename WHERE id = $recordID LIMIT $startRow_Recordset1, $maxRows_Recordset1

The LIMIT is just restricting the number of rows that display. I assume that there is an issue with using the DATE_FORMAT() and LIMIT in the one query although i cant understand why. The query worked when it was:

SELECT * FROM tablename WHERE id = $recordID LIMIT $startRow_Recordset1, $maxRows_Recordset1

[Zebbedy]

Spotted part of my mistake: drop the WHERE clause and it no longer gives a syntax error. Unfortunately i still have no date displayed in whatever format.

[Zebbedy]

I've been making things far too difficult for myself. Devinemke's suggestion has worked out fine. I have had to make a compromise as to how the data is entered originally, but it all seems to be working so far.

One small issue remains. If the date field is left empty (therefore saving a value of 0000-00-00) why do i get the date 30 November 1999 when i use the function:

date('jS F Y', strtotime($row_Recordset1['date']))

Even when i do a checkdate on it, it returns with it being a valid date. I would have thought that having a 0 day for a start would render it invalid.

[devinemke]

have the field default to NULL and simply check for NULL before running your function. BTW: you should really use mySQL's DATE_FORMAT() function to do that formatting.

[Zebbedy]

I tried using the DATE_FORMAT() as can be seen above but never got a satifactory result. Still not sure why, but the strtotime() seemed to work for the moment. I will set the date to default to NULL and see how that changes things.

Thanks for the help btw devinemke