I want to make a filter of my selecting Data, using PHP and MySQL

Question

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>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>&nbsp;</th>
<th>".$total_duration."(".round($durationmin,1)."min)</th>
</tr> 
   ";
}

As an aside, if '$calldate' was in the format Y-m-d, this query could run faster by irders of magnitude. — Strawberry
– Strawberry, Commented Aug 17, 2018 at 15:34
It's unclear what you are asking, I think probably the multiple OR are causing you problems, the query you have will return a row if any of those things are true, so it could easily return rows outside the data provided. In my experience OR is also terrible for query performance, its often faster to do a nested UNION query then multi or's but that's beyond the scope of the question. It's just typically you wont want that many OR's — ArtisticPhoenix
– ArtisticPhoenix, Commented Aug 17, 2018 at 15:44
For example I want to make a filter like this : to select all disposition=answered on date=8/16/2018 ,dst=xxxxxx. What does the , mean here. All disposition on data "AND" dst=xxx? I will also ignore the obvious SQLInjection issues you have for the time being. — ArtisticPhoenix
– ArtisticPhoenix, Commented Aug 17, 2018 at 15:48

Ervis Hysi · Accepted Answer · 2018-08-21 17:15:39Z

After many attempts to solve this problem , I found this solution thanks guys for your suggestions. Here is the PHP code:

<pre>
<?php
/********************************
-Comments...
-Show Alldata
*********************************************/
if(isset($_POST['filter'])){
//Filter by html form
$date=$_POST['calldate'];
$calldate=date("m/d/Y", strtotime($date));
$date2=$_POST['calldate2'];
$calldate2=date("m/d/Y", strtotime($date2));
$src=$_POST['src'];
$dst=$_POST['dst'];
$disposition=$_POST['disposition'];   
$r=1;
$client=0;
$agent=0;
$durationmin=0;
$total_duration=0;
$btx_id=$_POST['btx_id'];
$lead_name=$_POST['lname'];
$agent=$_POST['agent'];
$last_row=" ";
// test
$sql1 ="SELECT * FROM cdr INNER JOIN clients ON cdr.`dst`=`clients`.`mobile`";

$at_least_one = false;
$first_date=false;

if($date){
    $sql1.="where DATE_FORMAT(calldate,'%m/%d/%Y')>='$calldate'";
    $first_date=false;
    $at_least_one = true;
    //if we want all calls of any agent in given time
 }
if ($src) {
    if($at_least_one === false){
    $sql1.=" WHERE src='$src'";
  }
  else{
$sql1.=" AND src='$src'";
}
$at_least_one = true;
}
if ($dst) {
    if($at_least_one === false){
    $sql1.=" WHERE dst LIKE '$dst%'";
    }else{
    $sql1.=" AND dst LIKE '$dst%'";
}
  $at_least_one = true;
}
if ($disposition) {
if($at_least_one === false){
    $sql1.=" WHERE disposition='$disposition' ";
}else{
    $sql1.=" AND disposition='$disposition' ";
}
  $at_least_one = true;
}
if ($btx_id) {
if($at_least_one === false){
    $sql1.=" WHERE bitrix_id='$btx_id'";
}else{
    $sql1.=" AND bitrix_id='$btx_id'";
}
  $at_least_one = true;
}

if ($lead_name) {
if($at_least_one === false){
    $sql1.=" WHERE lead_name LIKE '%$lead_name%'";
}else{
    $sql1.=" AND lead_name LIKE '%$lead_name%'";
}
  $at_least_one = true;

}

if ($agent) {
  if($at_least_one === false){
  $sql1.=" WHERE agent LIKE '%$agent%' ";
} else{
  $sql1.=" AND agent LIKE '%$agent%'";
}
}
if ($date2) {
 if($at_least_one === false){
  $sql1.="AND DATE_FORMAT(calldate,'%m/%d/%Y')<='$calldate2'";
} else{
  $sql1.=" AND DATE_FORMAT(calldate,'%m/%d/%Y') BETWEEN '$calldate' AND '$calldate2'";
}
}

$sql1.=" ORDER BY DATE_FORMAT(calldate,'%m/%d/%Y') DESC";

echo "<div style='color:red;font-size:16px;'>".$sql1."</div>";
$query=mysqli_query($con,$sql1) or die(mysqli_error($con));
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['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=round($total_duration/60,1);   
echo "<tr>
      <th>&nbsp;</th>
         <th>&nbsp;</th>
         <th>(".$r.")Times Called This Client</th>
         <th>(".$r.")Total Calls From This Agent</th>
         <th>(".$r.")Total Calls on This Date</th>
         <th>&nbsp;</th>
         <th>&nbsp;</th>
         <th>(".$r.")Total Calls To This Number</th>
         <th>&nbsp;</th>
         <th>".$total_duration."(".round($durationmin,1)."min)</th>
      </tr> ";
}
?>

Collectives™ on Stack Overflow

I want to make a filter of my selecting Data, using PHP and MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related