Field position

Anon

I'm looking for a command in MySQL, that allow to move a field from a position to another in my DB.

I use PHP programs work with the number of field in DB:

field1 = name
field2 = age
field3 = nick

..
I have to move some field from his position to another, or insert a new field in the middle of DB.

ES.
field1 = age
field2 = nick
field3 = SURNAME new field
field4 = name
..
Thanks

Italiano:

Sto cercando un comando per MySQL che mi permetta di spostare un campo da una posizione ad un\'altra.

Ho dei programmi che lavoranocon i vari campi non direttamente con il nome ma con il numero progressivo degli stessi
campo1 = nome
campo2 = cognome
campo3 = indirizzo
..
Dovendo però interrogarne solo alcuni ad esempio dal campox al campoy ho il problema che nell\'intervallo ce n\'è uno che non mi interessa e potendo spostarlo come ordine in un\' altra posizione risolverei il problema.

ES.
campo1 = indirizzo
campo2 = cognome
campo3 = nome
..

Grazie.

Anon

This should do it:

INSERT INTO new_table (field1, field2, NEWFIELD, field3) SELECT (field1, field2, field3) FROM old_table;

Thanks to Vincent for an earlier tip, blame him if it doesn't work 😉

--ph

Anon

Nice one Peter... :-)

Just wanted to comment that the app you build in PHP should never depend
on the order of fields in your table.

If you want to add a new field, just add a column at the end, and use your select statement to specify which fields you want, and what order you want to receive them in.

So, if your table looks like this:

field1 | field 2 | field 3 | field 5 | field 4

you can get the correct order by using;

select (field1,field2,field3,field4,field5) from table

This saves you a LOT of trouble lateron.

(trouble like this question, how do I insert a new column between two existing columns)

Anon

Yeah, but it looked to me like giulio wanted to do it the hard way 😉

...which I have a knack for always finding.

--ph

Anon

Correctus :-)

If you want another hard way of doing it, user ALTER TABLE to rename the fields... lovely... :-)

Anon

I have to change the position of field because I use this type of script:

$result3 = mysql_db_query($nomedb,"select * from $tab");
$result1 = mysql_list_fields($nomedb, $tab);
for ($i=1 ; $i<mysql_num_fields($result1); $i++)

{
$nc = $ { "field$i"};
$nv = $ { "val$i" };
echo "</tr>
<td noWrap width=180>$nc</td>
<td noWrap width=40>$nv</td>
</tr>"; }

So I have to leave a particular order in column, or modify table to create order.
There is a script like this:

INSERT INTO new_table (field1, field2, NEWFIELD, field3) SELECT (field1, field2, field3) FROM old_table;

to organize field position?

Thanks for speedy answares.