How do I get last record from a mysql column through php where several other query is running -

im trying several user data (from voip calling card database) web portal multiple table login, name, registration date taken 1 table, last successful call date in table , current user balance , total duration in table. here demo screencap:

my problem im not able last call date have query , last or latest date list of total call history filtering each user individually.

codes given below:

all of needed data in these 3 table "clientsshared, invoiceclients, calls,"

"clientsshared" holds login & balance data.

"invoiceclients" holds name , account creation date.

"calls" holds call duration , other call history

<div>   <table class="table table-striped table-bordered" cellspacing="0" width="100%">     <thead>       <tr>         <th>#</th>         <th>login</th>         <th>full name</th>         <th>reg.date</th>         <th>lastcall</th>         <th>current balance</th>         <th>total duration</th>       </tr>     </thead>     <tbody>       <?php           $sql="select c.login,cname.name,cname.lastname,date_format(cname.creation_date,'%d-%m-%y')as regdate,cdr.call_start,c.account_state,sum(cdr.duration / 60) total_duration clientsshared c          left join invoiceclients cname on cname.idclient = c.id_client          left join calls cdr on cdr.id_client = c.id_client          c.id_reseller='10' group c.id_client order total_duration desc limit 100" ;           $result=$ db1->query($sql);           if($result){          $i = 1;          while($row = $result->fetch_object()){              $login = $row->login;              $name = $row->name;              $lastname = $row->lastname;              $regdate = $row->regdate;              $lastcall = $row->call_start;              $account_state = $row->account_state;              $total_duration = $row->total_duration;         ?>        <tr>         <td><?php echo $i;?></td>         <td><?php echo $login; ?></td>         <td><?php echo $name. " ".$lastname; ?></td>         <td><?php echo $regdate; ?></td>         <td><?php echo $lastcall; ?></td>         <td><?php echo round($account_state,2); ?></td>         <td><?php echo round($total_duration,2); ?></td>       </tr>        <?php          $i++;          }         }        ?>      </tbody>   </table> </div> 

==== updated problem , solved======

there new issue im facing, i've tried add new table left join payment history table after joining table actual total duration field giving wrong values

new query here:

$sql =     "select c.login,cname.name,cname.lastname,date_format(creation_date,'%m-%d-%y')as regdate,     (select max(data) payments payments.id_client = c.id_client) lastpayment,    (select max(call_start) calls calls.id_client = c.id_client) lastcall,      c.account_state,sum(cdr.duration / 60) total_duration clientsshared c       left join invoiceclients cname on cname.idclient = c.id_client     left join payments p on p.id_client = c.id_client     left join calls cdr on cdr.id_client = c.id_client       c.id_reseller='10' group c.id_client order total_duration desc limit 100";

solved wow, im not sure how works removed left join , tried output correct value expected ,

    select c.login,cname.name,cname.lastname,cname.creation_date regdate,   	(select max(data) payments payments.id_client = c.id_client) lastpayment,  	(select max(call_start) calls calls.id_client = c.id_client) lastcall,   	c.account_state,sum(cdr.duration / 60) total_duration     	from clientsshared c    	left join invoiceclients cname on cname.idclient = c.id_client  	left join calls cdr on cdr.id_client = c.id_client    	where c.id_reseller='10'     	group c.id_client     	order total_duration desc

posting answer long:

as said, not see field last call date in select statement of $sql. if have direct column last call date in calls table include select statement. query should this:

    select c.login,cname.name,cname.lastname,date_format(creation_date,'%d-%m-%y')as regdate,(select max(call_start) calls calls.id_client = c.id_client) lastcall,  c.account_state,sum(cdr.duration / 60) total_duration clientsshared c          left join invoiceclients cname on cname.idclient = c.id_client          left join calls cdr on cdr.id_client = c.id_client          c.id_reseller='10' group c.id_client order total_duration desc limit 100" 

i have added (select max(cdr.call_start) calls calls.id_client = c.id_client) in query.