0

Q.1) Suppose I have a table named advertisements with a field ad_memberships containing comma separated 1,2,3,4 type values and I want to calculate the total number of rows for each values then how can I do that? Like say total rows in the table containing value 1 is 10, 2 is 5, 3 is 15, 4 is 3 etc., without manually inputting the data in FIND_IN_SET() function? I just want to automatically calculate for all. Here, 1,2,3,4 is coming from a table named memberships where these are the unique IDs of different rows mbs_id.

Q.2) This is related to the first question and both needs to be done together. I am writing it separately so that it is clear and understandable. So, suppose I also have a second table named ads_category with fields ac_id and ac_value. This ac_id is inserted in 1st table advertisements in the field ads_category so that all ads are relevant to some different category. Now after calculating the rows in the 1st question I want to multiply is ac_value with the number of rows received in first question and display the result as follows. 

Standard Membership: 10 ads (Value: $5)
Premium Membership: 16 ads (Value: $12)
Elite Membership: 8 ads (Value: $7)
Ultimate Membership: 11 ads (Value: $8)
and so on....

This means for members with standard memberships 10 ads are available for members holding premium membership 16 ads are available and so on. How can I accomplish that? For an example, the database structure is given below:

table: memberships

mbs_id | mbs_name
   1   | Standard
   2   | Premium
   3   | Elite
   4   | Ultimate

table: advertisements

ad_id  | ad_memberships | ad_category
   1   | 1,2            | 1
   2   | 1,2,3          | 1
   3   | 1,3            | 2
   4   | 2,3            | 3

table: ads_category

ac_id | ac_value
   1  | 0.5
   2  | 1
   3  | 1.5
   4  | 2

Expected result from the above database structure

Standard : 3 ads (Value: $1.5)
Premium  : 3 ads (Value: $3)
Elite    : 3 ads (Value: $4.5)
Ultimate : 0 ads (Value: $0)

Now I want to join all the three tables and get the relevant result. And yes, no values can be entered manually in the query. All should be done with the values in the database. How can I do that? I am using PDO. Please help.

  • https://stackoverflow.com/questions/2857024/searching-a-column-containing-csv-data-in-a-mysql-table-for-existence-of-input-v – Tim G Jan 03 '18 at 19:10
  • @TimG How to make it automatic? Here, I need to enter the values manually if I use `select Id from Item where instr(concat(',', Features, ','), ',AB,') <> 0 or instr(concat(',', Features, ','), ',PQ,') <> 0` query and that values can increase, decrease or removed too –  Jan 03 '18 at 19:19

2 Answers2

0

I can't quite see how your formula is supposed to work, because the example results you show in your question don't seem to match the sample data. But maybe this will give you an idea how to get started.

SELECT CONCAT(
    m.mbs_name,
    '  Membership: ',
    COUNT(a.ad_id),
    ' ads ($',
    SUM(c.ac_value)
    ')'
  ) AS result
FROM memberships AS m
LEFT OUTER JOIN advertisements AS a ON FIND_IN_SET(m.mbs_id, a.ad_memberships)
LEFT OUTER JOIN ads_category AS c ON a.ad_category = c.ac_id
GROUP BY m.mbs_id
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thanks for your time but it didn't help. Instead I found the solution. My answer is posted below. –  Jan 04 '18 at 15:56
0

Okay so I found the solution myself. This is my achieved expected output

enter image description here

CODE:

<div class="row">
  <?php
    $mbs = $pdo->prepare("SELECT mbs_id, mbs_name, mbs_color, mbs_perclick FROM memberships");
    $mbs-> execute();

    while($mbsid = $mbs->fetch()){ extract($mbsid);
    $adstotal = $pdo->prepare("SELECT COUNT(*) as cnt, SUM(ac_value) FROM advertisements
    LEFT JOIN ads_category ON ads_category.ac_id = advertisements.ad_category
    WHERE INSTR(CONCAT(',', ad_memberships, ','), ',".$mbs_id.",') <> 0");
    $adstotal-> execute();
    $adsf = $adstotal->fetch();
  ?>
    <div class="col-sm-2">
      <span class="membership-indicator" style="background: <?php echo $mbs_color; ?>; margin-top: 5px"></span>
      <?php echo $mbs_name; ?>
    </div>
    <div class="col-sm-2 text-center">
      <?php echo $adsf['cnt']; ?>
    </div>
    <div class="col-sm-2 text-center">
       $<?php $cps = $mbs_perclick/100; $finalval = $adsf['SUM(ac_value)']*$cps; echo number_format($finalval,4,'.',','); ?>
    </div>
    <?php } ?>
</div>