[RESOLVED] Change Order Fileds in table

alirezaok

i have a table with 3 fields : ID name age

i want to change order of these fields in table to: ID age name

is it possible?

Piranha

Why do you want to change the order of the fields? The easiest way is probably to select in the order you want:

SELECT id, age, name FROM table WHERE...

Then it doesn't matter in what order the fields are.

alirezaok

i need to it for some subjects
as example i used this line in some locations in my scripts:

$sqlquery = "INSERT INTO table VALUES('0', '$name', '$age')";

i forced to change all of these lines in my scripts to

$sqlquery = "INSERT INTO table VALUES('0', '$age', '$name')";

but if my request possible i don't need to this changings

Piranha

Not to be a wise ass, but now you know why you should always include the column names in queries. Then it would be something like this:

$sqlquery = "INSERT INTO table (id, name, age) VALUES('0', '$name', '$age')";

And you would not have to change anything in any query, no matter what order you wrote it in. I hope you will do it like this in the future.

But now to your problem. I am sorry, but I don't know how to do it. Of course it is something with ALTER TABLE, but I have no idea how it is really done. Hopefully someone else can help you out.

alirezaok

i think mysql must make it possible to chaging the orders of fields directly.
some times it is very necceesery.

thank you for your help
i am wating to any help to solve this request

laserlight

How is it necessary in this case? Use what Piranha suggested.

alirezaok

Piranha's suggest is good for future.

but now i need to change order of fileds.

Piranha

Actually my suggestion is good now to prevent problems in the future. It is just to search for "insert into" and add the column names everywhere.

alirezaok

this was only one example that explained why do i need to change order.
i want to some other works that i need to change order

another example i have a text file that any rows make one record (Fields terminated by ,)
as example:
1,20,mike
2,35,ally

u see first section of each rows is ID second is age and 3'd is name
but my table is diffrence of this stracture's order and i can not use of

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'

alirezaok

it is very strange that when you want to add new field you can choise the location of new field (for example after ID filed). but after you added field there is no way to change it's location.

bradgrafelman

Strange? Maybe it's not so obvious because it's not so important at all! :p

But honestly here, I think this is one of those "can't see the forest for the trees" types of situations. For INSERT queries, you should always name the columns within the query. For your LOAD DATA INFILE query, you could write it like so:

LOAD DATA LOCAL INFILE 'data.txt'
INTO TABLE table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, age)

Once again, doing this makes it easy to see which order to have the data in (or the opposite; if the data changes, it's quite simple to adjust the query accordingly).

Finally... if you stubbornly refuse to listen to our advice and would rather use a rather unreliable workaround to avoid fixing your problem, you can use the ALTER TABLE syntax to change the order:

ALTER TABLE myTable MODIFY column_name column_definition AFTER column2_name

Note that this requires you to know the column's definition (get it wrong, and the column will be changed - oops!).

alirezaok

thank you very much

i have 2 question

what does column_definition stand for?

and can i use "before" instead "after" for moving one column to first?

thanks again

Piranha

To your first question: Have you searched to find an answer?

To your second question: Have you tried it?

You know, by doing those things first you will normally find the answer and be able to solve the problem before you would otherwise get an answer.

alirezaok

excusme!

you say right i did some laziness (because bradgrafelman said this subject is not obvious and i thinked there is not more information about it. also before start this Thread i searched for it too and i couldnot reach success.) anyway thanks for your advice and thanks of your actvities in this forum. actualy you are one of knowing good members that most others members use of your information.

first questione solved 🙂

about second question it seems BEFORE not works and i found a way to move one field to first. i moved my column to after first field then moving first field after second field.

bradgrafelman

alirezaok wrote:
i moved my column to after first field then moving first field after second field.

Wow, you're all about avoiding the problem and working around it instead of fixing it!

If you would have looked up the ALTER TABLE Syntax in the MySQL manual, you would have seen that there is a "FIRST" option instead of "AFTER col_name".

Sxooter

If you find yourself needing to move your columns about, you've got a problem other than the one of how to move your columns about. I.e. if your column order matters then things are already broken, and you just don't know it yet.

alirezaok

i dont know why you prevent to use of this way to solve the problem. each ways is good for some conditions and i does not think this way damage to other things. however other ways like using field name in insert sql query is better but this way is not so bad way that thinking.
any way thanks of all for advise to solve this problem. 🙂

Piranha

The reason why we don't want this workaround is that it will break as soon as you do something with the table. For your sake I hope that it will work in the long run, but you probably have to solve the problem later on instead of now. I don't think that you will say that it was a good solution in a year.

Sxooter

Look, I'm all for "get it running now" mode. I've been there enough myself in the past. But I also know that slapping a dirty hack on it (what you're doing) eventually comes back to bite you in the butt if you don't fix it properly.

Like the saying goes, you can pay now, or pay later.