ORDER BY help in query

[electricocean]

Hi,

When text is inserted into my database, it always adds it on the very bottom of the table.

How would I do a query with ORDER BY that selects the bottom to the top?

OR---- How would I make everything thats inserted into the database go on top instead of the bottom?

thanks,
electricocean

[fischermx]

How would I do a query with ORDER BY that selects the bottom to the top?

Order By MyField Desc, I'm assuming somehow you're seeing your table like ordered in ascending order for a given attribute.

How would I make everything thats inserted into the database go on top instead of the bottom?

If I'd were a relational purist, you'd be bleeding by now, but I'm not, so you're lucky. :)
There's no such sense of "insertion order" in a relational database as MySql.
You always have to order your output using the "Order By" clause.

Sometimes, though this is not a rule, if an index is present during the fetching and records are added to the resultset during a navigation on that index, the results might seems "ordered" by the order in that index. This is specially true when the particular index is a clustered index.
But you should never relay in a casual ordering like this, instead use the proper "Order By" clause for all your queries.

[electricocean]

I am not quite sure what you mean.

say my table is named x and I have 2 feilds, test and real.

what is the attribute? is that what you mean...? (ORDER BY x Desc)

I just got this mysql book and think it wsas using attributes and I didn't understand. in the book it said ORDER BY expense_date--- is that an attribute?

thanks,

electricocean

[mcibor]

To show the last insertion first it is best to use id.

Your table will look like this:
CREATE TABLE bla (id INT NOT NULL auto_increment, test VARCHAR(21), real VARCHAR(21), PRIMARY KEY(id));

And your query will be such:
SELECT test, real FROM bla ORDER BY id DESC; (desc means descending - highest is first)

To insert into such table:
INSERT INTO bla(test, real) VALUES('This is test', 'This is real');

Hope this helps
Michal Cibor

[fischermx]

Depending on the context, you may find these terms are used interchangable :

ATTRIBUTE = COLUMN = FIELD

TUPLE = ROW = RECORD

[electricocean]

If I used this query:

$query = "SELECT date, news FROM x ORDER BY date DESC LIMIT 10";

I want it to pull a lot... not just one piece of news which it is doing now.

I want it to pull out ten entries so i thought LIMIT 10, but that didn't work.

thanks,
electricocean

[electricocean]

Hi, I realized partly why it's not working is becuase of my mysql_result().

$date = mysql_result($result, 0, "date");
$news = mysql_result($result, 0, "news");

this only pulls one piece of news up from the bottom and chges if I change 0 to any number(every one row higher if changed)... How can I fix this?

thanks,

electricocean

[Stormfx]

mysql_result() only pulls one column from one row. If you're trying to get 10 news items from the database, use your select statement above and do something like this:

while ($row = mysql_fetch_assoc($result)) {
echo $row['date'].'<br />'.$row['news']."<br /><br />\n"
}

[electricocean]

Thats works great... thanks-

will the LIMIT 10 of the query take care of only ten posts? cuz the while($row=.....) seems to out put everything... thats better than nothing though.

thanks,
electricocean

[mcibor]

You can not only specify how many rows the function will return, but also from which answer to start:

SELECT * FROM table LIMIT 0, 10; 0 is the offset (from which row to start) and 10 is how many rows to display.
Then just use
while($row = mysql_fetch_array($result)){} and you have it

Best regards
Michal Cibor

[Stormfx]

Yes, setting the LIMIT 10 in the query will literally limit the amount of rows returned to 10. The your while(..){} statement will only output 10 rows.