CODEWITHSUNDEEP
phpmysql
Jerome Axel
Jerome Axel

Reputation: 17

trying to get Salary for months March to December then add them up in mysql and php

$January = 'January';
$February = 'February';
$March = 'March';
$April = 'April';
$May = 'May';
$June = 'June';
$July = 'July';
$August = 'August';
$September = 'September';
$October = 'October';
$November = 'November';
$December = 'December';
$user = $_COOKIE['user'];
$Year = '2017';
$Year1 = '2016';

in the below code i am trying only to get the Salary field with their values were the months are from March to December but it returns null but in the database i can see there are values in the Salary field .

// total Salary 
$result1 = mysqli_query($link, "SELECT Salary FROM $tb2_name WHERE Employee_Number='$user' AND Month='$March' AND Month='$April' AND Month='$May' AND Month='$June' AND Month='$July' AND Month='$August' AND Month='$September' AND Month='$October' AND Month='$November' AND Month='$December' AND Year='$Year1' "); 

$row = mysqli_fetch_assoc($result1); 

$test_sum = array_sum($row);

Upvotes: 0

Views: 229

Answers (7)

naf4me
naf4me

Reputation: 413

Please use this one:

$result1 = mysqli_query($link, "SELECT Salary FROM $tb2_name WHERE Employee_Number='mysqli_real_escape_string($link,$user)' 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' ");

Upvotes: 0

astrosixer
astrosixer

Reputation: 121

use ENUM for month like below

create table income(id int not null primary key auto_increment,
user_id not null varchar(500),
month ENUM('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
year varchar(500),
salary varchar(500))

Query

SELECT salary FROM income
WHERE month in('Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') 
and user_id='user_01'

Upvotes: 0

krishnakumar kk
krishnakumar kk

Reputation: 289

Change your query to the following 

     $result1 = mysqli_query($link, "SELECT Salary FROM $tb2_name WHERE Employee_Number='".$user."' 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( $row = mysqli_fetch_assoc($result1)){
        print_r($row);
    }

Upvotes: 0

Oldskool
Oldskool

Reputation: 34837

First of all, it would be a good idea to change the design of your database table to actually hold a date (like a DATE or DATETIME data type), that would simplify your query to this:

SELECT Salary FROM $tb2_name
WHERE Employee_Number='$user'
AND `date` BETWEEN '2016-03-01' AND '2016-12-31'

That would give you all the rows for employee $user between the 1st of March and the 31st of December of 2016. So, if you have the freedom to alter the table, I'd highly advise you to do so.

That being said. The main problem with your current query is that you're using AND instead of OR. A salary row in your database can never have the month March AND April AND May (etc.) at the same time. It's always one or the other.

But with so many months, using a lot of OR statements can become hard to read (and might cause problems when mixing it with other AND or OR statements). In that case you can also use the IN statement to have the column match anything in that group of choices. So change it to:

SELECT Salary FROM $tb2_name
WHERE Employee_Number='$user'
AND Month IN (
    '$March', '$April', '$May', '$June', '$July', '$August',
    '$September', '$October', '$November', '$December'
)
AND Year='$Year1';

Upvotes: 1

Max Oriola
Max Oriola

Reputation: 1374

To match any of the values of a list, you can use IN statement to form a more compact query.

SELECT salary FROM tablename WHERE colname IN('value1', 'value2', 'value3', ...)

Upvotes: 0

Shubham Dixit
Shubham Dixit

Reputation: 104

$result1 = mysqli_query($link, "SELECT Salary FROM $tb2_name WHERE Employee_Number='".$user."' 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."' ");

Upvotes: 0

JYoThI
JYoThI

Reputation: 12085

use OR condition 

$result1 = mysqli_query($link, "SELECT Salary FROM $tb2_name WHERE Employee_Number='$user' 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' OR Year='$Year1' "); $row = mysqli_fetch_assoc($result1); $test_sum = array_sum($row);

Upvotes: 0

Related Questions