Page 1 of 1

MySQL query strange order results

Posted: Sat Oct 27, 2007 8:07 am
by mweaver999
I have the following table named forum in a MySQL database......

Field Type Null Extra

ID int(11) No auto_increment
puname varchar(10) Yes
pid int(11) Yes
forum_id int(2) Yes
thread_id int(11) Yes
thread_type varchar(20) Yes
thread_title varchar(200) Yes
thread_body text Yes
thread_date bigint(20) Yes
thread_view int(11) Yes
thread_status int(2) Yes

Keyname Unique Field
ID No ID

The problem I have is when I do any query on this table ie.. SELECT * FROM forum the results do not appear how I would expect them to. I would expect the results to be listed in ascending order according to the ID field. The overall majority or the records are listed in this order but the odd one or two appear out of sequence. I have tried using ORDER by ID ASC but found I was experiencing problems.

The same problem even happens when I do a "Browse" of this table through PHPMyAdmin. I was wondering if it has something to do with the table key? I do not completely understand about index keys etc and would be grateful for any advice.

Many Thanks

Posted: Sat Oct 27, 2007 8:53 am
by seppo0010
hi mweaver999

If no order by clause is given, MySQL will return the results in any order, not necesarily ordering by id, just the way that internal mysql server prefers.

You should be able to use "ORDER BY id ASC" in your query... what problem did you get using it?

Posted: Sat Oct 27, 2007 4:13 pm
by califdon
As seppo0010 said, relational database theory assumes no "natural" order of data rows. Every time you want to see data in a particular order, you must use the ORDER BY clause in the SQL statement. Since your table has several "id" columns, are you sure that you're using the one you think you are?

Posted: Sat Oct 27, 2007 5:58 pm
by mweaver999
Well I've tried using the ORDER by clause and now its working OK. I'm not sure what I was doing wrong before but thanks for your help