regsamp
asked on
Output in PHP throwing alignment of data off issue
We have an Intranet that pulls data from MySQL written in php. When we output vacation time for certain months, anyone with the "same" last name is thrown off by the output.
If we add a . or, or any type of character after the alignment of the output is fine. I can post screenshots if needed but I am just concerned about confidentiality. Basically it looks like:
Smith, Lisa 27
Smith , John 27
Smith, Lisa 28
Smith , John 28
White, Keven 3,4,5,6,7,10,11,12,13,14 (The Smiths should have this format but do not unless a . or something is added to one of their last name.)
Is there a way to add a space in the database or someway to correct this? Any help would be appreciated.
If we add a . or, or any type of character after the alignment of the output is fine. I can post screenshots if needed but I am just concerned about confidentiality. Basically it looks like:
Smith, Lisa 27
Smith , John 27
Smith, Lisa 28
Smith , John 28
White, Keven 3,4,5,6,7,10,11,12,13,14 (The Smiths should have this format but do not unless a . or something is added to one of their last name.)
Is there a way to add a space in the database or someway to correct this? Any help would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I believe this is the code. (We have a consultant that does our programming)
<?php
require_once('Business/SessionHandler.php');
require_once('Business/FormFunctions.php');
require_once('DataAccess/daTimeOff.php');
$oDa = new daTimeOff();
$Action = getRequestFieldVal("Action");
$SrchDate = getRequestFieldVal("SrchDate");
$UserID = getRequestFieldVal("UserID");
if($SrchDate == "") $SrchDate = date("Y-m-1");
if(!$bCanViewAcct)
{
header("Location: UserErrors.php?Err=Permissions&Action=View&Access=Vacations");
exit;
}
else if(!$bCanDeleteAcct && $Action == "DeleteRec")
{
header("Location: UserErrors.php?Err=Permissions&Action=Delete&Access=Vacations");
exit;
}
writeSessionVar("VacSrchDate", $SrchDate);
if($SrchDate == "") $SrchDate = date("m/d/Y");
$MonthName = date("F", strtotime($SrchDate));
$arReasonIDs = getRequestFieldValMultiple("SrchReasonIDs");
$rsTimeOff = null;
$rsReasons = null;
$TRColor = "";
$CurUserID = "";
$bStarted = false;
$CurDates = "";
$CurName = "";
$dDateOut = null;
$sTimeOut = "";
$dDateIn = null;
$sTimeIn = "";
getTimeOffReasons();
if($Action == "DeleteRec")
{
deleteRec();
}
getTimeOffList();
function deleteRec()
{
global $sMsg;
global $oDa;
global $UserID;
global $SrchDate;
if($UserID == "") return false;
try
{
$oDa->deleteRec($UserID, $SrchDate);
$sMsg = "The record has been deleted";
}
catch(Exception $exp)
{
processError($exp->getMessage());
}
}
function getTimeOffList()
{
global $oDa;
global $SrchDate;
global $rsTimeOff;
try
{
$rsTimeOff = $oDa->getTimeOffList($SrchDate);
}
catch(Exception $exp)
{
processError($exp->getMessage());
}
}
function getTimeOffReasons()
{
global $oDa;
global $rsReasons;
try
{
$rsReasons = $oDa->getTimeOffReasons();
}
catch(Exception $exp)
{
processError($exp->getMessage());
}
}
require_once('header.php');
require_once('Business/RolodexHandler.php');
?>
<script language="javascript">
var fm = null;
function Body_Onload()
{
return;
}
function toggleRolodex()
{
var fm = document.getElementById("frmSearch");
if(fm.RolodexOn.value == "1")
{
fm.RolodexOn.value = "0";
}
else
{
fm.RolodexOn.value = "1";
}
fm.submit();
}
function editRec(UserID)
{
document.location = "acct_vac_TimeOffEditor.php?UserID=" + UserID + "&SrchDate=<?php echo $SrchDate; ?>";
}
function deleteRec(UserID)
{
var fm = document.getElementById("frmSearch");
if(confirm("Do you want to delete this time off?"))
{
fm.Action.value = "DeleteRec";
fm.UserID.value = UserID;
fm.submit();
}
}
function getList()
{
var fmSrch = document.getElementById("frmSearch");
var oFrm = document.getElementById("frmReport");
oFrm.action = "acct_vac_TimeOffList.php";
if(getFldVal(fmSrch.SrchDate) != "")
{
oFrm.SrchDate.value = getFldVal(fmSrch.SrchDate);
}
oFrm.submit();
}
function getReport()
{
var fmSrch = document.getElementById("frmSearch");
var oFrm = document.getElementById("frmReport");
oFrm.target = "winTOReport";
oFrm.action = "acct_vac_TimeOffReport.php";
if(getFldVal(fmSrch.SrchDate) != "")
{
oFrm.SrchDate.value = getFldVal(fmSrch.SrchDate);
}
oFrm.submit();
}
</script>
<table id="tblEditorMain">
<tr>
<td colspan="2">
<table class="tblNavTrack">
<tr>
<td class="tdNavTrack">
<a href="home.php" >Home</a> : <a href="acct_Default.php">Accounting</a> : Time Off
</td>
<td style="text-align:right;">
<a href="javascript:toggleRolodex();" title="Click here to display or not display the page top." class="aButton">Rolodex</a>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="tdExtLeftSideBar">
<table class="tblLeftSideBar">
<tr>
<td class="tdLeftSideBarNavTitle">Time Off</td>
</tr>
<?php
if($bCanAddAcct)
{?>
<tr>
<td class="tdLeftSideBarLink">
<a href="acct_vac_TimeOffEditor.php">Add Time Off</a>
</td>
</tr>
<?php
}?>
</table>
</td>
<td valign="top">
<table class="tblFormContainer" style="width:100%; text-align:center;">
<tr>
<td class="tdPageTitle" colspan="3">Time Off</td>
</tr>
<?php if($sErr != "" || $sMsg != ""){?>
<tr>
<td class="tdPageMsg" colspan="3">
<?php if($sErr != ""){?>
<span class="ErrorTitle">ERROR: </span> <span class="Msg"><?php echo $sErr ?></span>
<?php }?>
<?php if($sMsg != ""){?>
<span class="MsgTitle">NOTICE: </span> <span class="Msg"><?php echo $sMsg ?></span>
<?php }?>
</td>
</tr>
<?php }?>
</table>
<table class="tblFormContainer" style="width:100%; text-align:left;">
<tr>
<form id="frmSearch" name="frmSearch" action="acct_vac_Default.php" method="post">
<input type="hidden" name="Action" value="" />
<input type="hidden" name="UserID" value="" />
<input type="hidden" name="RolodexOn" value="<?php echo $arSession["RolodexOn"];?>" />
<td class="tdFormField">
<select name="SrchDate" id="SrchDate" class="formDropDown">
<?php
for($i=-3;$i <= 12; $i++)
{
$Selected = "";
if($i == 0)
{
$CurSrchDate = date("Y-m-1");
if($CurSrchDate == $SrchDate) $Selected = "selected";
echo "<option " . $Selected . " value='" . $CurSrchDate ."' >" . date("F", strtotime($CurSrchDate)) . " " . date("Y", strtotime($CurSrchDate)) . "</option>";
}
else if($i < 0)
{
$CurSrchDate = date("Y-m-1", strtotime($i . " months"));
if($CurSrchDate == $SrchDate) $Selected = "selected";
echo "<option " . $Selected . " value='" . $CurSrchDate ."' >" . date("F", strtotime($CurSrchDate)) . " " . date("Y", strtotime($CurSrchDate)) . "</option>";
}
else if($i > 0)
{
$CurSrchDate = date("Y-m-1", strtotime("+" . $i . " months"));
if($CurSrchDate == $SrchDate) $Selected = "selected";
echo "<option " . $Selected . " value='" . $CurSrchDate ."' >" . date("F", strtotime($CurSrchDate)) . " " . date("Y", strtotime($CurSrchDate)) . "</option>";
}
}
?>
</select>
<input type="submit" name="btnSrch" value="Get Month" />
</td>
</form>
</tr>
</table>
<table class="tblListRightPane">
<tr class="trListHeader" >
<?php if($bCanEditAcct)
{?>
<td class="tdListHeader" style="width:16px;" ></td>
<?php
}
if($bCanDeleteAcct)
{?>
<td class="tdListHeader" style="width:16px;" ></td>
<?php
}?>
<td class="tdListHeader" style="width:200px; text-align:left;" onclick="doSort('JobNumber');">Employee </td>
<td class="tdListHeader" style="text-align:left;" onclick="doSort('CSNumber');"> Dates Out</td>
</tr>
<?php
if($rsTimeOff != null && $rsTimeOff->num_rows > 0)
{
$CurUserID = "";
$rsTimeOff->data_seek(0);
while($row = $rsTimeOff->fetch_assoc())
{
if($CurUserID != $row["UserID"])
{
if($bStarted)
{
$CurDates = substr($CurDates, 0, strlen($CurDates) - 1);?>
<tr>
<?php
if($bCanEditAcct)
{?>
<td class="tdList" style="width:16px;">
<img class="imgLink" src="images/icoEdit.jpg" onclick="editRec('<?php echo $CurUserID; ?>');" title="Click here to edit this record." />
</td>
<?php
}
if($bCanDeleteAcct)
{?>
<td class="tdList" style="width:16px;">
<img class="imgLink" src="images/icoDelete.jpg" onclick="deleteRec('<?php echo $CurUserID; ?>');" title="Click here to delete this record." />
</td>
<?php
}?>
<td class="tdList" ><?php echo $CurName;?> </td>
<td class="tdList TextRed"> <?php echo $CurDates;?></td>
</tr>
<?php
}
$CurUserID = $row["UserID"];
$CurName = $row["LastName"] . ", " . $row["FirstName"];
$bStarted = true;
$CurDates = "";
}
if($row["DateTimeOut"] != "")
{
$CurDates .= ltrim(date("d", strtotime($row["DateTimeOut"])), 0);
if($row["TimeOut"] != "")
{
$sTimeOut = ltrim(date("h:i", strtotime($row["TimeOut"])), 0);
$sTimeOut = str_replace(":00", "", $sTimeOut);
$CurDates .= " (" . $sTimeOut . "-";
if($row["TimeIn"] != "")
{
$sTimeIn = ltrim(date("h:i", strtotime($row["TimeIn"])), 0);
$sTimeIn = str_replace(":00", "", $sTimeIn);
$CurDates .= $sTimeIn . ")";
}
}
$CurDates .= ",";
}
}
if($bStarted)
{
$CurDates = substr($CurDates, 0, strlen($CurDates) - 1);?>
<tr>
<?php
if($bCanEditAcct)
{?>
<td class="tdList" style="width:16px;">
<img class="imgLink" src="images/icoEdit.jpg" onclick="editRec('<?php echo $CurUserID; ?>', '<?php echo $SrchDate;?>');" title="Click here to edit this record." />
</td>
<?php
}
if($bCanDeleteAcct)
{?>
<td class="tdList" style="width:16px;">
<img class="imgLink" src="images/icoDelete.jpg" onclick="deleteRec('<?php echo $CurUserID; ?>', '<?php echo $SrchDate;?>');" title="Click here to delete this record." />
</td>
<?php
}?>
<td class="tdList"><?php echo $CurName;?> </td>
<td class="tdList TextRed"><?php echo $CurDates;?></td>
</tr>
<?php
}
}
else
{?>
<tr><td class="tdList" colspan="8">No records were found.</td></tr>
<?php
}
?>
</table>
<br/>
<table class="tblFormContainer" style="width:100%; text-align:left;">
<tr class="trFormTitle">
<td class="tdFormSubTitle">Time Off Report</td>
</tr>
<tr>
<form name="frmReport" id="frmReport" action="acct_vac_TimeOffReport.php" method="post">
<input type="hidden" name="UserID" value="" />
<input type="hidden" name="SrchDate" value="<?php echo $SrchDate;?>" />
<input type="hidden" name="Action" value="DeleteRec" />
<td class="tdFormField">
<select id="SrchReasons" name="SrchReasons[]" multiple class="formScrollBox">
<option value="">Select One</option>
<?php
if($rsReasons != null && $rsReasons->num_rows > 0)
{
while($row = $rsReasons->fetch_assoc())
{?>
<option value="<?php echo $row["Code"] . "|" . $row["Reason"];?>"><?php echo $row["Reason"];?></option>
<?php
}
}?>
</select>
<input type="button" onclick="getList();" name="btnRpt" value="Get List" /><input type="button" onclick="getReport();" name="btnRpt" value="Get Report" />
</td>
</form>
</tr>
</table>
</td>
</tr>
</table>
<?php require_once('footer.php'); ?>
Firstly, recommend you use CODE tags for snippets (highlight code and click CODE button on toolbar) - I have added for your last post.
Secondly, line 79 is relevant
Secondly, line 79 is relevant
$rsTimeOff = $oDa->getTimeOffList($SrchDate);
This is where the data is retrieved - however you have not given us the source for where the getTimeOffList is defined - that is what we need to see.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"Firstly, recommend you use CODE tags for snippets (highlight code and click CODE button on toolbar) - I have added for your last post." Got it. Will do from now on.
"Secondly, line 79 is relevant
$rsTimeOff = $oDa->getTimeOffList($Srch Date);
Select all
Open in new window
This is where the data is retrieved - however you have not given us the source for where the getTimeOffList is defined - that is what we need to see."
Okay, I am not sure myself. Is there anything in the code that gives a trail of where this could be? File or where in the database?
"Secondly, line 79 is relevant
$rsTimeOff = $oDa->getTimeOffList($Srch
Select all
Open in new window
This is where the data is retrieved - however you have not given us the source for where the getTimeOffList is defined - that is what we need to see."
Okay, I am not sure myself. Is there anything in the code that gives a trail of where this could be? File or where in the database?
I would use a file text search - most editors have them. Search all php files in the project folder for getTimeOffList looking for one that starts
function getTimeOffList
You could also troll through the included files - and the files included in those but I find the text search is usually the quickest.
function getTimeOffList
You could also troll through the included files - and the files included in those but I find the text search is usually the quickest.
ASKER
<?php
require_once('Business/SessionHandler.php');
require_once('Business/FormFunctions.php');
require_once('DataAccess/daTimeOff.php');
$oDa = new daTimeOff();
if(!$bCanViewAcct)
{
header("Location: UserErrors.php?Err=Permissions&Action=View&Access=Vacations");
exit;
}
$SrchDate = getRequestFieldVal("SrchDate");
writeSessionVar("VacSrchDate", $SrchDate);
if($SrchDate == "") $SrchDate = date("Y-m-1");
$MonthName = date("F", strtotime($SrchDate));
$arReasons = getRequestFieldValMultiple("SrchReasons");
$rsTimeOff = null;
$TRClass = "";
$Action = getRequestFieldVal("Action");
$CurUserID = "";
$bStarted = false;
$arReason = "";
$CurDates = "";
$CurName = "";
$dDateOut = null;
$sTimeOut = "";
$dDateIn = null;
$sTimeIn = "";
getTimeOffReport();
function getTimeOffReport()
{
global $sErr;
global $sMsg;
global $oDa;
global $SrchDate;
global $arReasons;
global $SortOrder;
global $SortFld;
global $LastSortFld;
global $rsPermits;
global $rsTimeOff;
if($SortFld == "") $SortFld = "ProjNum";
if($SortFld == $LastSortFld)
{
if($SortOrder == "ASC")
{
$SortOrder = "DESC";
}
else
{
$SortOrder = "ASC";
}
}
else
{
$SortOrder = "ASC";
}
$LastSortFld = $SortFld;
try
{
$rsTimeOff = $oDa->getTimeOffReport($SrchDate, $arReasons);
}
catch(Exception $exp)
{
processError($exp->getMessage());
}
}
function getReportType()
{
global $SortFld;
global $ReportType;
switch($SortFld)
{
case "":
$ReportType = "";
break;
}
}
require_once('header.php');
require_once('header.php');
require_once('Business/RolodexHandler.php');
?>
<script language="javascript">
var fm = null;
function Body_Onload()
{
fm = document.getElementById("frmSort");
}
function toggleRolodex()
{
RolodexOn = "<?php echo $arSession["RolodexOn"];?>";
if(RolodexOn == "1")
{
RolodexOn = "0";
}
else
{
RolodexOn = "1";
}
document.location = "acct_vac_TimeOffList.php?RolodexOn=" + RolodexOn + "&SrchDate=<?php echo $SrchDate;?>";
}
function editRec(UserID)
{
document.location = "acct_vac_TimeOffEditor.php?UserID=" + UserID + "&SrchDate=<?php echo $SrchDate;?>";
}
function deleteRec(RecordID)
{
if(confirm("Do you want to delete this permit and all it's related records?"))
{
fm.Action.value = "DeleteRec";
fm.RecordID.value = RecordID;
fm.submit();
}
}
</script>
<table id="tblEditorMain">
<tr>
<td colspan="2">
<table class="tblNavTrack">
<tr>
<td class="tdNavTrack">
<a href="home.php" >Home</a> : Time Off List
</td>
<td style="text-align:right;">
<a href="javascript:toggleRolodex();" title="Click here to display or not display the page top." class="aButton">Rolodex</a>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td valign="top">
<table class="tblListContainer">
<tr>
<td class="tdPageTitle" colspan="3">Time Off List For <?php echo $MonthName . " " . date("y", strtotime($SrchDate));?></td>
</tr>
<?php if($sErr != "" || $sMsg != ""){?>
<tr>
<td class="tdPageMsg" colspan="3">
<?php if($sErr != ""){?>
<span class="ErrorTitle">ERROR: </span> <span class="Msg"><?php echo $sErr ?></span>
<?php }?>
<?php if($sMsg != ""){?>
<span class="MsgTitle">NOTICE: </span> <span class="Msg"><?php echo $sMsg ?></span>
<?php }?>
</td>
</tr>
<?php }?>
<tr>
<td>
<table class="tblList">
<?php
if($rsTimeOff != null && $rsTimeOff->num_rows > 0)
{?>
<tr >
<td colspan="2" class="tdList">Reasons:
<?php
for($i=0;$i < count($arReasons); $i++)
{
$arReason = explode("|", $arReasons[$i]);
echo $arReason[1];
if($i < count($arReasons) - 1) echo ", ";
}?>
</td>
</tr>
<tr class="trListHeader">
<?php
if($bCanEditAcct)
{?>
<td class="tdListHeader" style="width:16px;"></td>
<?php
}?>
<td class="tdListHeader" style="width:150px;">User Name</td>
<td class="tdListHeader">Dates & Times Off</td>
</tr>
<?php
$CurUserID = "";
$rsTimeOff->data_seek(0);
while($row = $rsTimeOff->fetch_assoc())
{
if($CurUserID != $row["UserID"])
{
if($bStarted)
{
$CurDates = substr($CurDates, 0, strlen($CurDates) - 1);?>
<tr class="<?php echo $TRClass;?>" >
<?php
if($bCanEditAcct)
{?>
<td class="tdListHeader" style="width:16px;">
<img class="imgLink" src="images/icoEdit.jpg" onclick="editRec('<?php echo $CurUserID;?>');" title="Click here to edit this time off." />
</td>
<?php
}?>
<td class="tdList9" ><?php echo $CurName;?> </td>
<td class="tdList9"> <?php echo $CurDates;?></td>
</tr>
<?php
}
$CurUserID = $row["UserID"];
$CurName = $row["LastName"] . ", " . $row["FirstName"];
$bStarted = true;
$CurDates = "";
if($TRClass == "trListLine1")
{
$TRClass = "trListLine2";
}
else
{
$TRClass = "trListLine1";
}
}
if($row["DateTimeOut"] != "")
{
$CurDates .= ltrim(date("d", strtotime($row["DateTimeOut"])), 0);
if($row["TimeOut"] != "")
{
$sTimeOut = ltrim(date("h:i", strtotime($row["TimeOut"])), 0);
$sTimeOut = str_replace(":00", "", $sTimeOut);
$CurDates .= " (" . $sTimeOut . "-";
if($row["TimeIn"] != "")
{
$sTimeIn = ltrim(date("h:i", strtotime($row["TimeIn"])), 0);
$sTimeIn = str_replace(":00", "", $sTimeIn);
$CurDates .= $sTimeIn . ")";
}
}
$CurDates .= ",";
}
}
if($bStarted)
{
$CurDates = substr($CurDates, 0, strlen($CurDates) - 1);?>
<tr class="<?php echo $TRClass;?>" >
<?php
if($bCanEditAcct)
{?>
<td class="tdListHeader" style="width:16px;">
<img class="imgLink" src="images/icoEdit.jpg" onclick="editRec('<?php echo $CurUserID;?>');" title="Click here to edit this time off." />
</td>
<?php
}?>
<td class="tdList9"><?php echo $CurName;?> </td>
<td class="tdList9"><?php echo $CurDates;?></td>
</tr>
<?php
}
}?>
<tr><td colspan="2"></td></tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
<?php require_once('footer.php'); ?>[code]
[/code]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, I will probably get with the programmer or check with that group option the EE suggested. Thank you.
ASKER
It is now all set. tThe programmer just had to add the FirstName variable to the output along with the LastName and TimeOff and it worked. Thank you.
Great - and you are welcome.
You have a loop that is running through the returned records to bundle them up so the vacation days appear as a comma separated list after the name. However, the ordering on the recordset is not implemented correctly so instead of the Smith, John's appearing after each other they are interspersed with the other smiths which means the rollup is resetting when it hits a Smith with a different firstname.
I suspect the solution to the problem is to add the first name into the ordering on the query. So if your query has
Open in new window
You wantOpen in new window
This is just a guess - we would need to see the code that generates the query to confirm