How to duplicate and auto increment columns in sql?

Question

I haven't been coding in ages so consider me a real basic user.

I am implementing vtiger and trying to make it easier by playing directly with the database.

Here is my issue :

In my table vtiger_paiements I have 2 columns : paiements_tks_s1 and paiements_tks_s1typepaiement.

I want to duplicate those 2 columns and auto increment the duplicates to s53 (one after another, meaning paiements_tks_sntypepaiement is always following paiements_tks_sn).

And i have absolutely no clue how to do that.

I really don't want to create 53x2 tabs in vtiger...

Thanks for you help

PS : in general. Let's say I have a column called payment1. I want to duplicate this column N times and call it payment2, payment3, payment4...paymentN.

`paiements_tks_s1 !==paiements_tks_sn` And `paiements_tks_s1typepaiement !== paiements_tks_sntypepaiemen` — Alive to die - Anant, Feb 25 '21 at 09:53
When you say duplicate the column... Do you mean to you want a table with 53x2 columns? Or you want 53 rows with those two columns? — Steven, Feb 25 '21 at 09:55
Show an example (`s53` is excess, of course, `s3` will be enough). — Akina, Feb 25 '21 at 10:12
I want 53x2 colums. Actually those are paiements made every week. So I need 52+1, so 53 columns, one for tks and one for tks_typepaiement — Florence S, Feb 25 '21 at 10:31
Just a huge warning. vTiger does not pay nice with direct db manipulation. make sure you are keeping track of fields you are creating in the vtiger_fields table or the query generator will not find them and vTiger will crash. — Bradley Armstrong, Jul 19 '21 at 21:59

Steven · Accepted Answer · 2021-02-25T12:23:45.620

Okay, so lets assume you have a table with the current structure something like:

payments
    > id int(11) PRIMARY KEY AUTO_INCREMENT
    > user_id int(11)
    > payment1 int(11)
    > paymentType1 int(11)

And what you want to do is create additional columns so that you have 53 pairs of the last two columns...

    > payment2 int(11)
    > paymentType2 int(11)
    > payment3 int(11)
    > paymentType3 int(11)
    ...
    > payment53 int(11)
    > paymentType53 int(11)

Then you just need to use an ALTER TABLE query to add the columns.

You can generate the query like:

$tableName      = "payments";
$startingNumber = 2;
$endingNumber   = 53;

echo "ALTER TABLE {$tableName}\n";
for ($i = $startingNumber; $i < $endingNumber+1; $i++) {
    echo "    ADD COLUMN payment{$i} int(11),\n";
    echo "    ADD COLUMN paymentType{$i} int(11)" . ($endingNumber === $i ? ";": ",\n");
}

You can change echo to a variable and then concatenate and run it in PHP after the for loop or copy/paste the resulting query into PHPMyAdmin.

Run in PPH

$tableName      = "payments";
$startingNumber = 2;
$endingNumber   = 53;

$sql = "ALTER TABLE {$tableName}\n";
for ($i = $startingNumber; $i < $endingNumber+1; $i++) {
    $sql .= "    ADD COLUMN payment{$i} int(11),\n";
    $sql .= "    ADD COLUMN paymentType{$i} int(11)" . ($endingNumber === $i ? ";": ",\n");
}

// Assuming that you have an active connection to the db
// and $mysqli is the mysqli object
$mysqli->query($sql);

However, I would suggest that you think about your DB design. It seems to me that you could simply have a table like:

payment
    > id
    > user_id
    > number   -- If you want to number them specifically
    > amount
    > type

Having 50+ columns seems like a lot of overhead...

How to duplicate and auto increment columns in sql?

1 Answers1