Jump to content

Can anyone help? I'll PAY you


Moron

Recommended Posts

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?

Link to comment
Share on other sites

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

[code]<?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);
?>[/code]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.