...

View Full Version : Select Statement only the first row data was get



newphpcoder
12-05-2011, 04:20 AM
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...

BluePanther
12-05-2011, 06:39 AM
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.

newphpcoder
12-05-2011, 08:03 AM
I used this 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

newphpcoder
12-05-2011, 08:04 AM
$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

BluePanther
12-05-2011, 09:36 AM
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.

newphpcoder
12-05-2011, 09:53 AM
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:



<?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

BluePanther
12-05-2011, 10:05 AM
No, I need to code for the Execute() method in the database object you're using.

newphpcoder
12-05-2011, 10:15 AM
Execute is only a function from adodb mysql

newphpcoder
12-05-2011, 10:37 AM
Execute is just a function or syntax in mysql adodb and I think it is equal in mysql mysql_query()

Thank you

BluePanther
12-05-2011, 09:17 PM
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.

Old Pedant
12-05-2011, 09:34 PM
Yes, that is correct. Same as mysql_query. Just executes the SQL and returns all rows resulting.

Old Pedant
12-06-2011, 01:10 AM
$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...


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

Old Pedant
12-06-2011, 01:24 AM
Example:


$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?

newphpcoder
12-06-2011, 02:28 AM
Now i resolved it using this 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

newphpcoder
12-06-2011, 05:00 AM
Example:


$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...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum