By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

sql problem

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"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


Jul 17 '05 #2

P: n/a
>
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
Jul 17 '05 #3

P: n/a
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 =--
Jul 17 '05 #4

P: n/a
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/
Jul 17 '05 #5

P: n/a

"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
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.