user3332537 user3332537 - 2 years ago 92
SQL Question

All the combinations of values from the same column

I have two table in mysql user and item.

ID | session| item |
1 | 1 |A |
2 | 1 |B |
3 | 1 |C |

id | item| price | Images
1 | A | 10 | link
2 | B | 5 | link
3 | C | 5 | link
4 | D | 5 | link

The two tables are correlate from item and in the table "user" I've passed the data form where the user can choose from one to five items. The session is number 1 for example instead alphanumeric value create with function session start.

I display with select statement and cycle while the images for the items:

$select = "select item, price , images";
$from = " from item as i, customer as t";
$where = " where i.item = t.item and t.session = '".$session."'";
$sql = $select . $from . $where;

echo "<ul class=\"tab\"><li class=\"col1\"><strong>item<strong>";

while($row=$r->fetch(PDO::FETCH_ASSOC)) {
echo "<img src=\".$row['Images']."\">\n";
echo "</li></ul";


In this way I display only the request of the user, in the example session 1 abc.

I like to display all the others combination of the items, ab ac ad abc etc. of the item table but I don't know how set up code php.

Answer Source

there are probably other ways of doing this, but here's one in mysql. the first inner select (T1) just grabs a list of numbers from 1 counting upward to whatever, you can add to this query if you have lots of items where the number of combinations is higher). the second inner select (T2) just grabs items and price and turns on a bit position for each item, then the outer select inner joins them where the bits of num are turned on it selects that that item. GROUP_CONCAT() concatenates them, and SUM() just returns the total_price of that combination.

       GROUP_CONCAT(T2.item ORDER BY T2.item SEPARATOR '' ) as items,
       SUM(T2.price) as total_price
 (SELECT a.num+b.num+c.num+d.num+e.num+f.num+g.num+1 as num
    (SELECT 0 as num UNION SELECT 1)a,
    (SELECT 0 as num UNION SELECT 2)b,
    (SELECT 0 as num UNION SELECT 4)c,
    (SELECT 0 as num UNION SELECT 8)d,
    (SELECT 0 as num UNION SELECT 16)e,
    (SELECT 0 as num UNION SELECT 32)f,
    (SELECT 0 as num UNION SELECT 64)g
    ORDER BY num ASC
   (SELECT item,price,
       @bit := 1 << @shift as bit,
       @shift := @shift + 1 as shift
    FROM item,
       (SELECT @bit := 1,@shift := 0)initial
ON T1.num & T2.bit > 0
ORDER BY items



items   total_price
A       10
AB      15
ABC     20
ABCD    25
ABD     20
AC      15
ACD     20
AD      15
B        5
BC      10
BCD     15
BD      10
C        5
CD      10
D        5
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download