mysql query dynamic update

RESOLVED

I have a mysql table (table1) with many columns with a single ID.

table name: table1

columns: from “pt1 to pt1000”

id = 1

• I have a query that updates the data in the columns, 10 data at a time
• I would need a unique and dynamic query that inserts the 10 values ​​in sequence at each submission.

example:

1. send query update command (from php form)

2. I insert the 10 values ​​in the columns from “pt1” to “pt10”

3. send query update command with other data (from the same php form)

4. I insert the 10 values ​​in the columns from “pt11” to “pt21” and so on

Question: How can I set the query so that it knows to insert the values ​​in the right columns and not always in the same ones?

Thanks to all help me

—————— EDIT —————————–

I resolve passing manual (input and select value) from php form to php page of query and insert this value in variable to construct dynamical query.

For example:

IN PHP FORM: have two select

1 – Select DB (value = “name_db_i_want_to_update”)

2 – Select Column ( value = “1,2,3,4,5,6,7,etc”)

IN PHP QUERY PAGE:

1. Select DB Value as “$ db”
2. Select Column as “$ set”
3. IF statement with piece of query set. IF column value = 1 ELSE IF etc, $ setquery = pt1 = “value1″,”value2” etc.
4. Construct dynamical query ( UPDATE $db SET $setquery WHERE id=1 )

it is not the best solution, certainly also wrong as a programming logic, but it works.

Thanks to all for help

Answer

You shouild change your design to somethingk like mytable2, to gather the data will be a bit more sql programming needed, but it should be a one time thing to get your data in the form you need.

If you insist on keeping your design, you can use a stored procedure like blow to fill your table, but you have to know the sequence where the belong, this could be automated as well, with another table wherre you have the already filled data position stored , but the basic principle of dynamic sql is the same.

CREATE TABLE mytable2(id int, pos int, value int)

INSERT INTO mytable2 VALUES (1,1,1),(1,2,2),(1,3,3),(1,4,4),(1,5,5),(1,6,6)

CREATE TABLE mytable(id int, pt1 int, pt2 int, pt3 int, pt4 int, pt5 int, pt6 int)

CREATE PROCEDURE `new_routine` (_id int, _sequence int, _pt1 int, _pt2 int,_pt3 int)
BEGIN

    IF _sequence = 0 then
       SET @sql := CONCAT('INSERT INTO mytable (id,pt1,pt2,pt3) VALUES (',_id,',',_pt1,',',_pt2,',',_pt3,')');
    ELSE
       SET @sql := CONCAT('UPDATE mytable SET pt',1 +((_sequence -1) * 3) ,'= ',_pt1
                          ,', pt',2 +((_sequence -1) * 3) ,'= ',_pt2
                          ,', pt',3 +((_sequence -1) * 3) ,'= ',_pt3,' WHERE id = ',_id);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

CALL new_routine(1,0,1,1,1);

CALL new_routine(1,1,2,2,2)

CALL new_routine(1,2,3,3,3)

SELECT * FROM mytable

id | pt1 | pt2 | pt3 | pt4 | pt5 | pt6
-: | --: | --: | --: | --: | --: | --:
 1 |   2 |   2 |   2 |   3 |   3 |   3

SELECT * FROM mytable2

id | pos | value
-: | --: | ----:
 1 |   1 |     1
 1 |   2 |     2
 1 |   3 |     3
 1 |   4 |     4
 1 |   5 |     5
 1 |   6 |     6

db<>fiddle here