Retrieving specific key-values from a query and fetch count of their pair in query

Question

I have a table lead_submission which contains value of a user in a specific format like

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

now I want to get the count of only say 504:Yes from two rows

these values are coming from another table 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
}
?> 

Now by doing this i am getting count as 2 for 504:Yes

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

my point is how can i also count another combination say 1083:No and show count in the same table

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

Answer 1

Try this:

SELECT COUNT(DISTINCT ls.agent_name), 
       SUM(CASE WHEN pr.qno = 504 AND pr.paid_response = 'Yes' THEN 1 ELSE 0 END) AS '504-Count', 
       SUM(CASE WHEN pr.qno = 1083 AND pr.paid_response = 'No' THEN 1 ELSE 0 END) AS '1083-Count'
FROM lead_submission ls 
INNER JOIN paid_response pr
ON CONCAT('|', ls.qa_details, '|') LIKE CONCAT('%|', pr.qno, ':', pr.paid_response , '|%');

Check the SQL FIDDLE DEMO

OUTPUT

| COUNTAGENT | 504-COUNT | 1083-COUNT |
|------------|-----------|------------|
|          2 |         2 |          1 |

::EDIT::

First execute below query

SELECT GROUP_CONCAT('SUM(CASE WHEN pr.qno = ', qno, ' AND pr.paid_response = ''', paid_response,''' THEN 1 ELSE 0 END) AS ''', qno, '-Count''') 
FROM paid_response;

Use output of this query and build your final query as below:

query = 'SELECT COUNT(DISTINCT ls.agent_name), ' + outputOfAboveQuery + ' FROM lead_submission ls NNER JOIN paid_response pr ON CONCAT('''|''', ls.qa_details, '''|''') LIKE CONCAT('''%|''', pr.qno, ''':''', pr.paid_response , '''|%''');';

Execute this string in your code so you can get dynamic query to fetch the counts

Answer 2

<?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');

Try this Man!

Answer 3

Echoing the comments of others. You should probably normalize your model.

That said, it is not impossible to extract the result you require, just that the solution will be inefficient, not scalable, difficult for new developers to understand and not very extendable.

Also, it is easier to extract the data in the long format rather than the wide format, i.e.

# wide data format
CountAgent  504-COUNT   1083-Count
  2           2           1  

vs.

# Long data format
dimension   count
CountAgent    2
504-Count     2
1083-Count    1

The transformation from long to wide is easier in php (and may not even be required).

SELECT 
CONCAT(pr.qno, ":", pr.paid_response) dimension,
COUNT(*) `count`
FROM lead_submission ls 
JOIN paid_response pr 
  ON ls.qa_details LIKE CONCAT("%", pr.qno, ":", pr.paid_response, "%")
-- insert where clause 
GROUP BY 1
UNION
SELECT 'count-agent' dimension,
COUNT(DISTINCT ls.agent_id) `count`
FROM lead_submission ls 
JOIN paid_response pr 
  ON ls.qa_details LIKE CONCAT("%", pr.qno, ":", pr.paid_response, "%")
-- insert where clause        
GROUP BY 1

In the query above, the where clause should be the same for both the united selects, and I think for your case it should take the following form:

WHERE CONCAT(pr.qno, ":", pr.paid_response) IN (<key-value pair 1>, <key-value pair 2>, ...)

This returns the following result:

DIMENSION       COUNT
1083:No         1
504:Yes         2
count-agent     2

Here's the sqlfiddle demo

Answer 4

Assuming the format of the data stays the same, you could try something like this (untested):

$query[] = "SELECT COUNT(agent_name) as agent_cnt"; // number of agents in total

// Counts of each question/answer.
foreach ($exqno as $qno => $value) {
    $query[] = "(SELECT COUNT(agent_name) FROM lead_submission WHERE qa_details LIKE '%|$qno:$value|%') AS count_{$qno}";  
}

$full_query = implode(', ', $query) . " FROM lead_submission";

$sql = mysql_query( $full_query );

Answer 5

SELECT productOwner_org_id, 
(SELECT COUNT(*) FROM tblProducts P2 WHERE P1.product_id=p2.product_id AND productDescription='Leisure at PER01 IVR Gas') AS '504-data',
(SELECT COUNT(*) FROM tblProducts P3 WHERE P1.product_id=p3.product_id AND productDescription='Leisure Plus at Centerpoint') AS '1083-data'
FROM tblProducts p1
WHERE productOwner_org_id = 'AEG01'
AND
(SELECT COUNT(*) FROM tblProducts P2 WHERE P1.product_id=p2.product_id AND productDescription='Leisure at PER01 IVR Gas') != 0
OR
(SELECT COUNT(*) FROM tblProducts P3 WHERE P1.product_id=p3.product_id AND productDescription='Leisure Plus at Centerpoint') != 0


;

As you can see it's kind of ugly.

A) Your better option is to reorganize your data or B) When presenting/format your data differently with more php logic

Answer 6

Count agent can be different in each search case. If in the above example you search for '504','1083:No' then it is not 2 in both cases. I suggest you modify your script like this:

<?php
$array = array('504','1083');
//db connection goes here
$th="";
$tr="";
foreach($array as $arr) {
$srch=$arr;
$sql=mysql_query("select qno,paid_response from paid_response where qno=$srch ");
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 ");
        $th.="<th>CountAgent(".$arr.")</th><th>(".$arr.")-COUNT</th>";                      
while($row=mysql_fetch_array($sql)) {         
        $tr.="<td>".$row['agent_cnt']."</td><td>".$row['ppicount']."</td>";           
}
}
?> 
   <table border="1">
     <thead>
     <tr>
     <?php echo $th; ?>                     
     </tr>
     </thead>
     <tbody>
     <tr style="color:red" >            
     <?php echo $tr; ?>             
     </tr>
     </tbody>
     </table>

Answer 7

Okay, here is incomplete answer. Incomplete as I don't bother to write specific query for you. But I am writing on how to do it.

Note: Read about normalization. You will see your mistake.

If you can modify you data by adding | at start of it, i.e. make 1001:|1083:|504:Yes|1009:| as |1001:|1083:|504:Yes|1009:| will help you a lot.

Now, you can easily search a TEXT Search on %|<ID>:% this ensure that you find either 504 or 1009 or whatever number by replacing <ID> with that number.

Did you read normalization yet? Anyways.

once you did this you can now do Row to Column concept (I think it is know as PIVOT Queries, google it while you google normalization. ). Some of examples are given here

MySQL Row to Column Or Mysql query to dynamically convert rows to columns

Sorry for incomplete answer but Text Search is your kind a best bet, and better create a View of it before you use it in complex queries. But really it will be VERY SLOW To fetch data and is not recommended.

Hope you find normalization yet...if not try this: https://www.google.co.in/#q=normalization+in+database

Oh and don't forget to index that column ...