How to get latest record from mysql column through php where multiple other queries are running
I am trying to get multiple user data (from Voip calling card database) to a web portal from multiple tables where Login , Name , Registration date taken from one table, Last successful call . The Date is in another table, and the user's current balance and Total Duration are in another table. Here's a demo example:
My problem is that I cannot get the last call date, which should query and get the latest or last date from the general call history list, filtering each user separately.
Below are the codes:
All the required data is given in these three tables: "clients, clients, calls",
"clientsshared" contains login and balance details.
"invoiceclients" contains the date the name and account were created.
"calls" contains the duration of the call and the entire 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) as total_duration from clientsshared as c
left join invoiceclients as cname on cname.IdClient = c.id_client
left join calls as cdr on cdr.id_client = c.id_client
where c.id_reseller='10' group by c.id_client order by 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 and solution ======
I got a new problem, I tried to add a new table to Left Join, which is a payment history table, but after joining that table, the actual full Duration field giving wrong values
The new request is here:
$sql = "select c.login,cname.Name,cname.LastName,DATE_FORMAT(Creation_Date,'%m-%d-%y')as regdate, (Select max(data) from payments where payments.id_client = c.id_client) as lastpayment, (Select max(call_start) from calls where calls.id_client = c.id_client) as lastcall, c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c left join invoiceclients as cname on cname.IdClient = c.id_client left join payments as p on p.id_client = c.id_client left join calls as cdr on cdr.id_client = c.id_client where c.id_reseller='10' group by c.id_client order by total_duration desc limit 100";
solvable
wow, I'm not sure how it works, but I just deleted left join
and tried, which brought out the correct value as expected,
select c.login,cname.Name,cname.LastName,cname.Creation_Date as regdate, (Select max(data) from payments where payments.id_client = c.id_client) as lastpayment, (Select max(call_start) from calls where calls.id_client = c.id_client) as lastcall, c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c left join invoiceclients as cname on cname.IdClient = c.id_client left join calls as cdr on cdr.id_client = c.id_client where c.id_reseller='10' group by c.id_client order by total_duration desc
source to share
Posting it as an answer as it is too long:
As said, I don't see a field for the last call date in the $ sql statement. If you have a straight column for the last call date in the call table, include it in the select statement. Therefore, your request should look something like this:
select c.login,cname.Name,cname.LastName,DATE_FORMAT(Creation_Date,'%d-%m-%y')as regdate,(Select max(call_start) from calls where calls.id_client = c.id_client) as lastcall,
c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c
left join invoiceclients as cname on cname.IdClient = c.id_client
left join calls as cdr on cdr.id_client = c.id_client
where c.id_reseller='10' group by c.id_client order by total_duration desc limit 100"
I added (select max (cdr.call_start) from calls where call.id_client = c.id_client) in your request.
source to share
Finally he decided, thanks everyone.
Solved query:
$sql = "select c.login,cname.Name,cname.LastName,DATE_FORMAT(Creation_Date,'%d-%m-%y')as regdate,DATE_FORMAT((Select max(call_start) from calls where calls.id_client = c.id_client),'%d-%m-%y') as lastcall, c.account_state,sum(cdr.duration / 60) as total_duration from clientsshared as c left join invoiceclients as cname on cname.IdClient = c.id_client left join calls as cdr on cdr.id_client = c.id_client where c.id_reseller='10' group by c.id_client order by total_duration desc limit 100";
source to share