Checking in mysql database the data before compute the deduction

[newphpcoder]

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]

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

[guido2004]

 $Amount = mysql_real_escape_string($Amount); 

Using mysql_real_escape_string on a value that has to be numeric makes no sense. And even less because on the next line you are giving $Amount another value.

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

You should definitely separate the range column in a startRange and endRange column.
But what to base your query on to get the monthly deduction, you are the one that should know that. If you don't know, ask the person that told you to do this calculation.

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

I don't know. If it always enters the first IF, it means your query always returns 1 for that column. Shouldn't it? Try the query in phpMyAdmin. Does it work?

[newphpcoder]

The Ref_No has only one string, that's why ony the if condition was satisfied...


To know the monthly deduction first check if the amount is between the rangecompensation which has a equal value of monthly deduction..

But how can I check it if the Amount is between the range? and get the monthly deduction..


Thank you

[guido2004]

But how can I check it if the Amount is between the range?

Separate the range column in a startRange and endRange column.

and get the monthly deduction..

I don't know. How do you calculate the monthly deduction? Please post the calculation (the formula) based on the column names in your database.

[newphpcoder]

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]

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]

Good day!

I need to have an if and elseif condition and the data check in condition came from database.

I have 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' GROUP BY s.Ref_No"; 

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

$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'];

I attach my syntax for sss table.

i need to get the From_Range, To_Range and Employee_Share

and I used the Ref_No to check what I need data.

The problem is always 1 is the output of Ref_No even the EMP_ID was change


Thank you

[newphpcoder]

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,

I attach my database for reference

[r937]

why did you add GROUP BY to your SELECT?

i suspect possibly because your join is incorrect -- you are joining what looks like one row from the employment table to ~all~ rows in the sss table

[newphpcoder]

Yes, i think my query is wrong..., I join only because I need to check if what is the EMP_ID.And also I only test to add group by and its wrong...


I don't how can I correct my syntax to check the range and to compare it in totEarn and get the Employee share..

Thank you so much...

[newphpcoder]

SSS table only has the data to check and get the deduction based on the totalearnings of employee and to know who is employee I used emp_id...

Thank you so much..

[r937]

i advise you to get your query working properly outside of php before incorporating it into your php code

[newphpcoder]

I test this query in mysql:

SELECT em.EMP_ID, s.Ref_No, s.From_Range, s.To_Range, s.Salary_Credit, s.Employee_Share
        FROM payroll.sss s, hris.employment em  
        WHERE em.EMP_ID = '000002';

and it display all data...

I attach the image of output of this query..


Thank you...

[newphpcoder]

So i don't why when I var_dump the $Ref_No it has only 1 output..

Thank you

[infoxicated]

This is not a php issue, it's a mySQL one.

You have two tables but are not joining them.

[newphpcoder]

Good day!

I have table for the list of range and the equal deduction based on their total earnings.

here is the fields from table sss

Ref_No
From_Range
To_Range
Salary_Credit
Employee-Share

Sample data
Ref_No 1
From_Range 1000
To_Range 2000
Salary Credit 1500
Employee Share 50

Ref_No 2
From_Range 3000
To_Range 4000
Salary Credit 3500
Employee Share 150

And so on..

I have this test code to check if all data was selected for reference in condition to get the employee share based on their totalernings.:

$TotEarn is not already save in the database, it was only display in the webpage.

$TotEarn = round(($Amount + $OTReg_Amt + $SunReg_Amt + $OTSun_Amt + $HolReg_Amt + $HolRegOT_Amt + $HolLeave_Amt + $NP_Amt + $Meal_Amt + $Cola_Amt), 2);

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

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

$Ref_No = $rs->fields['Ref_No'];
$From_Range = $rs->fields['From_Range'];
$To_Range = $rs->fields['To_Range'];
$Employee_Share = $rs->fields['Employee_Share'];       
                                                        

if($Ref_No == 1 AND $Totearn >= $From_Range AND $TotEarn <= $To_Range){
    echo $Employee_Share;
}
elseif($Ref_No == 2 AND $TotEarn >= $From_Range AND $TotEarn <= $To_Range){
    echo $Employee_Share;
}
elseif($Ref_No == 3 AND $TotEarn >= $From_Range AND $TotEarn <= $To_Range){
    echo $Employee_Share;
}
else{
    echo 0;
}

the result is only one data has the correct outoput and the rest the output is 0.

How can I get all the data not only the first row in the database, because i need to used it in if, elseif , else condition.

Thank you so much...

[r937]

haven't i seen this same problem before?

a couple of times?

why are you still posting new threads for this same problem?

are you sure your join is working correctly?

[newphpcoder]

what do you mean join?

I have no join in my query...

Sorry for posting as new thread...coz i need to solve it..

Thank you

[newphpcoder]

Should I need to join it???where???
I only have that table for the reference and getting data for the deduction..

Thank you so much..i hope you can help me..

[newphpcoder]

i could not join them because the first table has only data that i need to get the deduction and no need to put an employee id on that..so theres no fields that they both have...so i could not join them..

i only want is to work my condition..my problem is cin my query, only the first row executed.


Thank you

[newphpcoder]

I used this query:

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

I need to used EMP_ID in where clause to check if who's employee should have that total earnings.

My problem is how can i get the data to check the range of totearn and get the employeeshare.


Thank you so much..

I'm sorry im not too good in mysql query..

[newphpcoder]

I used this query:

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

I need to used EMP_ID in where clause to check if who's employee should have that total earnings.

My problem is how can i get the data to check the range of totearn and get the employeeshare.


Thank you so much..

I'm sorry im not too good in mysql query..

[guido2004]

WHERE em.EMP_ID = '$currentEmpID'
AND $amount BETWEEN From_Range AND To_Range

[newphpcoder]

Thank you for your suggested codes..

it works

this is my new code:

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