Solved

PHP using data from table.

Posted on 2012-04-04
3
256 Views
Last Modified: 2012-04-04
Hi,

I have a PHP script that calculates an employees potential bonus, based on the figures that are entered in my html form.

The form is used to calculate an employees bonus. The form asks the following questions.

Market
What is your salary
What is review score
What is the customer satisfaction score

However to get their potential bonus the company profits figure needs to be pulled from my mysql table. This figure is not entered by the user however it is inserted using another PHP form used by their managers. This form works well.

So my calculation would look like

Salary * review * customer satisfaction * profit factors

My table for the company profits looks like this
Market     Profit_Factor
UK               50
BE                200
FR                 40

My first question is how can I include the profit factor figure into my calculation.
My second question is how can I tell PHP to use the profit factor that matches the market selected in my form.

<html>
<body>
 
<center>
 
<h2> ADD TWO NUMBERS! </h2>
 
<form action="addition.php" method="post">

<CENTER><input type="text"  name="salary_factor" id="salary_factor" /></center>


<td><select id="personal_factor" name='personal_factor'
   
    <option value="None">Please Select Value</option>
    <option value="135">Outstanding Review</option>
    <option value="120">Excellent</option>
    <option value="100">Good</option>
    <option value="75">Satisfactory</option>
    <option value="0">Unsatifactory</option>
 
  
   </select></td>


<td><select id="customer_factor" name='customer_factor'
   
    <option value="None">Please Select Value</option>
    <option value="115">90%</option>
    <option value="75">80%</option>
    <option value="50">>70%</option>
    <option value="0"><=60%</option>
 
  
   </select></td>




 =
<T><input type="submit" value="Answer is ->>" />
</form>
 

Open in new window


<?php
$personal_factor = $_POST["personal_factor"];
$customer_factor = $_POST["customer_factor"];
$salary_factor = $_POST["salary_factor"];
$answer = $personal_factor * $customer_factor * $salary_factor;
 
echo $answer;
?>

Open in new window

0
Comment
Question by:Dan560
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Scott Madeira earned 500 total points
ID: 37806331
In your second PHP script you would need to call the database and get the profit factor to add to your calculation.

Here are the basics.  You need to add all the error checking and input sanitization.

<?php

// You will want to sanitize all of your input data to eliminate 
// chance for hacking on form inputs

// Connect to your MySQL database here
$db = mysql_connect( connection parameters);


// You need the market info from your form
$market = $_POST["market"];

// Do SQL query to get market factor
$sql = "select profit_factor from market_table where marketname = '".$market."';";

$result = mysql_query($sql, $db);
$row = mysql_fetch_row($result);

// Get the profit from returned data
$profit_factor = $row['profit_factor'];

$personal_factor = $_POST["personal_factor"];
$customer_factor = $_POST["customer_factor"];
$salary_factor = $_POST["salary_factor"];



$answer = $profit_factor * $personal_factor * $customer_factor * $salary_factor;
 
echo $answer;
?>

Open in new window

0
 
LVL 2

Author Comment

by:Dan560
ID: 37806497
Hi I have modified the script slightly to connect to my DB..

However I get the following error messages..

Notice: Undefined variable: db in C:\xampp\htdocs\boncalc\addition.php on line 20

Warning: mysql_query() expects parameter 2 to be resource, null given in C:\xampp\htdocs\boncalc\addition.php on line 20

Warning: mysql_fetch_row() expects parameter 1 to be resource, null given in C:\xampp\htdocs\boncalc\addition.php on line 21
0

Thanks in advance for your help.

<?php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="bonuscalc"; // Database name 
$tbl_name="market_values"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


// You need the market info from your form
$market = $_POST["market"];

// Do SQL query to get market factor
$sql = "select profit_factor from market_values where marketname = '".$market."';";

$result = mysql_query($sql, $db);
$row = mysql_fetch_row($result);

// Get the profit from returned data
$profit_factor = $row['profit_factor'];

$personal_factor = $_POST["personal_factor"];
$customer_factor = $_POST["customer_factor"];
$salary_factor = $_POST["salary_factor"];



$answer = $profit_factor * $personal_factor * $customer_factor * $salary_factor;
 
echo $answer;
echo $profit_factor;
?>]

Open in new window

0
 
LVL 2

Author Comment

by:Dan560
ID: 37807595
Ok I've managed to solve it.

The first error I posted was because I could not connect to the DB I used the following code to help me debug that -

$result = mysql_query($sql, $db) or die (mysql_error());

secondly I got an error stating Undefined index on line 21

I had to change
mysql_fetch_row.
to mysql_fetch_assoc
source: http://stackoverflow.com/questions/7895862/mysql-undefined-indexes-even-though-they-have-been-defined

Hopefully that info will be useful to someone.
However thanks for your help as it definitely set me on the right path.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question