I need to display data in an HTML table, fetching from the database.
I have to select two tables with INNER JOIN
, table1
and table2
.
This is my query:
$sql="SELECT * FROM `cdr` INNER JOIN `clients`
ON `cdr`.`dst`=`clients`.`mobile`
WHERE
DATE_FORMAT(calldate,'%m/%d/%Y')='$calldate' OR
src='$src'
dst='$dst' OR
disposition='$disposition' OR
bitrix_id='$btx_id' OR
lead_name='$lead_name' OR
agent='$agent'
ORDER BY DATE_FORMAT(calldate,'%m/%d/%Y') DESC";
For example, I want to make a filter like this: to select all dispositon=answered on date=8/16/2018 ,dst=xxxxxx
.
This is also all my PHP code:
if(isset($_POST['filter'])){
//Filter by html form
$date=$_POST['calldate'];
$calldate=date("m/d/Y", strtotime($date));
$src=$_POST['src'];
$dst=$_POST['dst'];
$disposition=$_POST['disposition'];
$r=1;
$total_duration=0;
$btx_id=$_POST['btx_id'];
$lead_name=$_POST['lname'];
$agent=$_POST['agent'];
// test
$sql1="SELECT * FROM `cdr` INNER JOIN `clients`
ON `cdr`.`dst`=`clients`.`mobile`
WHERE
DATE_FORMAT(calldate,'%m/%d/%Y')='$calldate' OR
src='$src' OR
dst='$dst' OR
disposition='$disposition' OR
bitrix_id='$btx_id' OR
lead_name='$lead_name' OR
agent='$agent'
ORDER BY DATE_FORMAT(calldate,'%m/%d/%Y') DESC";
$query=mysqli_query($con,$sql1) or die(mysqli_error());
while($row1=mysqli_fetch_array($query,MYSQLI_ASSOC)){
echo "
<tr>
<td>".$r."</td>
<td>".$row1['bitrix_id']."</td>
<td>".$row1['lead_name']."</td>
<td>".$row1['agent']."</td>
<td>".$row1['calldate']."</td>
<td>".$row1['src']."</td>
<td>".$row1['dst']."</td>
<td>".$row1['disposition']."</td>
<td>".$row1['duration']."</td>
</tr>
";
$r++;
$total_duration=$total_duration+$row1['duration'];
}
$durationmin=$total_duration/60;
echo "
<tr>
<th> </th>
<th> </th>
<th> </th>
<th> </th>
<th> </th>
<th> </th>
<th> </th>
<th> </th>
<th>".$total_duration."(".round($durationmin,1)."min)</th>
</tr>
";
}