CODEWITHSUNDEEP
mysqlcodeigniter-3
user_777
user_777

Reputation: 805

fetching value from database

This is the data am having in my table named rent

    id    date     stud_id  paid    balance

    18  10-2016       94    15000   15000
    19  10-2016       94    10000   5000
    20  10-2016       96    25000   5000
    21  10-2016       96    5000    0

here i want to get last inserted stud_id balance but am getting the first one. my code looks like this

public function rent_outstanding($hos,$dt)
{
    $this->db->select('student_hostel.*,rent.balance,rent.paid');
    $this->db->join('rent','rent.stud_id=student_hostel.id');
    $this->db->where('status!=','G');
    $this->db->where('status!=','R');
    $this->db->where('hostel',$hos);
    $this->db->where('rent.date',$dt);
    $this->db->select_sum('paid');
    $this->db->group_by('stud_id');
    $query=$this->db->get('student_hostel');
    return $query;
}

my student_hostel table looks like this

     id   first_name  last_name    stud_id     admit_date   hostel   class  room bed status

     94     ss        ff          PHBH00094     01-10-2016   12        16   115  501A    P
     96    maltu      uv          PHBH00096     01-10-2016   12        16   115  501C    p

am getting my result like this 

SI.No   STUDENT ID   NAME    RENT    PAID    BALANCE
 1      PHBH00094     Ss    30000   25000    15000  
 2      PHBH00096    Maltu  30000   30000    5000

i want to get my result like this

SI.No   STUDENT ID   NAME    RENT    PAID    BALANCE
 1      PHBH00094     Ss    30000   25000    5000   
 2      PHBH00096    Maltu  30000   30000    0

Upvotes: 0

Views: 38

Answers (1)

Srihari Karanth
Srihari Karanth

Reputation: 2167

You need to specify somewhere to take the max of ID from rent table. This query should give you that. 

select stud_id ,first_name, paid, balance from rent rt, student_hostel s where
hostel='$hosr' and ent.date='$dt' and id = (select max(id) from rent r where r.stud_id = rt.stud_id) and status!= 'G' and status!= 'R'

Add date to inner query if you need balance of that date id = (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt')

For Codeignitor you can write the query directly:

    $sql = "select stud_id ,first_name, paid, balance from rent rt, student_hostel s where hostel=? and ent.date=? and id = (select max(id) from rent r where r.stud_id = rt.stud_id) and status!= 'G' and status!= 'R'";

    $this->db->query($sql, array($hosr, $dt));

More info here: https://www.codeigniter.com/userguide3/database/queries.html#query-bindings

Upvotes: 1

Related Questions