sql problem

**Guest** · Jul 17 '05, 01:40 AM

Re: sql problem

"Chris Mosser" <cmosser_at_com cast_dot_net> wrote in message
news:87KdnQ3qYu-1eECiRVn-uw@comcast.com. ..[color=blue]
> 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[/color]
a1069648271.txt[color=blue]
> 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[/color]
the[color=blue]
> DB indexing itself or something like that. I set the 'id' column to[/color]
primary[color=blue]
> key and since the entry is $id = time(); there is[/color]
practically[color=blue]
> 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
>
>[/color]

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** · Jul 17 '05, 01:40 AM

Re: sql problem

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

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** · Jul 17 '05, 01:40 AM

Re: sql problem

Chris Mosser wrote:[color=blue]
> 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[/color]

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** · Jul 17 '05, 01:40 AM

Re: sql problem

I noticed that Message-ID: <a5KdnUk7IfCGd0 CiRVn-jA@comcast.com> from
Chris Mosser contained the following:
[color=blue]
>I would still like to know why the order gets messed up, but at least I
>found a work around for it.[/color]

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** · Jul 17 '05, 01:40 AM

Re: sql problem

"Pedro Graca" <hexkid@hotpop. com> wrote in message
news:brkqa9$4f1 2j$1@ID-203069.news.uni-berlin.de...[color=blue]
> Chris Mosser wrote:[color=green]
> > Q2: This is the 1st project that I've done where the entry order[/color][/color]
matters.[color=blue][color=green]
> > If the DB is actually acting normal then I need to rethink my design and
> > code. Any ideas would be greatly appreciated[/color]
>
> 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>
> _______________ _______________ _______________ ___ ^^^^^^^^^^^^^^^ ^^^^[/color]

cool..thnx for the info guys

--
Chris Mosser

sql problem

sql problem

Comment

Comment

Comment

Comment

Comment