...

View Full Version : Checking in mysql database the data before compute the deduction



newphpcoder
11-23-2011, 03:48 AM
Good day!

I have table in my database which I could check the range of compensatin,monthly salary credit, monthly contribution.

Now i am in the process of analyzing how can I get the monthly contribution of employee it is depend on their basic pay.

I have this code for getting the basic pay of employee:


$sql = "SELECT em.EMP_NO, w.RATE, e.Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.earnings e WHERE w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' AND e.EMP_NO = em.EMP_NO";
$RsEarnings = $conn2->Execute($sql);

$Rate = trim($RsEarnings->fields['RATE']);
$Hours = trim($RsEarnings->fields['Hours']);

$Hours = substr($Hours, 0, 5);
$Hours = str_replace(':', '.', $Hours);

$Amount = $_POST["Amount"];
$Amount = mysql_real_escape_string($Amount);

$Amount = round(($Hours/8)* $Rate, 2);


$smarty->assign('Rate', $Rate);
$smarty->assign('Hours', $Hours);
$smarty->assign('Amount', $Amount);


the Amount is the Basic Pay and it is only display the basic pay not yet save in the database.

and I have table deduction that has fields:
RangeCompensation varchar
MonthlySalaryCredit varchar
MonthlyContribution varchar

Example data:
1000 - 1249.99 1000 33.30
1250 - 1749.99 1500 50.00
1750 - 2249.99 2000 66.70 and so on.....

The deduction is base on the Amount, what should be my query or condition to check if what would be his monthlydeduction.

For example Amount or Basic pay is 5422.5

Where should I base is it in MonthlySalaryCredit? or should separate the rangecompensation into two then i will based on that? to get his monthly deduction


Thank you so much...

newphpcoder
11-23-2011, 06:15 AM
I tried this code: and I add Ref_No in my table



$sql = "SELECT em.EMP_ID, em.EMP_NO, s.Ref_No s.RangeCompensation, s.MonthlySalaryCredit, s.EmployeeShare
FROM $PAYROLL.sss s, $ADODB_DB.employmet em
WHERE em.EMP_ID = '$currentEmpID'";
$rsSSS = $conn2->Execute($sql);

$Ref_No = $rsSSS->fields['Ref_No'];

if($Ref_No = 1){
$SSS = (80.70 / 2);
}
elseif($Ref_No = 10){
$SSS = (183.30 / 2);
}
elseif($Ref_No = 8){
$SSS = (150.00 / 2);
}
else{
$SSS = 0;
}

$smarty->assign('SSS', $SSS);



and the result is:

the $SSS has always a value of 40.35, that's means that only the if statement was read...

What's wrong in my syntax?

Thank you

XterM
11-23-2011, 06:51 AM
it is simple bro. to compare values, don't use =, but use ==



if($Ref_No == 1){
$SSS = (80.70 / 2);
}
elseif($Ref_No == 10){
$SSS = (183.30 / 2);
}
elseif($Ref_No == 8){
$SSS = (150.00 / 2);
}
else{
$SSS = 0;
}

newphpcoder
11-23-2011, 06:56 AM
I tried it but still only if condition satisfied:



$sql = "SELECT em.EMP_ID, em.EMP_NO, s.Ref_No, s.RangeCompensation, s.MonthlySalaryCredit, s.EmployeeShare
FROM $PAYROLL.sss s, $ADODB_DB.employment em
WHERE em.EMP_ID = '$currentEmpID'";
$rsSSS = $conn2->Execute($sql);

$Ref_No = $rsSSS->fields['Ref_No'];


if ($Ref_No == 1){
$SSS = (80.70 / 2);
}
elseif ($Ref_No == 2){
$SSS = (50.00 / 2);
}
elseif ($Ref_No == 3){
$SSS = (66.70 / 2);
}
elseif ($Ref_No == 4){
$SSS = (83.30 / 2);
}
elseif ($Ref_No == 5){
$SSS = (100.00 / 2);
}
elseif ($Ref_No == 6){
$SSS = (116.70 / 2);
}
elseif ($Ref_No == 7){
$SSS = (133.30 / 2);
}
elseif ($Ref_No == 8){
$SSS = (150.00 / 2);
}
elseif ($Ref_No == 9){
$SSS = (166.70 / 2);
}
elseif ($Ref_No == 10){
$SSS = (183.30 / 2);
}
else {
$SSS = 0;
}
$smarty->assign('SSS', $SSS);

XterM
11-23-2011, 07:00 AM
try to var_dump $rsSSS->fields first bro. and look value of Ref_No

newphpcoder
11-23-2011, 07:11 AM
This is the output when i var_dump ($rsSSS);

object(ADORecordSet_mysql)#33 (28) { ["databaseType"]=> string(5) "mysql" ["canSeek"]=> bool(true) ["dataProvider"]=> string(6) "native" ["fields"]=> array(12) { [0]=> string(6) "000003" ["EMP_ID"]=> string(6) "000003" [1]=> string(8) "09900215" ["EMP_NO"]=> string(8) "09900215" [2]=> string(1) "1" ["Ref_No"]=> string(1) "1" [3]=> string(14) "1000 - 1249.99" ["RangeCompensation"]=> string(14) "1000 - 1249.99" [4]=> string(4) "1000" ["MonthlySalaryCredit"]=> string(4) "1000" [5]=> string(5) "33.30" ["EmployeeShare"]=> string(5) "33.30" } ["blobSize"]=> int(64) ["sql"]=> string(183) "SELECT em.EMP_ID, em.EMP_NO, s.Ref_No, s.RangeCompensation, s.MonthlySalaryCredit, s.EmployeeShare FROM payroll.sss s, hris.employment em WHERE em.EMP_ID = '000003'" ["EOF"]=> bool(false) ["emptyTimeStamp"]=> string(6) " " ["emptyDate"]=> string(6) " " ["debug"]=> bool(false) ["timeCreated"]=> int(0) ["bind"]=> bool(false) ["fetchMode"]=> int(3) ["connection"]=> object(ADODB_mysql)#4 (66) { ["databaseType"]=> string(5) "mysql" ["dataProvider"]=> string(5) "mysql" ["hasInsertID"]=> bool(true) ["hasAffectedRows"]=> bool(true) ["metaTablesSQL"]=> string(11) "SHOW TABLES" ["metaColumnsSQL"]=> string(20) "SHOW COLUMNS FROM %s" ["fmtTimeStamp"]=> string(13) "'Y-m-d H:i:s'" ["hasLimit"]=> bool(true) ["hasMoveFirst"]=> bool(true) ["hasGenID"]=> bool(true) ["upperCase"]=> string(5) "upper" ["isoDates"]=> bool(true) ["sysDate"]=> string(9) "CURDATE()" ["sysTimeStamp"]=> string(5) "NOW()" ["hasTransactions"]=> bool(false) ["forceNewConnect"]=> bool(false) ["poorAffectedRows"]=> bool(true) ["clientFlags"]=> int(0) ["dbxDriver"]=> int(1) ["__db"]=> array(1) { [10]=> string(7) "payroll" } ["_genIDSQL"]=> string(38) "update %s set id=LAST_INSERT_ID(id+1);" ["_genSeqSQL"]=> string(33) "create table %s (id int not null)" ["_genSeq2SQL"]=> string(26) "insert into %s values (%s)" ["_dropSeqSQL"]=> string(13) "drop table %s" ["database"]=> string(7) "payroll" ["host"]=> string(9) "localhost" ["user"]=> string(4) "root" ["password"]=> string(0) "" ["debug"]=> bool(false) ["maxblobsize"]=> int(256000) ["concat_operator"]=> string(1) "+" ["fmtDate"]=> string(7) "'Y-m-d'" ["true"]=> string(1) "1" ["false"]=> string(1) "0" ["replaceQuote"]=> string(2) "\'" ["charSet"]=> bool(false) ["hasTop"]=> bool(false) ["readOnly"]=> bool(false) ["genID"]=> int(0) ["raiseErrorFn"]=> bool(false) ["cacheSecs"]=> int(3600) ["arrayClass"]=> string(18) "ADORecordSet_array" ["noNullStrings"]=> bool(false) ["numCacheHits"]=> int(0) ["numCacheMisses"]=> int(0) ["pageExecuteCountRows"]=> bool(true) ["uniqueSort"]=> bool(false) ["leftOuter"]=> bool(false) ["rightOuter"]=> bool(false) ["ansiOuter"]=> bool(false) ["autoRollback"]=> bool(false) ["fnExecute"]=> bool(false) ["fnCacheExecute"]=> bool(false) ["blobEncodeType"]=> bool(false) ["_oldRaiseFn"]=> bool(false) ["_transOK"]=> NULL ["_connectionID"]=> resource(10) of type (mysql link persistent) ["_errorMsg"]=> string(0) "" ["_queryID"]=> resource(23) of type (mysql result) ["_isPersistentConnection"]=> bool(true) ["_bindInputArray"]=> bool(false) ["autoCommit"]=> bool(true) ["transOff"]=> int(0) ["transCnt"]=> int(0) ["fetchMode"]=> bool(false) ["databaseName"]=> string(7) "payroll" } ["_numOfRows"]=> int(29) ["_numOfFields"]=> int(6) ["_queryID"]=> resource(23) of type (mysql result) ["_currentRow"]=> int(0) ["_closed"]=> bool(false) ["_inited"]=> bool(true) ["_obj"]=> NULL ["_names"]=> NULL ["_currentPage"]=> int(-1) ["_atFirstPage"]=> bool(false) ["_atLastPage"]=> bool(false) ["_lastPageNo"]=> int(-1) ["_maxRecordCount"]=> int(0) ["dateHasTime"]=> bool(false) }

newphpcoder
11-23-2011, 07:31 AM
$Ref_No has ["Ref_No"]=> string(1) "1"

That's why only the if condition was satisfied...

I don't know how can I get the monthly deduction...:(

newphpcoder
11-23-2011, 07:34 AM
How can I get all the data of Ref_No to satisfied also the elseif and else statement..


Thank you

XterM
11-23-2011, 07:43 AM
i feel there is something in your queries.

$currentEmpID <--- are you sure this value is uniq?

i worried you run the query with same $currentEmpID, so return always 1.

newphpcoder
11-23-2011, 07:46 AM
i feel there is something in your queries.

$currentEmpID <--- are you sure this value is uniq?

i worried you run the query with same $currentEmpID, so return always 1.

Yes, it's unique....I think there's something in my queries and i can't figured out:(


Thank you

XterM
11-23-2011, 09:19 AM
maybe we need to look from where values of $currentEmpID.

newphpcoder
11-23-2011, 09:35 AM
$currentempID is based who is employee is...so every employee has a unique id...

XterM
11-23-2011, 09:39 AM
try look at where is $currentEmpID employed

newphpcoder
11-24-2011, 12:19 AM
try look at where is $currentEmpID employed

The $currentEmpID is equal to employee who I click or choose in my navagation bar......but still it is always the first employee id was get.


Thank you so much

newphpcoder
12-01-2011, 02:14 AM
Now I resolved my problem by manually coding of formula in php not from in database. but i need to get the range from the database.

I have this field in my table to check the range and the amount of deduction.

Ref_No
From_Range
To_Range
Salary_Credit
Employee_Share

this is my sample code from php:


if ($TotEarn >= 1000 && $TotEarn <= 1249.99 ) { //, $TotEarn is not yet save in database it is internally computed in php code, From_Range = 1000 To_Range = 1249.99
$SSS = (33.30); // Employee_Share = 33.30
}
elseif ($TotEarn >= 1250 && $TotEarn <= 1749.99) {
$SSS = (50.00);
}
else
{
$SSS = 0;
}


I don't know how can I get the data from database to check the range and get the employee share based on their total eranings.

Thank you so much...

newphpcoder
12-01-2011, 03:37 AM
i tried this query:



$sql = "SELECT em.EMP_ID, s.Ref_No, s.From_Range, s.To_Range, s.Salary_Credit, s.Employee_Share
FROM $PAYROLL.sss s , $ADODB_DB.employment em
WHERE em.EMP_ID = '$currentEmpID'";
$rsSSS = $conn2->Execute($sql);

$Ref_No = $rsSSS->fields['Ref_No'];
$EMP_ID = $rsSSS->fields['EMP_ID'];

var_dump($Ref_No);
var_dump($EMP_ID);


but still the Ref_No always has a value of 1 and I don't know waht can i put in my condition statement to check if the totearn is between the range then the employee_share is equal to designated value.

Thank you

newphpcoder
12-01-2011, 07:30 AM
Nope...

I will attach my database....for further understanding..

Thank you

newphpcoder
12-01-2011, 08:17 AM
I tried this code:


$sql = "SELECT em.EMP_ID, s.Ref_No, s.From_Range, s.To_Range, s.Salary_Credit, s.Employee_Share
FROM $PAYROLL.sss s , $ADODB_DB.employment em
WHERE em.EMP_ID = '$currentEmpID' GROUP BY s.Ref_No";

$rsSSS = $conn2->Execute($sql);

if ($rsSSS === false) die("failed");
while (!$rsSSS->EOF) {
for ($i=0, $max=$rsSSS->FieldCount(); $i < $max; $i++)

$Ref_No = $rsSSS->fields['Ref_No'];
$EMP_ID = $rsSSS->fields['EMP_ID'];
$From_Range = $rsSSS->fields['From_Range'];
$To_Range = $rsSSS->fields['To_Range'];
$Employee_Share = $rsSSS->fields['Employee_Share'];
if ($Ref_No == 1 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range ) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 2 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 3 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 4 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 5 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 6 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 7 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 8 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 9 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 10 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 11 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 12 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 13 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
elseif ($Ref_No == 14 AND $TotEarn >= $From_Range && $TotEarn <= $To_Range) {
$SSS = $Employee_Share;
}
else {
$SSS = 0;
}
$rsSSS->MoveNext();
}


but the output is 0,



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum