mysql-problems with adding columns

Question

So, I have problem with adding columns to table tablica, tablica has only id column and trough loop I am adding column names with ALTER query and then I am adding values from table prva with query INSERT, here's my code:

    if(isset($_POST['submit']))
    {
        mysql_query("CREATE TABLE tablica (id INT PRIMARY KEY AUTO_INCREMENT)");

        if(isset($_POST['kolona']))
            {
                foreach($_POST['kolona'] as $vrednost)
                  {  

                mysql_query("ALTER TABLE tablica ADD $vrednost text");

                mysql_query("INSERT INTO tablica ($vrednost) SELECT $vrednost FROM prva");}

            }
    }

I have problem in what way columns are added, first column is ok, then the second one begins where the first ends, so my ouput is something like this:

id   column1  column2
1     a1       NULL
2     a2       NULL
5     NULL     b1
6     NULL     b2

and it needs to be:

id  column1   column2
1    a1        b1
2    a2        b2

so I need to put the column2 in the place, than and id's will be sorted also i think. Can anyone help? Tnx

Why dont you alter your column so it doesn't allow NULL values. — Mihai, Oct 04 '13 at 23:11
I tried to change query to: mysql_query("ALTER TABLE tablica ADD $vrednost text NOT NULL"); but I am getting only empthy spaces instead of NULLs — iva, Oct 04 '13 at 23:29
Its like that above, just i have more a-s and b-s, and a-s and b-s are some text — iva, Oct 04 '13 at 23:37
Write in your mysql client SHOW CREATE tablica and post the results here. — Mihai, Oct 04 '13 at 23:38

score 0 · Accepted Answer · answered Oct 04 '13 at 23:41

0

You are doing inserts where you only supply one column:

INSERT INTO tablica ($vrednost) SELECT $vrednost FROM prva

This will force the other columns that don't have defaults or autoincrement to be null.

Instead of doing the insert in every iteration of the loop, you should get the different $vrednost in the foreach iterations and after the loop do a query something like:

INSERT INTO tablica (vrednost1,vrednost2,...) SELECT vrednost1,vrednost2,... FROM prva

answered Oct 04 '13 at 23:41

Filipe Silva

20,688
4
50
67

Its a bit problem to do that because i will not know how many columns is needed to be add, it would be always different number, so it would be and a different number of vrednost1, vrednost2,... – iva Oct 05 '13 at 00:00
Yes. But you would do alter table in every iteration of the loop. Then you could do the insert using something like [this question](http://stackoverflow.com/q/2435216/1385896) on your `$_POST['kolona'] ` to construct your insert. – Filipe Silva Oct 05 '13 at 00:11
you mean to break array $_POST['kolona'] with implode to get vrednost1, vrednost2..? – iva Oct 05 '13 at 00:42
yea, ok its working when i put precise number of vrednost1, vrednost2... but when i put it in a loop again same problem, damn, dont know how to construct insert – iva Oct 05 '13 at 00:55
I'm not very familiar with php, but searching around i found [this answer](http://stackoverflow.com/a/7051792/1385896). You replace the userData from that question with your $_POST['kolona'] and instead of values, you do the select. That should do it. – Filipe Silva Oct 05 '13 at 01:10
Yesss it works, finally, i change the insert query into this: mysql_query("INSERT INTO tablica ( ".(implode(',',($_POST['kolona']))).") SELECT ".(implode(',',($_POST['kolona'])))." FROM prva"); and works perfectly, tnx Silva – iva Oct 05 '13 at 13:31

mysql-problems with adding columns

1 Answers1