Order Table by X but keep ID?

[Pinchanzee]

I'm looking to re-order a table while keeping the table's id column constant.
ie, Links has entries in no particular order, whereas LinksPopular is a duplicated table but I want it ordered by category X, while leaving the id category unchanged.
That way the first most popular is at id 1, second at id 2, etc, instead of the index's being all over the place.

Does anyone know how to do this?
I've tried dropping the id column then re-adding it in order, but I couldn't get that to work with the auto_increment, and it doesn't seem very efficient.

Thanks in advance.

[smantscheff]

Please show us your code.

[Pinchanzee]

My code isn't really relevant, this is generic.

Lets say table X has columns "id, Y, Z, P"
I want to order the table by P, which will change Y and Z relatively, but keep id unchanged.

BEFORE:
id . Y .. Z .. P
1....1....9....8
2....4....7...10
3....7...12....3

AFTER:
id .. Y .. Z .. P
1.....7...12....3
2.....1....9....8
3.....4....7...10

I'd also like to be able to order it randomly, leaving id unchanged, if possible.

[smantscheff]

If your code is not relevant, what is? Does it mean you didn't even try a solution?

Test this one:

create table tBefore 
(id integer,
y integer,
z integer,
p integer);
insert into tBefore values
(1,1,9,8),
(2,4,7,10),
(3,7,12,3);

create table tAfter
(id integer not null auto_increment primary key,
y integer,
z integer,
p integer);
insert into tAfter (y,z,p) (select y,z,p from tBefore order by p);
select * from tAfter;

And what do you mean by "order it randomly"? Maybe you just replace "order by p" in the above query with "order by rand()";

[Pinchanzee]

My code is irrelevant because I straight up didn't know how to do it.

select y,z,p from tBefore order by p;

That's looks like exactly what I needed, thanks! Didn't realise it'd be as easy as simply not selecting the id column.. Amn't in the position to try it at the moment but I'm sure it'll work.And what do you mean by "order it randomly"? Maybe you just replace "order by p" in the above query with "order by rand()";

Again, underestimated how simple it would be, thanks again!

[Pinchanzee]

Ok it looked like it would work but I can't get a simple query working. (using PHP)

mysql_query("SELECT Title
                    , LinkTitle
                    , Image
                    , Type
                    , Description
                    , Categories
                    , Age
                    , Rating
                    , Likes
            FROM LinksPopular
            ORDER BY Likes");

LinksPopular is a duplicate table of Links.
(given the id category is ignored)
SELECT * doesn't even seem to work either.
Is there something obvious that I've missed?
(obviously I have the connection scripts at the start, and I'm just executing the script via localhost/path/file.php)
Tried FROM Archive.LinksPopular (db name) and that didn't work either.

[drjohn]

The order of data in your table is totally and utterly irrelevant.
Extract you data as required via a query and include an ORDER BY clause.
Display it on your web page and fiddle around with the display using PHP.

No need at all to "re-order" a table. This is a basic tenet of relational database theory. And the same applies to the order that the columns appear in the table, even though most people like them to be in a semi-logical order.

Re query not seeming to work - test it at the command line or via phpmyadmin, or whatever db management tool you have. Then when you have a query that does what you want, start working on the web page version.

[Pinchanzee]

The order of data in your table is totally and utterly irrelevant. Extract you data as required via a query and include an ORDER BY clause. Display it on your web page and fiddle around with the display using PHP.

No need at all to "re-order" a table. This is a basic tenet of relational database theory. And the same applies to the order that the columns appear in the table, even though most people like them to be in a semi-logical order.

Re query not seeming to work - test it at the command line or via phpmyadmin, or whatever db management tool you have. Then when you have a query that does what you want, start working on the web page version.

I had originally planned to order it via PHP, yes, but figured it's create a big server load. If lots of people are using the (hypothetical) order.php script at the same time it'd slow it down a lot, whereas I imagine simply calling it from the database would be quicker for a larger scale.
Or is there a more efficient way yet?

Ignore that, upon discussion it seems like SELECT * FROM Links ORDER BY Likes would be just as if not more efficient than making a duplicate table..

[Pinchanzee]

Having a problem with seperate coding.
I can see the problem myself - the first SELECT will be totally forgotten by the time it gets to the second SELECT - is there anyway to combine the two while keeping the id constant?

{mysql_query("SELECT Title
                                , LinkTitle
                                , Image
                                , Type
                                , Description
                                , Categories
                                , Age
                                , Rating
                                , Likes
                            FROM Links
                            ORDER BY Likes") ("SELECT * FROM Links");
    $result = mysql_query("SELECT * FROM Links");}

[drjohn]

MySQL does not execute pairs of queries like that. Full stop.