[SOLVED] Help with this query, please?

[Moron]

while ($RESULT = mssql_fetch_assoc($RESULTDS)) {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);

$Date = $month."/".$day."/".$year; 

echo "<a href=\"paystubpopup.php?Date=".$endingdate."\" target=\"_blank\">";

echo $Date;

echo "</a>";

echo "<BR>";

 

It works perfectly, EXCEPT..... if there is more than one database entry for a certain date, it only displays one of them.

 

Ideas?

[PFMaBiSmAd]

Help with what query? My guess is that your actual query is only retrieving one of each date.

[Brian W]

Show your code for the query...

is $RESULTDS the correct variable? something about the D after the T looks odd

[Moron]

Help with what query? My guess is that your actual query is only retrieving one of each date.

 

Here's the actual query:

 

$RESULTDS=mssql_query("SELECT DISTINCT LH.[EMPNO], M2.[hrYRAT], M2.[EMPNO], M2.[MANLAP], M2.[PAYCTR], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF], EH.[DATE], EH.[ENETPA], EH.[EGRSER], EH.[EREGHR], EH.[EDEDUC], EH.[EROTHR], EH.[EFWHD], EH.[EPEDAT], EH.[ESSDED], EH.[EHOSPD], EH.[ELIFED], EH.[ECRUD], M2.[POSITN], M2.[MCTDWH], M2.[MHDATE], M2.[MCTDCS], M2.[MO3TOT], M2.[MCTDLD], M2.[MCTDGE], M2.[MALPPP], M2.[MSLPPP], M2.[MWHSTA], M2.[MWHALL], M2.[MWHADD], EH.[EGARND], EP.[EMPNO], DP.[EMPNO], EI.[DDEPTN], EI.[NEWOCC], EI.[hrYRAT], EI.[MEMPAD], EI.[MEMPCS], EI.[MEMPZI], PA.[PSTUB1], PA.[PSTUB5], PA.[PSTUB6], PA.[PSTUB2], PA.[PSTUB3], PA.[PSTUB4], PA.[PSTUB7], PA.[PSTUB8], PA.[PSTUB9], PA.[PSTU10], PA.[PSTU11], PA.[PSTU12], PA.[PSTU13], PA.[PSTU14], PA.[PSTU15], PA.[PSTU16], PA.[PSTU17], PA.[PSTU18], PA.[PSTU19], PA.[PSTU28], PA.[PSTU29], PA.[PSTU30], PA.[PSTU20], PA.[PSTU21], PA.[PSTU30], PA.[PSTU26], PA.[PSTU31], PA.[PSTU36], PA.[PSTU37], PA.[PSTU42], PA.[PSTU43], PA.[PSTU44],  PA.[PSTU45], PA.[PSTU46], PA.[PSTU47], PA.[PSTU48], PA.[PSTU22], PA.[PSTU23], PA.[PSTU24], PA.[PSTU25], PA.[PSTU27], PA.[PSTU32], PA.[PSTU33], PA.[PSTU34], PA.[PSTU35], PA.[PSTU49], PA.[PSTU50], PA.[PSTU51], PA.[PSTU52], PA.[PSTU53], PA.[PSTU38], PA.[PSTU39], PA.[PSTU54], PA.[PSTU55], PA.[PSTU57]

FROM MASTERL2 M2 

LEFT JOIN LeaveHistory LH 
ON LH.[EMPNO]=M2.EMPNO  LEFT JOIN EARNHIST EH
ON EH.[EEMPNO]=M2.EMPNO LEFT JOIN EMPPICTURE EP
ON EP.[EMPNO]=EH.EEMPNO LEFT JOIN Departments DP
ON DP.[EMPNO]=EP.EMPNO LEFT JOIN View_EmployeeInfo EI
ON EI.[EMPNO]=M2.EMPNO LEFT JOIN PAYSTUBS_Archive PA
ON PA.[PSTUB2]=M2.EMPNO

WHERE PA.[PSTUB2] = '".$_SESSION['empcode']."' 


ORDER BY PA.[PSTU57] desc"); 

$RESULT=mssql_fetch_assoc($RESULTDS);

 

Is there some reason it wouldn't be pulling all of the records?

 

On mine, I have two records for Nov 29 and one for November 15. It pulls the November 15 record and ONE for the 29th.

 

[Moron]

Show your code for the query...

is $RESULTDS the correct variable? something about the D after the T looks odd

 

Yes, $RESULTDS is correct. The "DS" part is an acronym for my department. Everything works except when there is more than one record for a given date. That's why I suspect that the first code I posted might be the problem instead of the query.

 

[PFMaBiSmAd]

 

 

DISTINCT removes duplicate rows from the result set.

[Moron]

 

 

DISTINCT removes duplicate rows from the result set.

 

Thank you! That did the trick! I was using code from a previous query and I had used DISTINCT for whatever reason at the time.

 

 

[Moron]

There is still a problem.... removing DISTINCT from the query makes it list all records, but both links for the 29th bring up the same record.

 

How can I make this:

while ($RESULT = mssql_fetch_assoc($RESULTDS)) {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);

$Date = $month."/".$day."/".$year; 

echo "<a href=\"paystubpopup.php?Date=".$endingdate."\" target=\"_blank\">";

echo $Date;

echo "</a>";

echo "<BR>";

.... distinguish between the two?

 

 

[PFMaBiSmAd]

You would need to use something in the data that identifies what is different about the two records with the same date. You should probably be using an id as a parameter on the end of the URL instead of the date.

[Moron]

You would need to use something in the data that identifies what is different about the two records with the same date. You should probably be using an id as a parameter on the end of the URL instead of the date.

 

The check number and date cannot be the same, so the check number can be the distinguishing field. How do I express that here?

 

while ($RESULT = mssql_fetch_assoc($RESULTDS)) {

$endingdate = $RESULT['PSTUB5'];
$month = substr("$endingdate", -8, 2);
$day = substr("$endingdate", -6, 2);
$year = substr("$endingdate", -4, 4);

$Date = $month."/".$day."/".$year; 

echo "<a href=\"paystubpopup.php?Date=".$endingdate."\" target=\"_blank\">";

echo $Date;

echo "</a>";

 

i.e.  How do I add the check number field to the URL variable?

 

[Moron]

Never mind. Total bonehead error. I was using a second query on the same page and it was missing a necessary element.

 

Thanks to all for your help, though!