Moron Posted September 25, 2006 Share Posted September 25, 2006 I want to take a result from my query:[code]$RESULT[EPEDAT] (Pay period ending date)[/code]...and compare it to a variable that gives me the UNIX timestamp of two weeks ago:[code]$twoweeks = strtotime('-14 days');[/code]I want to only display the most current paystub, based on $RESULT[EPEDAT] from the database.I've tried converting $RESULT[EPEDAT] to a timestamp and then doing the math, but I can't seem to make it work.The record date ($RESULT[EPEDAT]) is in mm/dd/yyyy format.What's the best way to go about this? Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 with the format you have it stored in, how did you go about changing it to a UNIX timestamp? you'd have to do a little more than just strtotime on it. try something like this, and then do your comparison:[code]<?php$date = "02/14/2006";list($month, $day, $year) = explode("/", $date);// now make your timestamp$ts = mktime(0,0,0,$month,$day,$year);?>[/code]hope this helps Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 [quote author=obsidian link=topic=109419.msg440965#msg440965 date=1159195742]with the format you have it stored in, how did you go about changing it to a UNIX timestamp? you'd have to do a little more than just strtotime on it. try something like this, and then do your comparison:[code]<?php$date = "02/14/2006";list($month, $day, $year) = explode("/", $date);// now make your timestamp$ts = mktime(0,0,0,$month,$day,$year);?>[/code]hope this helps[/quote]Thanks, but I goofed. The date from the query, $RESULT[EPEDAT], has no slashes. It's stored as 9252006 and so on, so there shouldn't be any parsing necessary. Quote Link to comment Share on other sites More sharing options...
AndyB Posted September 25, 2006 Share Posted September 25, 2006 [quote author=Moron link=topic=109419.msg440972#msg440972 date=1159196410]Thanks, but I goofed. The date from the query, $RESULT[EPEDAT], has no slashes. It's stored as 9252006 and so on, so there shouldn't be any parsing necessary.[/quote]Unfortunately, you've chosen a format that just made life difficult for you. Without knowing how those 'dates' are put together, I'd just guess that:year is the last four characters of the 'date'day is the first two characters of the last six characters of the 'date'month is the first two characters if the 'date' is eight characters long, otherwise it's the first character of the 'date' when the 'date' is seven characters long.Is that correct? If so, then substr() will be able to produce the day, month, and year from the string. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 actually, there will still be parsing necessary. think of it this way:Sept 25, 2006 = "9252006"Oct 12, 2001 = "10122001"with that naming convention, Oct 12, 2001 is actually after Sept 25, 2006... make sense? Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 Okay, now let me drop the [b]really[/b] stupid question:I want the query to select the most current record, based on date.Is there a way to do that without a lot of timestamp functions and math? Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 Let me run this by you:I now have a variable:[code]$recordyear = $RESULT[EPEDAT];[/code]...and a substr statement:[code]echo substr("$recordyear", 3, 8); [/code]This gives me "2005," (no quotes) which is the year of the paystub it's pulling, 10122005.I could do the same technique for the month and day, BUT.... how do I use this in the query? $RESULT[EPEDAT] doesn't exist intil the query has been ran. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 you'll need to familiarize yourself with the mysql SUBSTRING function as well. you can use results of string functions as part of your WHERE clause to do what you're after. Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 [quote author=obsidian link=topic=109419.msg441104#msg441104 date=1159204495]you'll need to familiarize yourself with the mysql SUBSTRING function as well. you can use results of string functions as part of your WHERE clause to do what you're after.[/quote]I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run? Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 [quote author=Moron link=topic=109419.msg441106#msg441106 date=1159204725]I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run?[/quote]read my post carefully. i'm suggesting you use the [b][i]MySQL[/i][/b] substring. check it out in the manual along with [url=http://dev.mysql.com/doc/refman/5.1/en/string-functions.html]all the other string functions[/url] that may help you out. Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 [quote author=obsidian link=topic=109419.msg441111#msg441111 date=1159205049][quote author=Moron link=topic=109419.msg441106#msg441106 date=1159204725]I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run?[/quote]read my post carefully. i'm suggesting you use the [b][i]MySQL[/i][/b] substring. check it out in the manual along with [url=http://dev.mysql.com/doc/refman/5.1/en/string-functions.html]all the other string functions[/url] that may help you out.[/quote]Thanks. Will explore. Quote Link to comment Share on other sites More sharing options...
AndyB Posted September 25, 2006 Share Posted September 25, 2006 Good luck with substrings ... because the length of your strings is not always the same ... unless you [i]always[/i] have a two-digit day number and a two-digit month number. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 [quote author=AndyB link=topic=109419.msg441219#msg441219 date=1159212239]Good luck with substrings ... because the length of your strings is not always the same ... unless you [i]always[/i] have a two-digit day number and a two-digit month number.[/quote]that's what i thought, too, but after no concern seemed to be given over my last post, i figured i'd drop it. i thought maybe i missed something :P Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 Well, gents.... :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter? Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 [quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]Well, gents.... :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?[/quote]how about days? 1 vs. 11??? Quote Link to comment Share on other sites More sharing options...
Moron Posted September 25, 2006 Author Share Posted September 25, 2006 [quote author=obsidian link=topic=109419.msg441262#msg441262 date=1159215726][quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]Well, gents.... :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?[/quote]how about days? 1 vs. 11???[/quote]Nope. Days less than 10 are preceeded by a zero, like 9042006 for September 4th. Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 25, 2006 Share Posted September 25, 2006 [quote author=Moron link=topic=109419.msg441289#msg441289 date=1159217457]Nope. Days less than 10 are preceeded by a zero, like 9042006 for September 4th.[/quote]well, i guess then you could always simply check the length of the string before you run substring on it. that way, you'll know whether to grab the first one or two digits. otherwise, i'm not sure of a good way to substring [i]from the right[/i] Quote Link to comment Share on other sites More sharing options...
AndyB Posted September 25, 2006 Share Posted September 25, 2006 [quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]Well, gents.... :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?[/quote]That would be reply #3 in this thread.At the risk of this being deemed spam (since I've said it so many times) ... why don't you convert your database information to 'real' dates instead of these alternatives that require all sorts of mathematical and logical gymnastics? Quote Link to comment Share on other sites More sharing options...
Moron Posted September 26, 2006 Author Share Posted September 26, 2006 [quote author=AndyB link=topic=109419.msg441358#msg441358 date=1159225776][quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]Well, gents.... :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?[/quote]That would be reply #3 in this thread.At the risk of this being deemed spam (since I've said it so many times) ... why don't you convert your database information to 'real' dates instead of these alternatives that require all sorts of mathematical and logical gymnastics?[/quote]Thanks, Andy. That would be my first choice, as well, but I've been told that [i]"the database is what it is and you'll have to work with what you have."[/i] I think the reason is because there are other scripts pointing to it and they don't want to rock the boat, which I can understand. Quote Link to comment Share on other sites More sharing options...
AndyB Posted September 26, 2006 Share Posted September 26, 2006 So do you use still that 8-track player?I understand the problems with legacy software and database data. My company made a lot of money in resolving or working around Y2K problems that were also a legacy problem. But just because that's how great-great-grandfather did it doesn't mean we have to keep doing it the same way whenever we have a [i]new[/i] problem to deal with - otherwise we'd still be using quill pens and parchment to record all the data. You know how much of a headache it has been to deal with the legacy data when a 'real' date would have made your recent problems trivial ... and the effort in conversion would have paid for itself many times over (unless you're working for free).How about adding a new field to the database that can contain the date in an unmistakable, usable, format. All future applications should use that and ignore the wacko, pre-historic notions of date; all old applications can continue to be a monument to the past. Run a simple script 'offline' to generate a real date from each group of pseudo-date data and write it to the new date field. 21st century scripts are simpler and the historical stuff can continue until ... whatever. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.