How to duplicate and auto increment columns in sql?

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 53×2 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.

Answer

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…