Looping through rows and replace values

[Amaina]

I have 2 tables that look like this.(see below). Table one is named tableX and table 2 is named tableY.

+-------+---------+
| count | post_id |
+-------+---------+
|     3 |    7209 |
|     3 |    7210 |
|     1 |    7211 |
|     1 |    7212 |
|     1 |    7213 |
|     1 |    7215 |
+-------+---------+

+---------+---------+------------+------------+
| meta_id | post_id | meta_key   | meta_value |
+---------+---------+------------+------------+
|      32 |    7209 | multiple   | 3          |
|      33 |    7209 | multiple_0 | 27         |
|      34 |    7209 | multiple_0 | 34         |
|      35 |    7209 | multiple_0 | 1          |
|      36 |    7210 | multiple   | 3          |
|      37 |    7210 | multiple_0 | 11         |
|      38 |    7210 | multiple_0 | 37         |
|      39 |    7210 | multiple_0 | 51         |
|      40 |    7211 | multiple   | 1          |
|      41 |    7211 | multiple_0 | 36         |
|      42 |    7212 | multiple   | 1          |
|      43 |    7212 | multiple_0 | 11         |
|      44 |    7213 | multiple   | 1          |
|      45 |    7213 | multiple_0 | 15         |
|      46 |    7215 | multiple   | 1          |
|      47 |    7215 | multiple_0 | 42         |
+---------+---------+------------+------------+

I am looking for suggestions on how i can change values in the field "meta_key" so that tableY looks like the table below whereby multiple auto_increments with values 0 to nth number depending on the count of post_id using php

+---------+---------+------------+------------+
| meta_id | post_id | meta_key   | meta_value |
+---------+---------+------------+------------+
|       1 |    7209 | multiple   | 3          |
|       2 |    7209 | multiple_0 | 27         |
|       3 |    7209 | multiple_1 | 34         |
|       4 |    7209 | multiple_2 | 1          |
|       5 |    7210 | multiple   | 3          |
|       6 |    7210 | multiple_0 | 51         |
|       7 |    7210 | multiple_1 | 11         |
|       8 |    7210 | multiple_2 | 37         |
|       9 |    7211 | multiple   | 1          |
|      10 |    7211 | multiple_0 | 36         |
|      11 |    7212 | multiple   | 1          |
|      12 |    7212 | multiple_0 | 11         |
|      13 |    7213 | multiple   | 1          |
|      14 |    7213 | multiple_0 | 15         |
|      15 |    7215 | multiple   | 1          |
|      16 |    7215 | multiple_0 | 42         |
+---------+---------+------------+------------+

[Amaina]

This is what i have used but cannot get desired results

$con = dbConnect();
$query = "select post_id,'multiple' as multiple,count(*) as count from xyz AS t1 group BY t1.post_id";
$sql=$con->prepare($query);
$sql->execute();
$sql->setFetchMode(PDO::FETCH_ASSOC);

    while($row=$sql->fetch()){
        $multiple = $row['multiple'];
        $post_id[] = $row['post_id'];
        $count[] = $row['count'];
        echo "<pre>"; 
        }
        $c = array_combine($post_id, $count);
        foreach($c as $key=>$value){
            for($i=0;$i<=$value;$i++){
                $post_id = $key;
                if($i==0){
                    $meta = $value;
                    $multiple = 'multiple';
                }
                else{
                    $x = $i-1;
                    $meta_value = $multiple . "_$x";
                    $meta_key = $x;
                    echo $meta_value . "<br>";
                }
$query2 = "insert into tableY(post_id,meta_key, meta_value) values (:post_id, :meta_key, :meta_value)";
$stm = $con->prepare($query2);

$stm->bindValue(':post_id', $post_id,PDO::PARAM_INT);
$stm->bindValue(':meta_key', $meta_key,PDO::PARAM_STR);
$stm->bindValue(':meta_value', $meta_value,PDO::PARAM_STR);
$stm->execute();
echo "success";
            }
        }

[cereal]

Hi,

I have an idea for this. You could write a sequencer function, to keep track of the count. You need InnoDB tables to do this, transactions and a function:

DROP FUNCTION IF EXISTS `seq`;
DELIMITER //
CREATE FUNCTION seq(`pID` INT UNSIGNED, `lID` INT UNSIGNED) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE `n` INT UNSIGNED;
    select MAX(CAST(SUBSTRING_INDEX(`meta_key`, '_', -1) AS UNSIGNED)) INTO `n` FROM `tableY` WHERE `post_id` = `pID`;
    UPDATE `tableY` SET `meta_key` = CONCAT('multiple_', last_insert_id(`n` + 1)) WHERE `post_id`=`pID` AND `meta_id` = `lID`;
    RETURN last_insert_id();
END
//
DELIMITER ;

The table structure is like you described:

CREATE TABLE IF NOT EXISTS `tableY`(
    `meta_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT UNSIGNED NOT NULL,
    `meta_key` VARCHAR(100) NOT NULL DEFAULT 'multiple',
    `meta_value` INT UNSIGNED DEFAULT 0
) ENGINE = InnoDB;

And here's a short example of the usage in SQL:

-- enable the transaction
START TRANSACTION;

-- insert
INSERT INTO `tableY` (`post_id`, `meta_key`, `meta_value`) VALUES(7209, 'multiple', 3);

-- run the sequencer, this will update the inserted row
SELECT seq(7209, LAST_INSERT_ID());

-- save the insert/update
COMMIT;

And so on: after each insert statement, you run the seq() function with the post_id value as first argument and the LAST_INSERT_ID() as second, so that it will update only the current insert.

Here are other inserts:

START TRANSACTION;
INSERT INTO `tableY` (`post_id`, `meta_key`, `meta_value`) VALUES(7209, 'multiple', 31);
SELECT seq(7209, LAST_INSERT_ID());
COMMIT;

START TRANSACTION;
INSERT INTO `tableY` (`post_id`, `meta_key`, `meta_value`) VALUES(7209, 'multiple', 313);
SELECT seq(7209, LAST_INSERT_ID());
COMMIT;

-- This runs on post_id 7210
START TRANSACTION;
INSERT INTO `tableY` (`post_id`, `meta_key`, `meta_value`) VALUES(7210, 'multiple', 21);
SELECT seq(7210, LAST_INSERT_ID());
COMMIT;

When you select, you get something like this:

> SELECT * FROM `tableY`;
+---------+---------+------------+------------+
| meta_id | post_id | meta_key   | meta_value |
+---------+---------+------------+------------+
|       1 |    7209 | multiple_1 |          3 |
|       2 |    7209 | multiple_2 |         31 |
|       3 |    7209 | multiple_3 |        313 |
|       4 |    7210 | multiple_1 |         21 |
+---------+---------+------------+------------+
4 rows in set (0.00 sec)

As you see the counter restarted again. Now, I see it's not the same of what you want: start with multiple and then multiple_0, multiple_1, multiple_N, but it should be easy to fix, by adding IF statements in the function, or by removing the multiple_ fragment, and saving only the integers, so that you can add those bits only in the query, through CONCAT(). Hope it makes sense, I have not tested this extensively, but I've seen few warnings like this:

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'multiple' |
+---------+------+-----------------------------------------------+

It happens because of the meta_key value being inserted as multiple without appending a number. Here I would, probably, start with multiple_0 and make an exception to avoid the update when there is only a row under the inserted post_id and the meta_key is equal to 0.

Something similar was discussed in this thread, but for MyISAM tables:

• https://www.daniweb.com/programming/databases/threads/478191/mysql-db-design-storing-invoices-that-starts-from-1-each-financial-year