Obtaining certain key values ​​from the request and selecting their pair in the request

Question

I have a lead_submission table that contains a user value in a specific format, e.g.

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

now I want to get the counter only say 504:Yes from two lines

these values ​​come from another paid_response table

 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 get the score 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 calculate another combination, say 1083:No and show the number 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

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

Try the following:

 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 SQL FIDDLE DEMO

OUTPUT

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

:: EDIT ::

First run below request

 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 the output of this query and create the final query as shown 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 , '''|%''');'; 

Run this line in your code so you can get a dynamic query to get the counts

Answer 3

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

However, it is impossible to extract the required result, just to make the solution ineffective and not scalable, for new developers difficult to understand and not very extensible.

In addition, it is easier to extract data in a long format rather than in a wide format, i.e.

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

against.

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

Converting from long to wide is easier in php (and might 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 above query, where clause should be the same for both selected ones, and I think for your case it should look like this:

 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 sqlfiddle demo

Answer 4

Assuming the data format remains unchanged, you can 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 is somehow ugly.

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

Answer 6

The counting agent may be different in each search case. If in the above example you are looking for "504", "1083: No", then in both cases it is not 2. I suggest you change your script as follows:

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

Well, here is an incomplete answer. Incomplete, because I do not want to write a specific request for you. But I am writing about how to do this.

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

If you can change your details by adding | at the beginning, i.e. make 1001:|1083:|504:Yes|1009:| like |1001:|1083:|504:Yes|1009:| helps you a lot.

Now you can easily search for TEXT Search on %|<ID>:% to make sure you find either 504 or 1009, or any other number, replacing <ID> with that number.

Have you read normalization yet? Anyway.

as soon as you do this, now you can use the Row to Column concept (I think it is known as PIVOT Queries, google, as long as you normalize Google). Some examples are given here.

MySQL row in a column Or Mysql query to dynamically convert rows to columns

Sorry for the incomplete answer, but text search is your good choice and better create an idea about it before using it in complex queries. But actually it will be VERY SLOWLY to Receive data and it is not recommended.

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

Oh, and don't forget to specify this column ...