Jump to content


Photo

Can anyone help? I'll PAY you


  • Please log in to reply
6 replies to this topic

#1 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 02 October 2006 - 04:45 PM

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?



#2 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 02 October 2006 - 05:15 PM

I don't know mssql, but just select all of them, but based on your code, try something this:

<?php
function 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 EH
ON EH.[EEMPNO]=M2.EMPNO

WHERE 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);
?>


#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 02 October 2006 - 05:21 PM

russ, here you go:
<?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
?>

obviously, with you using mssql, your function calls would be slightly different, but the idea still applies to the sort.

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 02 October 2006 - 05:23 PM

Some replies are missing o_O

#5 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 02 October 2006 - 05:25 PM

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!

#6 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 02 October 2006 - 05:33 PM

Why in the world would he / she need to select all the records to get the newest one!


How would you sort them using a query if they are formatted in a strange way?

#7 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 02 October 2006 - 06:41 PM

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 value, 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!


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');

The query...

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;


me!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users