Moving fields in a MySQL Table

[Germaris]

Hi there!

I just inserted a new field in my MySQL table and populated it by hand.
I find it to be in the wrong place i.e. I want to move it from the end of the table to the center of the table just after a determined field.

How do I perform this task?

Many thanks in advance for your help!

 

[KarveR]

This really should have been asked in the mysql forum but heres the ALTER TABLE page of the manual anyway.

I don't think you can move columns, you'll probably have to delete and re add it.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ingresman]

Why does the position matter any way ??

 

[KarveR]

less code I guess... albeit a very small amount

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[DRJ478]

I can't imagine how the internal position of a column in a data row would make any difference unless there is iterative printing with offsets going on. That, however, is something I would recommend against.

while ($row = mysql_fetch_row($result)){
   echo("<tr>");
   foreach ($row as $value){
       echo("<td>".$value."</td>");
   }
   echo("/tr>");
}

This might look ok but if another column ever is inserted the whole thing is messed up, a time bomb and headache if a change is introduced. IMO The best choice would be to use mysql_fetch_assoc() and use explicit column names. The, literallt, the position of the column in the data structure does not matter.

 

[Germaris]

Don't be paranoid brothers!!! ;-)))

I want to move the field only for comfort of use!
Say in PHPMyAdmin half of my table (which has sixteen Fields) is out of the screen and it's very boring to scroll horizontally to view the misplaced Field which is located at the end of the table (and on which I'll make frequent changes in the values it contains).

Cheers!

Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[ingresman]

That's a fair enough reason, my pet hate is when you do a select in the mysql tool and it shoots off the to of the command window before you see the column names !.
Your best bet might be to set up a couple of scripts which select each coumun rather than a * (which I'm assuming here).

 

[Germaris]

The syntax I used to move the column is:
ALTER TABLE table_name MODIFY COLUMN column_name INT AFTER the_other_column_name

...and guess what? It works!

))

Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[DRJ478]

I rememeber that PHPMyAdmin offers an interface to insert new colums into a specific place. Of course, all it does is to issue the same query you just mentioned.
Who's paranoid?

 

[Germaris]

Sorry but, the query I refer to is not that simple.
My problem (already solved) was not to insert a new column but to MOVE an existing column from the end of the table right after the first column.
I am a newbie but at least I know how to insert a new column (or a new Field, if you prefer).

I referred in a humoristic way to some of the replies which deviate from the problem to dive into some kind of horror story... (see Ingresman).
If I offended somebody, I apologize. ;-)))

Best regards.


Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[DRJ478]

No offense. Look at the worm!
Cheers,
DRJ478

 

[ingresman]

What horror story did I tell ????