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.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Select Statement only the first row data was get

    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.

    Code:
    $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...
    Last edited by newphpcoder; 12-05-2011 at 09:45 AM.

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    It depends entirely on the $conn2 object and how it executes queries (the return from execute()). You should be looping through an associated array of rows, and do your conditional checks inside that loop.

    What does $conn2->execute() return?

    This is more of a PHP question btw.

  • Users who have thanked BluePanther for this post:

    newphpcoder (12-05-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I used this code:
    Code:
    $sql = "SELECT Ref_No, From_Range, To_Range, Employee_Share FROM sss";
    $rs = $conn2->Execute($sql);
    
    var_dump($rs);
    the output of var_dump is:

    object(ADORecordSet_mysql)#55 (28) { ["databaseType"]=> string(5) "mysql" ["canSeek"]=> bool(true) ["dataProvider"]=> string(6) "native" ["fields"]=> array(8) { [0]=> string(1) "1" ["Ref_No"]=> string(1) "1" [1]=> string(7) "1000.00" ["From_Range"]=> string(7) "1000.00" [2]=> string(7) "1249.99" ["To_Range"]=> string(7) "1249.99" [3]=> string(5) "33.30" ["Employee_Share"]=> string(5) "33.30" } ["blobSize"]=> int(64) ["sql"]=> string(60) "SELECT Ref_No, From_Range, To_Range, Employee_Share FROM sss" ["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(32) 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(4) ["_queryID"]=> resource(32) 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) } 0

    how can i loop it as an array??

    Thank you so much

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    $conn2 returns all data from the sss table...

    and now i dont know how can loop it or used the executing data in if, else if condition

    thank you

  • #5
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Can I see the specific code for the execute method? It looks like it's only designed to return one row, if you truly have 2 or more rows in the sss table.

  • Users who have thanked BluePanther for this post:

    newphpcoder (12-05-2011)

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BluePanther View Post
    Can I see the specific code for the execute method? It looks like it's only designed to return one row, if you truly have 2 or more rows in the sss table.


    This only my code:

    Code:
    <?php
     $sql = "SELECT Ref_No, From_Range, To_Range, Employee_Share FROM sss";
    $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;
    }    
    ?>

    Thank you so much

  • #7
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    No, I need to code for the Execute() method in the database object you're using.

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Execute is only a function from adodb mysql

  • #9
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Execute is just a function or syntax in mysql adodb and I think it is equal in mysql mysql_query()

    Thank you

  • #10
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Ah, ok. Any specific reason why you're using ADOdb?

    Regardless, you should be using GetAll() instead of Execute(). GetAll() will return a 2D array of all rows in the format $array[rownum] = array(fields).

    To confirm, change execute to GetAll() then dump $rs again.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Yes, that is correct. Same as mysql_query. Just executes the SQL and returns all rows resulting.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Quote Originally Posted by newphpcoder View Post
    Code:
    $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.
    In that code, you are only ever looking at the first record.

    You don't show any loop at all to even attempt to get multiple records.

    Where is *THAT* code?

    Your IF conditions are also way overblown They are easily compressed down to one IF. And why didn't you do them in the SQL query in any case?

    But...
    Code:
    if(    ($Ref_No == 1 || $Ref_No == 2 || $Ref_No == 3 )
        && $Totearn >= $From_Range 
        && $TotEarn <= $To_Range){
        echo $Employee_Share;
    } else{
        echo 0;
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Example:
    Code:
    $sql = "SELECT Ref_No, From_Range, To_Range, Empplyee_Share, 
              IF( Ref_No IN(1,2,3) AND $Totearn BETWEEN From_Range AND To_Range, Employee_Share, 0 ) AS actualEmployeeShare
              FROM $PAYROLL.sss, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID' ";
    $rs = $conn2->Execute($sql);
    while ( ! $rs->EOF ) /* not sure this is how you do it in PHP, but likely */
    {
        $actualEmployeeShare = $rs->Fields["actualEmployeeShare"];
        ...
        $rs->MoveNext(); /* almost surely this is correct? */
    }
    MySQL has better comparison capabilities (using IN() and BETWEEN) than does PHP, so why not do the work there?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-06-2011)

  • #14
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Now i resolved it using this code:
    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);
    Thank you

  • #15
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Example:
    Code:
    $sql = "SELECT Ref_No, From_Range, To_Range, Empplyee_Share, 
              IF( Ref_No IN(1,2,3) AND $Totearn BETWEEN From_Range AND To_Range, Employee_Share, 0 ) AS actualEmployeeShare
              FROM $PAYROLL.sss, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID' ";
    $rs = $conn2->Execute($sql);
    while ( ! $rs->EOF ) /* not sure this is how you do it in PHP, but likely */
    {
        $actualEmployeeShare = $rs->Fields["actualEmployeeShare"];
        ...
        $rs->MoveNext(); /* almost surely this is correct? */
    }
    MySQL has better comparison capabilities (using IN() and BETWEEN) than does PHP, so why not do the work there?
    Thank you for this code...later i will tried it...


  •  

    Posting Permissions

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