Insert calculated value to mysql table

[kingot]

Hi to All,

I am performing calculation which i echo it in the input form to insert it to DB table
The calculation works fine when i submit the form but the it does not correct calculation to the DB.
it seems that the isert to database is done before the calculation and I can't figure a way around it.

Because i'm submitting to the same page, the calculation populate in the input form correctly but it insert Zero to the database table instead of correct calculation populated in the input field to DB table

You could see from the line 114 to 116 that, i'm performing some calculations and echo it at line 128 and line 128 in the input form value field.

Please any help on how to do this...All the function in the code is in another file and it works fine...so the only program is that the calculated value is not inserted as expected

Here is the code

<?php
include 'core/initForMainLogPage.php';


if(isset($_GET['empId']) && !empty($_GET['empId'])){

//delete employee here
$empId=$_GET['empId'];
grabEmpId($empId);
}
?>

<?php
if(logged_in()){
$data=user_dataManager('username');
$usernameData=$data['username'];
}else{
header('Location: index.php');
}
?>
<?php include 'includes/adminHeadAll.php';?>

<header>
<?php include 'includes/managerMenu.php';?>  
</header>
<div class="container">
<br/>
<h3>Pay Employee</h3>
<?php
$error=array();
$errorAll='';
$leave="";
if(isset($_POST['empId']) && isset($_POST['name']) && isset($_POST['date']) && isset($_POST['basicSalary'])
&& isset($_POST['leave']) && isset($_POST['salaryPerDay']) && isset($_POST['leaveDeduct']) 
&& isset($_POST['netSalary'])){
$empId=htmlentities(mysql_real_escape_string($_POST['empId']));
$name=htmlentities(mysql_real_escape_string($_POST['name']));
$date=htmlentities(mysql_real_escape_string($_POST['date']));
$basicSalary=htmlentities(mysql_real_escape_string($_POST['basicSalary']));
$leave=htmlentities(mysql_real_escape_string($_POST['leave']));
$salaryPerDay=htmlentities(mysql_real_escape_string($_POST['salaryPerDay']));
$leaveDeduct=htmlentities(mysql_real_escape_string($_POST['leaveDeduct']));
$netSalary=htmlentities(mysql_real_escape_string($_POST['netSalary']));



//checking for the validity of data entered
if(empty($leave) || empty($date)){
$error[]='Pleave leave or date field is empty.';
}else{

if(preg_match('/[0-9]/',$leave)==false){
$error[]='Leave should only contain numbers'; 
}

if(empId($empId)===false){
$error[]="This employee is not recoganize by the system and can not be paid,he may need to register first.";
}

}

if(!empty($error)){
$errorAll= '<div class="error"><ul><li>'.implode('</li><li>',$error).'</li></ul></div>';


}else{
//this funciton insert into database
payrollData($name,$empId,$date,$basicSalary,$leave,$salaryPerDay,$leaveDeduct,$netSalary);
echo '<p class="pa">Payment made successfully.   <a href="employees-salary-report.php">See Payment Records</a></p>';
}


}//end isset


?>

<div class="tableWrap">
<form action="" method="post" >
<div class="styletable"><table cellpadding="" cellspacing="" border="0">
<?php
$query=mysql_query("SELECT 
			  empId,name,level,company.compId,company.levelOne,company.levelTwo,
			  company.levelThree,company.levelFour,company.levelFive
			   FROM employee JOIN company ON company.compId=1 WHERE empId='$empId' LIMIT 1");


while($row=mysql_fetch_array($query)){
  $empId=$row['empId'];
  $name=$row['name'];
  $levelEmp=$row['level'];
  $levelOne=$row['levelOne'];
  $levelTwo=$row['levelTwo'];
  $levelThree=$row['levelThree'];
  $levelFour=$row['levelFour'];
  $levelFive=$row['levelFive'];
  
  if($levelEmp==1){
	  $levelPay=$levelOne;
  }elseif($levelEmp==2){
	  $levelPay=$levelTwo;
  }elseif($levelEmp==3){
	  $levelPay=$levelThree;
  }elseif($levelEmp==4){
	  $levelPay=$levelFour;
  }elseif($levelEmp==5){
	  $levelPay=$levelFive;
  }
  


//making calculations here

$basicSalary=$levelPay * 30;
$leaveDeduct=$leave * $levelPay;
$netSalary=$basicSalary - $leaveDeduct;
}


?>
<tr><td>Employee ID: </td><td><input type="text" name="empId" readonly="readonly" value="<?php if(isset($empId)){echo $empId;}?>"></td></tr>
<tr><td>Employee: </td><td><input type="text" name="name" readonly="readonly" value="<?php if(isset($name)){ echo $name;}?>"></td></tr>
<tr><td>Date: </td><td><input type="text" id="Date" class="picker" name="date"></td></tr>

<tr><td> Basic Salary: </td><td><input type="text" name="basicSalary" readonly="readonly" value="<?php echo $basicSalary;?>"></td></tr>
<tr><td> No. Of Absent: </td><td><input type="text" name="leave" class="input" value=""></td></tr>
<tr><td> Salary Per Day:</td><td><input type="text" name="salaryPerDay" readonly="readonly" value="<?php echo $levelPay;?>"></td></tr>
<tr><td> Deduction For Absentee:</td><td><input type="text" name="leaveDeduct" readonly="readonly" value="<?php echo $leaveDeduct;?>"></td></tr>
<tr><td> Net Salary:</td><td><input type="text" name="netSalary" readonly="readonly" value="<?php echo $netSalary;?>"></td></tr>
<tr><td> </td><td><input type="submit" value="Submit Pay" class="submit" name="pay"></td></tr>
</table></div>

</form>
<?php



?>
</div>
<br />
<?php echo $errorAll; ?>
<p>Manage the monthly salary details of your employee along with the allowances, deductions, etc. by just entering their leave</p>
</div> 
<?php include 'includes/footerAll.php';?>  

<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/jquery-ui.js"></script>
<script type="text/javascript" src="js/ui.js"></script>
</body>
</html>

[Ch0cu3r]

After this

if(isset($_POST['empId']) && isset($_POST['name']) && isset($_POST['date']) && isset($_POST['basicSalary'])
&& isset($_POST['leave']) && isset($_POST['salaryPerDay']) && isset($_POST['leaveDeduct']) 
&& isset($_POST['netSalary']))
{

Add the following. It will show what was submitted to page

printf('<pre>%s</pre>', print_r($_POST, 1));

Are the correct values shown for all inputs?

 

If they are all correct. Then you need to debug your payrollData() function. Can you post the code for that?

[kingot]

here is the result

Array(    [empId] => 7    [name] => Baddo Blessed Lord    [date] => 12/15/2014    [basicSalary] => 2400    [leave] => 3    [salaryPerDay] => 80    [leaveDeduct] => 0    [netSalary] => 2400    [pay] => Submit Pay)

I put  printf('<pre>%s</pre>', print_r($_POST, 1));

 

at the top of the isset post check...

 

but the input field shows correct values after i submit the form

 

Here is insert function

 

// insert into payroll
    function payrollData($name,$empId,$date,$basicSalary,$leave,$salaryPerDay,$leaveDeduct,$netSalary){
        $name=htmlentities(mysql_real_escape_string($name));
        $empId=htmlentities(mysql_real_escape_string($empId));
         $date=htmlentities(mysql_real_escape_string($date));
         $basicSalary=htmlentities(mysql_real_escape_string($basicSalary));
         $leave=htmlentities(mysql_real_escape_string($leave));
         $salaryPerDay=htmlentities(mysql_real_escape_string($salaryPerDay));
         $leaveDeduct=htmlentities(mysql_real_escape_string($leaveDeduct));
         $netSalary=htmlentities(mysql_real_escape_string($netSalary));
        
        mysql_query("INSERT INTO payroll VALUES('','$name','$empId','$date','$basicSalary','$leave','$salaryPerDay',
                    '$leaveDeduct','$netSalary')") or mysql_error();
    }

[CroNiX]

You are running everything through htmlentities() and mysql_real_escape_string() twice for each value. Once when you retrieve from $_POST and the other in your payrollData() function. Why? Just do it once, and you should only run through mysql_real_escape_string() when you are actually going to insert them in the db since it potentially alters the value.

[Ch0cu3r]

mysql_real_escape_string() should only be used on string values (hence the word string in its name) .

 

If you are inserting a number into the database then at least check that is a number first (using ctype_digit or filter_var with the appropriate filter flag) before using it in your query.

Also do not use mysql_ functions as they are deprecated meaning they are no longer supported and could be removed from future versions of PHP. You need to update your code to use PDO or MySQLi and use prepared statements when using user input in your  queries.

[mac_gyver]

I am performing calculation which i echo it in the input form to insert it to DB table

 

 

you should NOT use calculated values that come from the browser, as they can be set to anything that anyone wants. any value you calculate and output to the browser should be for display only. the value you actually use on the server should be calculated on the server only, so that no one can manipulate the values.

 

all the other values that you are storing, like basicSalary, should not be the values from the form, but should be the current values from the employee table.

 

lastly, as to the reason your code doesn't work. the $leave variable that you are using in the calculation doesn't have a value until after the form has been submitted. the empty string you are setting it to near the start of your code to prevent undefined variable errors is just hiding the problem.

[kingot]

Hi sir,

 

I have remove the $leave ="0"; but it still not working

when i use printf('<pre>%s</pre>', print_r($_POST, 1)); and submit the form

 

here is the result

Array(    [empId] => 7    [name] => Baddo Blessed Lord    [date] => 12/15/2014    [basicSalary] => 2400    [leave] => 4    [salaryPerDay] => 80    [leaveDeduct] =>     [netSalary] => 2400    [pay] => Submit Pay)

The way  i want it to work is ..if you select employee to pay , it will take you to this page and it will populate the EmpId , Employee name, basicSalary ,

so all you enter is the number of leave and the select date and then submit pay ....but it seems the form is submitted before the calculation is done ,so it does not insert the calculated values into the database but the correct result show in the input field the form is submitted.

 

Any help..i will be much appreciated.

[mac_gyver]

so all you enter is the number of leave and the select date and then submit pay ...

 

 

^^^ this is your goal for the form processing code (after the form has been submitted.) the leave, date, and empId are the only relevant values from the form to use in the form processing code.

 

your form processing code, after if validates the three input values, should take the empId value, retrieve the other necessary values (note: the name should only be in the employee table, you should not store it in the payroll table, just store the empId) from the employee table, perform the calculations you need, then insert the resulting information into the payroll table.