Moron Posted October 2, 2006 Share Posted October 2, 2006 Okay, I need some query/coding help here. I've discussed this in other threads. Yes, I know our database does dates in a weird way. No, there is nothing I can do about it. No, I'm not a PHP programmer. Yes, I'm trying to learn. I've had this same matter hanging over my head for two weeks. Enough is enough.Dates are like 9202006 or 10012006. There is nothing I can do about it. If the month is 09 or less, then the date is seven digits. If it's 10 through 12, it's eight digits. With me? There is no trailing zero on dates with a month less than 09.Anyway, when I run the query, it gives me a record from 10122005 (October 12, 2005). I don't know if it's randomly pulling this record out of clear blue sky or what, but I need to pull the MOST CURRENT RECORD. Period. In case you're wondering, this is a paystub retrieval system. I need it to return the MOST CURRENT PAYSTUB. What would seem to be a simple matter is apparently rocket science. The trick must be in the query.Can anyone help? Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/ Share on other sites More sharing options...
Daniel0 Posted October 2, 2006 Share Posted October 2, 2006 I don't know mssql, but just select all of them, but based on your code, try something this:[code]<?phpfunction convert_date($date){ return mktime(0,0,0,substr($date,0,(strlen($date)==8 ? 2 : 1)),substr($date,-6,2),substr($date,-4));}$RESULTDS=mssql_query("SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[PAYCTR], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF], EH.[EPEDAT], EH.[ENETPA], LH.[LOCC], EH.[EGRSER], EH.[EREGHR], EH.[EDEDUC], EH.[EROTHR], EH.[EFWHD], EH.[ESSDED], EH.[EHOSPD], EH.[ELIFED], EH.[ECRUD], M2.[MCTDWH], M2.[MCTDCS], M2.[MO3TOT], M2.[MCTDLD], M2.[MCTDGE], M2.[MALPPP], M2.[MSLPPP], M2.[MWHSTA], M2.[MWHALL], M2.[MWHADD], EH.[EGARND]FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO INNER JOIN EARNHIST EHON EH.[EEMPNO]=M2.EMPNOWHERE M2.[EMPNO] = '".$_POST['employeenumber']."' and M2.[MSSNO] = '".$_POST['password']."' ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc");while($RESULT=mssql_fetch_assoc($RESULTDS)){ $rows[] = $RESULT;}$most_recent_row = array();foreach($RESULT as $row){ $timestamp = convert_date($row['whatever_the_field_name_is']); if(convert_date($most_recent_row['whatever_the_field_name_is']) < $timestamp) { $most_recent_row = $row; }}print_r($most_recent_row);?>[/code] Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102485 Share on other sites More sharing options...
obsidian Posted October 2, 2006 Share Posted October 2, 2006 russ, here you go:[code]<?php// get all your records into your array:$sql = mysql_query("SELECT * FROM tableName");$res = array();while ($row = mysql_fetch_array($sql)) $res[] = $row;// now, set up your data to run a multisort:foreach ($res as $key => $row) { $date = $row['dateCol']; $year[$key] = substr($date, -4); $date = preg_replace('|' . $year . '|', '', $date); $month[$key] = substr($date, -2); $day[$key] = preg_replace('|' . $month . '|', '', $date);}// sort it!array_multisort($year, SORT_DESC, $month, SORT_DESC, $day, SORT_DESC, $res);// now, your $res[0] should hold the record of the most recent entry?>[/code]obviously, with you using mssql, your function calls would be slightly different, but the idea still applies to the sort.hope this helps Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102489 Share on other sites More sharing options...
Daniel0 Posted October 2, 2006 Share Posted October 2, 2006 Some replies are missing o_O Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102492 Share on other sites More sharing options...
printf Posted October 2, 2006 Share Posted October 2, 2006 Why in the world would he / she need to select all the records to get the newest one!Also my post got deleted from this thread, I wonder why that happen!In my deleted post, I asked what was the name of the column that holds your funny date!me! Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102496 Share on other sites More sharing options...
Daniel0 Posted October 2, 2006 Share Posted October 2, 2006 [quote author=printf link=topic=110258.msg445408#msg445408 date=1159809928]Why in the world would he / she need to select all the records to get the newest one![/quote]How would you sort them using a query if they are formatted in a strange way? Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102506 Share on other sites More sharing options...
printf Posted October 2, 2006 Share Posted October 2, 2006 It depends on the database, each has it on way of doing things! In MySQL it handles common IF() blocks, so you can pose any question you want in the SELECT part of your query and MySQL wil sort on that [b]value[/b], even complex values can be taken from as many columns as you are using in your IF(), so sorting can be done by a WHERE componet or a VALUE made from many different columns in the SELECT. No matter how complex it is, it will always be a SIMPLE query using filesort.I'll give you a quick example based on this question![code]CREATE TABLE test ( id int(10) NOT NULL auto_increment, df int(8) NOT NULL default '0', junk char(3) NOT NULL, PRIMARY KEY (id), KEY df (df)) ENGINE=MyISAM;INSERT INTO test VALUES (1, 9252006, 'abc');INSERT INTO test VALUES (2, 10122006, 'def');[/code]The query...[code]SELECT id, CAST(IF(LENGTH(df)>7,CONCAT(SUBSTRING(df,5,4), '-', SUBSTRING(df, 1, 2), '-', SUBSTRING(df,3,2)),CONCAT(SUBSTRING(df,4,4), '-', '0', SUBSTRING(df, 1, 1), '-', SUBSTRING(df,2,2))) AS DATE) AS new_date FROM test ORDER BY new_date DESC LIMIT 1;[/code]me! Quote Link to comment https://forums.phpfreaks.com/topic/22765-can-anyone-help-ill-pay-you/#findComment-102567 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.