Output database rows from the very end

[ueon]

I'm trying to duplicate a very basic very of facebook's wall. How do I output the data in my db from the most recent entries?

 

$checkfriends = mysql_query("SELECT receive, confirm FROM addfriend WHERE request=$email");
$checkfriendsreq = mysql_query("SELECT request, confirm FROM addfriend WHERE receive=$email");
$findposts = mysql_query("SELECT content, email FROM posts");


while ($posts = mysql_fetch_array($findposts) && $addpost < 10){ //Checks a post entry
$checkemail = $posts['email']; // Extracts post's email
if ($checkemail == $email){
	//Display
}
while ($requestemail = mysql_fetch_array($checkfriends)){
	if ($requestemail['receive'] == $checkemail && $requestemail['request'] == $email && $requestemail['confirm'] == "2") {
		$addpost = $addpost + 1;
		echo $posts['content'];
	}
	elseif ($requestemail['request'][
}
while ($receiveemail = mysql_fetch_array($checkfriendsreq)){
	if ($receiveemail['request'] == $checkemail && $receiveemail['recieve'] == $email && $receiveemail['confirm'] == "2") {
		$addpost = $addpost + 1;
		echo $posts['content'];
	}
}
}

[btherl]

If you store a timestamp (mysql datetime data type, I believe) for each entry, and create an index on that column, then you can do a query like this:

 

SELECT * FROM posts ORDER BY created_timestamp DESC LIMIT 10

 

The index will allow Mysql to find the entries quickly.

[ueon]

thanks, do I insert a date() into the timestamp? day of week, day of month, month, year, time - mysql should be able to order this right?

[jcbones]

Just insert the MySQL function NOW().  It will populate the timestamp column with the current time.

 

INSERT INTO table (timestamp_column) VALUES (NOW())

[TeNDoLLA]

If you have already ID field as auto_increment and primary key in the table I don't see a reason to use any other fields than that. Just use same method on the ID field as mentioned for the timestamp. Int type field ID will be faster on larger datasets and if its primary key its indexed already.

[ueon]

I actually have an auto_increment so I guess fixes the problem. but what is the primary key and creating an index on a column? are those necessary to accomplish what I'm trying to do?

[TeNDoLLA]

If the column is primary key, it is indexed automatically. If not you can use CREATE INDEX syntax to create index in the field http://dev.mysql.com/doc/refman/5.0/en/create-index.html . Index is not necessary, but it will make queries faster on larger data sets when limiting the result set (mysql does not need to read all data in whole table, just gets the asked data by index). You can use the auto_increment field instead of creating separate date field. Just use it the same way as btherl mentioned earlier and change the field.