Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Checking in mysql database the data before compute the deduction

    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:
    PHP Code:
     $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($Hours05);
      
    $Hours str_replace(':''.'$Hours);
      
     
    $Amount $_POST["Amount"];
     
    $Amount mysql_real_escape_string($Amount); 
     
     
    $Amount round(($Hours/8)* $Rate2); 


      
    $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...
    Last edited by newphpcoder; 11-23-2011 at 06:50 AM.

  • #2
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I tried this code: and I add Ref_No in my table

    PHP Code:
    $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
    Last edited by newphpcoder; 11-23-2011 at 06:51 AM.

  • #3
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    it is simple bro. to compare values, don't use =, but use ==
    PHP Code:

    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


  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I tried it but still only if condition satisfied:

    PHP Code:
    $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); 

  • #5
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    try to var_dump $rsSSS->fields first bro. and look value of Ref_No
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • Users who have thanked XterM for this post:

    newphpcoder (11-23-2011)

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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) }

  • #7
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    $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...:(

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    How can I get all the data of Ref_No to satisfied also the elseif and else statement..


    Thank you

  • #9
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    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.
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • Users who have thanked XterM for this post:

    newphpcoder (11-23-2011)

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by XterM View Post
    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

  • #11
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    maybe we need to look from where values of $currentEmpID.
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • Users who have thanked XterM for this post:

    newphpcoder (11-23-2011)

  • #12
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    $currentempID is based who is employee is...so every employee has a unique id...

  • #13
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    try look at where is $currentEmpID employed
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • Users who have thanked XterM for this post:

    newphpcoder (11-24-2011)

  • #14
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by XterM View Post
    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

  • #15
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    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:
    PHP Code:
    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...


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •