[RESOLVED] change order of columns

mrjoeblack

Can anyone tell me if I can change the order of the columns in a mysql database once they contains data .. if so HOW? thanks

bradgrafelman

Why on earth would you want to do so even if you could?

mrjoeblack

well, there is a reason, more to do with an admin use. If one were to print_view a selection of rows and then one was to print this to paper, perhaps there is a column which is sensitive information and needs excluded from the printed copy.

If this sensitive column was originally set up as the last column but now is in the middle of the columns because new columns have been added due to extra fields, then being in the middle makes it impossible to be simply trimmed off like it used to be originally. So being able to move the column to the end column would be useful.

hahaha, bet you didn't expect that answer!!!

bradgrafelman

mrjoeblack;10996436 wrote:
hahaha, bet you didn't expect that answer!!!

Not really.. I was hoping for one that made sense.

What does "print_view" mean?

Regardless, if you want to show the columns in a different order, then simply output them in a different order.

mrjoeblack

how do i output them in a different order? To be clearer, I was reffering to working with the database in phpmyadmin where I can print-view selections but I see no way of changing column order.

Weedpacket

I would have just created a new view in the database containing the columns I want in the order I want.

bradgrafelman

SELECT col1, col2 FROM myTable

will show col1 followed by col2

SELECT col2, col1 FROM myTable

will show col2 followed by col1.

EDIT: And also, what Weedpacket said.

traq

furthermore, if [font=monospace]col2[/font] contains "sensitive" data that you don't want to be shown, simply use

SELECT col1 FROM mytable

Derokorian

Furthermore, using PHPMyAdmin when viewing the structure page of a table, you can click the little checkboxes to the left of the column names then click Browse under With Selection, and this will only show the columns which you checked.

mrjoeblack

Hello,

ok I tried what you said, but that doesn't seem to only browse the selection, it just returns to the full normal browse of the fields/columns.

Anyway, what i did was, just rebuild the dbase again putting the columns in the preferred order. So it seems, unless someone knows differently, that one must make sure the table structure is built absolutely correctly in the beginning and taking into account possible future alterations which might arise.

I am however, quite surprised there is no way of altering the column order after data is input.

thanks for your input.

mrjoeblack

way hey!!! Weedpacket had it .... once I figured out how to create a view, I cracked it with an sql query in myadmin as follows

CREATE VIEW vw_myview AS
SELECT Table1.field1, Table1.field2, Table2.field1
FROM Table1
ORDER BY Table1.field1

Thanks mate 🙂

bradgrafelman

mrjoeblack;10996469 wrote:
So it seems, unless someone knows differently, that one must make sure the table structure is built absolutely correctly in the beginning and taking into account possible future alterations which might arise.

No, you missed the point of our posts above entirely.

The "order" of the columns in the table is completely irrelevant, because you simply need to SELECT them in whatever order you wish.

mrjoeblack;10996469 wrote:
I am however, quite surprised there is no way of altering the column order after data is input.

There is a way (using ALTER TABLE statements), but it's not really useful (see above statement for why).

mrjoeblack

Brad, that's funny, I was feeling you missed the point of what I was trying to achieve this time ... I see no way of selecting columns out of order using phpMyAdmin to produce a view for exporting. However, no worries, I still think you are a great guy, as you've helped me a lot, so thanks for that. I need you understand I'm stupid when it comes to this stuff and kinda old for it too lol, so please bare with me.

I did however solve the problem now as I've said, so I can "resolve" this now.

cheers to all, until my next problem lol.

Derokorian

I think the point was, if you are just "trimming" off the "columns displayed last" then you don't even need to select those columns in the first place.

EX: I have a table with 5 columns: id,fname,lname,dob, and social security number. Now I want to give this info to someone without them seeing the SSN, instead of printing it out as is and then literally cutting that information off, i just SELECT id,fname,lname,dob FROM people and now SSN isn't even showing.

mrjoeblack

yes Derok, BUT BUT BUT......

if the social sec number is not the end column and it's the middle column, I see no way of selecting the columns around it using phpMyadmin....not on my system anyway!!!

if the ssec column was the last column, yes i could do what you suggest.

I did post the solution tho 🙂

bradgrafelman

mrjoeblack;10996501 wrote:
if the social sec number is not the end column and it's the middle column, I see no way of selecting the columns around it using phpMyadmin....not on my system anyway!!!

SELECT col1, col2, col3, col5, col6 FROM myTable

(where col4 is the "middle column" that contains the SSN).

mrjoeblack

To create a specific view for my output dump....

CREATE VIEW vw_myview AS
SELECT Table1.field1, Table1.field2, Table1.field3
FROM Table1
ORDER BY Table1.field1

bradgrafelman

I agree that it sounds like for the purposes of generating a report (especially if you're going to be doing this more than once), creating a view is a good solution (perhaps the 'best' solution).

However, that's not to say that it's the only way to do what you were after. That's all I was trying to convey.

Derokorian

Try this:

create table test ( col1 int, col2 int, col3 int );
insert into test values (1,2,3);
select col1,col3 from test;

Which shows me the screenshot (see attached)

mrjoeblack

ok Brad, yes I see the confusion .... I didn't want to make an sql query each time this needed to by done as it would be done several times a day or maybe an hour. We needed a more idiot proof and simple way to bring up the info we needed and so that monkey's could do it rather than techy guys.

hope this explains better now ....

The create a custom view method makes it a simple selection in the dbase tables list.

Just solving an admin headache around the office here :rolleyes: