从查询中检索特定键值并在查询中获取其对的计数

Black 发表于 Dev

黑色

我有一个表格lead_submission，其中包含特定格式的用户价值

agent_name     qa_details
xxx      1001:|1083:|504:Yes|1009:|
ccc      504:Yes|1083:No|1008:|1009:|

现在我想504:Yes从两行中获得只说的次数

这些值来自另一个表 paid_response

qno    paid_response
504     Yes
1083    No
1083    Possibly

<?php
//db connection goes here

$sql=mysql_query("select qno,paid_response from paid_response where qno='504' ");
while($rows=mysql_fetch_array($sql)) {    
$exqnos= $rows['qno'].'|'.$rows['paid_response'];
}

list($key,$val)=explode('|',$exqnos);
$exqno[$key]=$val;

foreach($exqno as $qno=>$value) {
$string .="qa_details LIKE '%|$qno:$value|%' ";  
}

$sql=mysql_query("SELECT count(agent_name) as agent_cnt,count($string) as ppicount FROM `lead_submission` WHERE $string "); ?>

               <table border="1">
                <thead>
                  <tr>
                    <th>CountAgent</th>
                    <th>504-COUNT</th>                      
                  </tr>

<?php
while($row=mysql_fetch_array($sql)) { ?>

     <tr style="color:red" >            
        <td><?php echo $row['agent_cnt']; ?></td>
        <td><?php echo $row['ppicount']; ?></td>            
        </tr>

<?php
}
?>

现在通过执行此操作，我可以算为2 504:Yes

CountAgent  504-COUNT
 2            2        //as u can see that `504:Yes` has occured two times in lead_submission table.

我的意思是我怎么也可以数另一个组合说1083:No并在同一张桌子上显示数

NB:- cant we just fetch the combination like `504:Yes` or `1083:No` or `1083:Yes` from paid_response table to maintain stability so that i dont have to change the query everytime. 

CountAgent  504-COUNT   1083-Count
  2           2           1        //how to get this count `1083:No` . as u can see it only appeared 1 times in `lead_submission` table

小型兽医系统

<?php
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('test',$con);

function countIt($checkArray)
{
    $checkVals = explode(',', $checkArray); 
    foreach($checkVals AS $val){
        list($qNo, $pRes) = explode(':', $val);
        $query = mysql_query("SELECT * FROM `paid_response` WHERE `qno`='$qNo' AND `paid_response`='$pRes'");
        if(mysql_num_rows($query) > 0){
            $query = mysql_query("SELECT * FROM `lead_submission` WHERE `qa_details` LIKE '%$val%'");
            $countArray[$val] = mysql_num_rows($query);
        } else {
            $countArray[$val] = 0;
        }
    }

    foreach($countArray AS $key=>$val){
        echo $key . '  =>  ' . $val . "<br/>";
    }
}

echo countIt('504:yes,1083:no,1083:yes,1083:possibly,504:no');

试试这个男人！

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。