Organizing mySQL data

[DifuzhanDude]

I am working on an administration page for some of my sites. I have written a script that works similar to a guestbook but only certain people can access the submit page.

My knowledge of PHP and mySQL is very basic and when I access the viewing page, the most recent entry is on the bottom. Is there a way to flip this around so that the most recent ones are at the top? I am guessing that the easiest way would be to auto_increment the entries and sort them in descending order but I have no idea how i would do this.

I was also wondering if there is an easy way to take a row and move it up or down on the table so that it would be in a different order. Basically I want everything to be chronoligical but the entries are not always submitted that way, so I want a way to be able to sort those.

If anyone has any ideas, please help me. Thanks,
Josh

[DifuzhanDude]

just to let everyone know... i figured out the first part of my question. I set an "id" column and had it auto_increment each time.

I changed my PHP code to this:

$result = mysql_query("SELECT * FROM myTable ORDER BY id DESC")
or die (mysql_error());

I hope this can help anyone out that needs the same thing. I still do not know how to sort the rows though so I would appreciate some help on that still...

Thanks
Josh

[PhilG]

It looks as though you have the first question sorted (exuse the pun!)!

Anyway I didnt fully understand your second question. From what I read you want to sort your data chronoligically and to do this you could order by the title/name of your data

For example if you have a table with:

id,name,timestamp

You could have a query to list the data chronoligically and then if they chronoligical value is the same order by the timestamp

$result = mysql_query("SELECT * FROM myTable ORDER BY name ASC, timestamp DESC")
or die (mysql_error());

I hope that helps

[DifuzhanDude]

sorry... i guess i'm just not explaining it very good... but here's an example...

lets say i design a page for a band. i want to setup a page where someone can add shows to a list. they add all the shows and they're in a certain order but then they book another show that's before the other ones and when they add it, it shows up on the top.

i want to write a script so that they can change the order and move the shows up or down on the list depending on when it was.

i hope that makes more sense... thanks for the help

Josh

[PhilG]

Hi Josh,

I now know what you mean. i have also looked at your site and think that its pretty easy to do what you need to do.

I would create a table(shows) with:

id - int(10)
date - int(16)
time - varchar(255)
place - varchar(255)
lineup - text()
cost - float()

When adding a show i would constuct an html form that adds the following:

$date = mktime (0,0,0,6,6,2003);
$time = "Doors open at 8PM";
$place = "The Door Fort Worth";
$lineup = "Drain, Headliner, Difuzhan, etc";
$cost = "10.00";

$result = mysql_query("INSERT INTO shows (date, time, place, lineup, cost) VALUES ('" . $date . "', '" . $time . "', '" . $place . "', '" . $lineup . "', '" . $cost . "')")
or die (mysql_error());

Then when you want to output simply use:


$result = mysql_query("SELECT * FROM shows ORDER BY date ASC")
or die (mysql_error());

[DifuzhanDude]

ok cool... I was manually putting in the date because I didn't know the format used. that will help out a lot!

I was also trying to figure out a way to move the rows still, if I ever needed to and I thought about just taking the "id"s of the rows I want to switch and changing the numbers. would that be a good way of doing that?

but thanks for you help, it's something I can definately use!

[yager]

i think when we partition the data it is done only logically

not physically