Mysql – How to concatenate a column after x rows

functionsinnodbMySQLmysql-5.5

I have a table with columns

object varchar(255),
frequency tinyint(3) unsigned,

I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as

.. query loop {
$object .= $row['object'].",";
$frequency = $frequency+$row['frequency'];

if($frequency > 20) { // 20 is a given value
    $mysqli->query("INSERT INTO table2 (concatenated_column) VALUES ('$object')");
    $frequency=0;
    $object='';
    }
}

Is it possible to do this with a native query of mysql instead of bringing the data into PHP and returning back to mysql?

Since the process is simple, I think mysql has enough functions to do so, but I do not know how to do so.

In fact, I want to group concatenate the entire column and split it when sum of frequency reaches the given value (i.e. 20). This needs a loop of INSERTs in which the sum of frequency reset to 0 upon reaching 20.

UPDATE: The process with do as:

table1:

object    frequency
object1   7
object2   5
object3   6
object4   8
object5   2

table2:

concatenated_column
object1,object2,object3,object4
object5,....

Best Answer

Basically, you want to result the running total and group it

This requires iterative variable manipulation.

First, your sample data quadrupled:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.03 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into table1 select * from table1;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+---------+-----------+
| object  | frequency |
+---------+-----------+
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
| object1 |         7 |
| object2 |         5 |
| object3 |         6 |
| object4 |         8 |
| object5 |         2 |
+---------+-----------+
20 rows in set (0.00 sec)

mysql>

Let's make the query that generates group values:

set @freq_limit = 20;
set @freq_count = 0;
set @freq_group = 1;
set @freq_sum   = 0;
select cc,GROUP_CONCAT(object) objects from 
(
select * from 
(
    select *,
        @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
        @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
        @freq_group    := @freq_group + @freq_groupinc                 cc,
        @freq_count    := @freq_count + frequency                      dd
    FROM table1
) AA) A GROUP BY cc;

Would you believe it works?

First I'll let you see the inner subquery's output

mysql> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA;
+---------+-----------+----+------+------+------+
| object  | frequency | bb | aa   | cc   | dd   |
+---------+-----------+----+------+------+------+
| object1 |         7 |  0 |    0 |    1 |    7 |
| object2 |         5 |  0 |    7 |    1 |   12 |
| object3 |         6 |  0 |   12 |    1 |   18 |
| object4 |         8 |  0 |   18 |    1 |   26 |
| object5 |         2 |  1 |    0 |    2 |    2 |
| object1 |         7 |  0 |    2 |    2 |    9 |
| object2 |         5 |  0 |    9 |    2 |   14 |
| object3 |         6 |  0 |   14 |    2 |   20 |
| object4 |         8 |  1 |    0 |    3 |    8 |
| object5 |         2 |  0 |    8 |    3 |   10 |
| object1 |         7 |  0 |   10 |    3 |   17 |
| object2 |         5 |  0 |   17 |    3 |   22 |
| object3 |         6 |  1 |    0 |    4 |    6 |
| object4 |         8 |  0 |    6 |    4 |   14 |
| object5 |         2 |  0 |   14 |    4 |   16 |
| object1 |         7 |  0 |   16 |    4 |   23 |
| object2 |         5 |  1 |    0 |    5 |    5 |
| object3 |         6 |  0 |    5 |    5 |   11 |
| object4 |         8 |  0 |   11 |    5 |   19 |
| object5 |         2 |  0 |   19 |    5 |   21 |
+---------+-----------+----+------+------+------+
20 rows in set (0.00 sec)

mysql>

Look at the cc column. It increments every time the sum exceeds @freq_limit (20) and resets.

Now just run GROUP_CONCAT on object, grouping by cc

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object2,object3,object5,object1 |
|    3 | object4,object5,object1,object2 |
|    4 | object5,object1,object3,object4 |
|    5 | object2,object3,object4,object5 |
+------+---------------------------------+
5 rows in set (0.00 sec)

mysql>

Just set @freq_limit to whatever value you need.

To show it works for just the 5 rows you supplied:

mysql> drop database if exists all_db;
Query OK, 1 row affected (0.04 sec)

mysql> create database all_db;
Query OK, 1 row affected (0.00 sec)

mysql> use all_db
Database changed
mysql> create table table1
    -> (
    ->     object varchar(255),
    ->     frequency tinyint unsigned
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into table1 values
    -> ('object1',7),('object2',5),('object3',6),('object4',8),('object5',2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set @freq_limit = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @freq_sum   = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select cc,GROUP_CONCAT(object) objects from
    -> (
    -> select * from
    -> (
    ->     select *,
    ->         @freq_groupinc := IF(@freq_count >= @freq_limit,1,0)           bb,
    ->         @freq_count    := IF(@freq_count >= @freq_limit,0,@freq_count) aa,
    ->         @freq_group    := @freq_group + @freq_groupinc                 cc,
    ->         @freq_count    := @freq_count + frequency                      dd
    ->     FROM table1
    -> ) AA) A GROUP BY cc;
+------+---------------------------------+
| cc   | objects                         |
+------+---------------------------------+
|    1 | object1,object2,object3,object4 |
|    2 | object5                         |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Related Solutions

Mysql – InnoDB – How to get top locked tables and rows which are locked

Just search for 'lock'

mysql> pager grep -C 3 -i lock
PAGER set to 'grep -C 3 -i lock'
mysql> show engine innodb status\G
... results ...
1 row in set (0.00 sec)

mysql> \n
PAGER set to stdout
mysql>

Mysql – PHP and theSQL – Binding a parameter in an if statement

I'm more mysql than php but are you binding the $guestid php variable twice? You need to supply a variable for each ? placeholder in prepared statements and presumably so in mysqli. In a native prepared statement on the server, the ? is legal within an IF() but then again so is ? = ?, which apparently mysqli doesn't allow.

But also, you could rewrite the query:

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that i.guestId has to contain the guestId you're interested in, because you've specified it in the where clause so we should be able to compare it this way.

However, there seem to be other issues with your query.

LEFT JOIN rsvpList AS r ON r.eventId = e._id

It seems like this should actually be:

LEFT JOIN rsvpList AS r ON r.eventId = e._id AND r.guestId = i.guestId

...because otherwise you're joining all of the rsvpList entries for that event, not just those for the guest in question. You may have tried to filter for this in the WHERE clause at some point, which doesn't work if they haven't rsvp'ed.

This change also illustrates that the first comparison in your IF() is actually unnecessary.

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that r.eventId will match e._id OR will be null if no RSVP, so we don't need to test it, because r.guestId will be what we want or null if no rsvp, so this becomes simply:

r.guestId IS NOT NULL as `RSVP-ed`

the truthiness of the assertion that r.guestId is not null in the joined-up result set is returned as a 1 or 0, and r.guestId will be null with no rsvp because there's no matching row in rsvpList for that guestId.

An equivalent and slightly more intuitive (but redundant) expression of that would be

IF(r.guestId IS NOT NULL,1,0) as `RSVP-ed`

Best Answer

Related Solutions

Mysql – InnoDB – How to get top locked tables and rows which are locked

Mysql – PHP and theSQL – Binding a parameter in an if statement

Related Question