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

Why are you storing the data like this? This would be WAY easier if you'd normalize the information properly, and it's probably a better idea to fix that over implementing it this way. — Erik
– Erik, Commented Dec 24, 2014 at 8:59
'without this complications.' The complications come from the fact that you did't choose a good format to save your data. You'd rather normalize your data. — Lorenz Meyer
– Lorenz Meyer, Commented Dec 24, 2014 at 9:01
It is not a good idea to remove the | and : . How could you distinguish from the question 504 and the answer 504? — Lorenz Meyer
– Lorenz Meyer, Commented Dec 24, 2014 at 9:05

Saharsh Shah · Accepted Answer · 2015-01-05 09:16:33Z

5

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

edited Jan 5, 2015 at 9:16

answered Dec 29, 2014 at 12:54

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

STT LCU Over a year ago

While an answer like this may work for this specific case (and variations thereof) this is not the recommended path. The absolute BEST solution is to normalize the database first. Google for "Database Normal Form". Kudos for @Saharsh for finding a solution anyway.

black Over a year ago

but if the combination like 1083:No changes to 1083:Yes in paid_response table then i have to change this query to Yes. cant we just fetch the combination from paid_response table to maintain stability

Saharsh Shah Over a year ago

@black If you want to perform something like that and then fetch the records from paid_response thable then build a query in your code with dynamically creation SUM(CASE WHEN pr.qno = 504 AND pr.paid_response = 'Yes' THEN 1 ELSE 0 END) AS '504-Count', this portion and in this you have to replace 504 to your fetched qno column data and Yes to 'your fetched paid_response column data`. So you can create dynamic query for that

black Over a year ago

@shah i didnt got ur code. if i paste ur edited 1st query its echoing the query in table header in phpmyadmin. can u plz elaborate ur code and comment.thnx for the response and quick solution. all i want to fetch the combination from paid_response table.

Saharsh Shah Over a year ago

@black I have shown a part of code rest code of PHP you have to write. First you have to call my first query in PHP method and store the output of that query in one variable and build the second query using that above variable and execute that query in PHP you will get the desired output.

|

Minivetsystem · Accepted Answer · 2015-01-02 10:34:01Z

<?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!

Haleemur Ali · Accepted Answer · 2014-12-29 14:00:28Z

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

PZL · Accepted Answer · 2014-12-29 12:59:56Z

1

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

answered Dec 29, 2014 at 12:59

PZL

615 bronze badges

Comments

terary · Accepted Answer · 2014-12-29 13:16:45Z

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

score 1 · Accepted Answer · 2014-12-31 10:05:57Z

1

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>

edited Dec 31, 2014 at 10:05

answered Dec 31, 2014 at 6:30

user2560539

10 Comments

user2560539 Over a year ago

This way you could insert your search variables in the array in the beginning.

black Over a year ago

ty for the response but ur code is not working Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given and the table heading is showing only the 2nd array value like u have given 504,1083 in array so its showing <th> as 1083 not 504.

black Over a year ago

its working fine with only one value passed like 504 or 1083 in the array. but not when both

black Over a year ago

so its not counting accurate count(agent_name), its displaying count(agent_name) as its displaying count($string).means if 1083:No is 1 agent count is 1. if 1083:No is 0 agent count is showing 0

user2560539 Over a year ago

try now please corrected and without mysql_real_escape_string

|

Community · Accepted Answer · 2017-05-23 12:00:04Z

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 ...

Collectives™ on Stack Overflow

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

7 Answers 7

10 Comments

Comments

Comments

Comments

Comments

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

10 Comments

Comments

Comments

Comments

Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related