Jerome Jerome - 2 months ago 5
MySQL Question

Foreach only computes first row and discards the rest

there are pre-defined fields and users can as well create new fields and insert values , so what I want to do is to get all the columns than remove only the pre-defined fields so that only the fields the users has created can remain in the query and i would like to get the sum of the remaining fields . The problem is that only the first row gets processed and discards the remaining rows since there are many rows that meet the where condition.

$added_income = 0;
$added_income1 = 0;

// total Salary
$result868 = mysqli_query($link, "SELECT * FROM $income WHERE Company='".$company."' AND ( Month='".$March."' OR Month='".$April."' OR Month='".$May."' OR Month='".$June."' OR Month='".$July."' OR Month='".$August."' OR Month='".$September."' OR Month='".$October."' OR Month='".$November."' OR Month='".$December."') AND Year='".$Year1."'");

$rows54 = mysqli_fetch_assoc($result868);

$removeKeys = array('ID', 'Employee_Number', 'Month', 'Year', 'Company', 'Status', 'payment_cycle', 'Payslip_Number', 'House1', 'closing', 'generated', 'Salary','Bonus','Commission','Housing_Allowance','House1','Transport_Allowance','Travel_Allowance','Vehicle_Allowance','Vehicle1','Cellphone_Allowance','Entertainment_Allowance','Company_Car','Medical_Allowance','Leave_payout','Overtime_Hours','Overtime','Cost_to_company');

foreach($removeKeys as $key) {
unset($rows54[$key]);
}

foreach($rows54 as $x => $x_value) {
$added_income = $added_income + $x_value;
}

Answer

That is because it is fetching only the first row

Put the lines

$rows54 = mysqli_fetch_assoc($result868);

$removeKeys = array('ID', 'Employee_Number', 'Month', 'Year', 'Company', 'Status', 'payment_cycle', 'Payslip_Number', 'House1', 'closing', 'generated', 'Salary','Bonus','Commission','Housing_Allowance','House1','Transport_Allowance','Travel_Allowance','Vehicle_Allowance','Vehicle1','Cellphone_Allowance','Entertainment_Allowance','Company_Car','Medical_Allowance','Leave_payout','Overtime_Hours','Overtime','Cost_to_company');

foreach($removeKeys as $key) {
   unset($rows54[$key]);
}

foreach($rows54 as $x => $x_value) {    
    $added_income = $added_income + $x_value;
}

inside a while loop like this

$added_income = 0;
$added_income1 = 0;

// total Salary 
$result868 = mysqli_query($link, "SELECT * FROM $income WHERE Company='".$company."' AND ( Month='".$March."' OR Month='".$April."' OR Month='".$May."' OR Month='".$June."' OR Month='".$July."' OR Month='".$August."' OR Month='".$September."' OR Month='".$October."' OR Month='".$November."' OR Month='".$December."') AND Year='".$Year1."'");                                             

while($rows54 = mysqli_fetch_assoc($result868)){

 $removeKeys = array('ID', 'Employee_Number', 'Month', 'Year', 'Company', 'Status', 'payment_cycle', 'Payslip_Number', 'House1', 'closing', 'generated', 'Salary','Bonus','Commission','Housing_Allowance','House1','Transport_Allowance','Travel_Allowance','Vehicle_Allowance','Vehicle1','Cellphone_Allowance','Entertainment_Allowance','Company_Car','Medical_Allowance','Leave_payout','Overtime_Hours','Overtime','Cost_to_company');

 foreach($removeKeys as $key) {
    unset($rows54[$key]);
 }

 foreach($rows54 as $x => $x_value) {    
     $added_income = $added_income + $x_value;
 }
}
Comments