sql problem

[Chris Mosser]

sorry to post this sql problem here, but I dont know of a MySQL group. If
it helps ease the pain, this is an excerpt from "PHP" MyAdmin

id title date file
Edit Delete 1069774255 Good Morning Tuesday 11/25/2003
a1069774255.txt
Edit Delete 1069648271 Admin news Sunday 11/23/2003 a1069648271.txt
Edit Delete 1069836400 Buon Giorno! Wednesday 11/26/2003
a1069836400.txt

Q1: Can anyone find any sort of pattern to the ordering of these rows, I
can't.

I want my entries to be pulled in the reverse order that they are entered
into the DB..ie have the newest post come first. Not a problen in itself,
except that after a few days, my order gets all jumbled. I assume it's the
DB indexing itself or something like that. I set the 'id' column to primary
key and since the entry is $id = time(); there is practically
no way for duplicate entries and each new entry should in theory be larger
in value than the next. Which is the case except the order gets jumbled.
Anyone have any insight as to why this is.

Q2: This is the 1st project that I've done where the entry order matters.
If the DB is actually acting normal then I need to rethink my design and
code. Any ideas would be greatly appreciated

--
Chris Mosser

"Chris Mosser" <cmosser_at_comcast_dot_net> wrote in message
news:87********************@comcast.com...

sorry to post this sql problem here, but I dont know of a MySQL group. If
it helps ease the pain, this is an excerpt from "PHP" MyAdmin

id title date file
Edit Delete 1069774255 Good Morning Tuesday 11/25/2003
a1069774255.txt
Edit Delete 1069648271 Admin news Sunday 11/23/2003 a1069648271.txt Edit Delete 1069836400 Buon Giorno! Wednesday 11/26/2003
a1069836400.txt

Q1: Can anyone find any sort of pattern to the ordering of these rows, I
can't.

I want my entries to be pulled in the reverse order that they are entered
into the DB..ie have the newest post come first. Not a problen in itself,
except that after a few days, my order gets all jumbled. I assume it's the DB indexing itself or something like that. I set the 'id' column to primary key and since the entry is $id = time(); there is practically no way for duplicate entries and each new entry should in theory be larger
in value than the next. Which is the case except the order gets jumbled.
Anyone have any insight as to why this is.

Q2: This is the 1st project that I've done where the entry order matters.
If the DB is actually acting normal then I need to rethink my design and
code. Any ideas would be greatly appreciated

--
Chris Mosser

If id=time() ,then it should be working - As you are probably trying
"select * from TABLE order by id desc"

If this is indeed causing problems, you may want another field. I forget
what it is called in mysql , but it is the auto increment capabiblity for a
field.
This field will be incremented by 1 for every record inserted. Then by
design, your sql should alwasy work. "select * from TABLE order by
auto_increment_field_name desc

BTW, there is also a group called alt.php.sql

[Chris Mosser]

>

If id=time() ,then it should be working - As you are probably trying
"select * from TABLE order by id desc"

Actually, I feel really stupid now, the second I sent the post in, the word
"ORDER" was stuck in my head. Then it hit me, I changed my query from
"SELECT * from table" to "SELECT * from table ORDER BY id DESC".
Everything is all gravy now.

I would still like to know why the order gets messed up, but at least I
found a work around for it.

thnx
--
Chris Mosser

[Pedro Graca]

Chris Mosser wrote:

Q2: This is the 1st project that I've done where the entry order matters.
If the DB is actually acting normal then I need to rethink my design and
code. Any ideas would be greatly appreciated

ANSI SQL does not assume a 'default' sort order (and MySQL doesn't
either).
If you want your records sorted somehow, specify the sort order in the
select query; without it records will be returned the way the database
manager thinks is fastest (and that may be different between two calls
of the same sql command, even if the database didn't change!).

select <columns> from <tables> where <condition> order by <whatever>
________________________________________________ ^^^^^^^^^^^^^^^^^^^
HTH
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Geoff Berrow]

I noticed that Message-ID: <a5********************@comcast.com> from
Chris Mosser contained the following:

I would still like to know why the order gets messed up, but at least I
found a work around for it.

There is no order to get messed up. The order that you entered the
records only exists if you specify that order in your query as you have
found out. But you could equally order the records alphabetically on a
text field, ascending or descending. In fact you can order the records
any number of ways.

It's simplest just to remember that there is no order, unless you
specify it.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Chris Mosser]

"Pedro Graca" <he****@hotpop.com> wrote in message
news:br************@ID-203069.news.uni-berlin.de...

Chris Mosser wrote:

Q2: This is the 1st project that I've done where the entry order matters. If the DB is actually acting normal then I need to rethink my design and
code. Any ideas would be greatly appreciated

ANSI SQL does not assume a 'default' sort order (and MySQL doesn't
either).
If you want your records sorted somehow, specify the sort order in the
select query; without it records will be returned the way the database
manager thinks is fastest (and that may be different between two calls
of the same sql command, even if the database didn't change!).

select <columns> from <tables> where <condition> order by <whatever>
________________________________________________ ^^^^^^^^^^^^^^^^^^^

cool..thnx for the info guys
--
Chris Mosser