Show latest posts

[aff_dan]

Dear Sir, I have this script who show the first posts, but I want latest post, how to change this script?

<?php
include('dbconnect.php');
$totalrows = 10;
$rr = mysql_query("SELECT count( * ) as total_record FROM posts") or die(mysql_error());
while ($row = mysql_fetch_object($rr)) { 
$count = $row->total_record;
if(!isset($_GET['p'])) { $_GET['p'] = ""; }
if($_GET['p'] == "") { $page = 1; } else { $page = $_GET['p']; }
if($_GET['p'] == 0) { $page = 1; }
if($_GET['p'] > ($count / $totalrows)) { $page = 1; }

if($page == 1) { $pstart = 0; } else { $pstart = $page * $totalrows; }
$pend = $pstart + $totalrows; 


$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER by posts.Id AND posts.Id ASC LIMIT " . $totalrows;
//echo $sql;
$r = mysql_query($sql) or die(mysql_error());

}
$count = intval($count / $totalrows);
?>

[jecasc]

Try switching from ascending to descending order by changing ASC to DESC in your sql statement:


$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER by posts.Id AND posts.Id DESC LIMIT " . $totalrows;

[aff_dan]

Its not showing the latest posts

[jecasc]

I don't quite understand the reason for this in you sql statment: ORDER by posts.Id AND posts.Id ASC.

Perhaps it will work if you try this:


$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER BY posts.Id DESC LIMIT " . $totalrows;

or like this:

$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER by posts.Id DESC AND posts.Id DESC LIMIT " . $totalrows;

[aff_dan]

Its works! Thank you.

This SQL takes 25 seconds to open it, it is showing 10 posts every time when you open the index.php, the problem is its taking 25 seconds to be show different posts. What could I change?

$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Title != '' order by rand() asc limit " . $totalrows;

[aff_dan]

$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER by posts.Id DESC AND posts.Id DESC LIMIT " . $totalrows;

give error but using the first advice is showing at very speed posts

[aff_dan]

$sql = "SELECT posts.Tags as tags, posts.OwnerUserId as postsid, posts.Id as postid, posts.Body as body, posts.Title as title, users.Id as userid, users.DisplayName as usersname FROM posts JOIN users ON posts.OwnerUserId = users.Id WHERE posts.Id >= " . $pstart . " AND posts.Title != '' ORDER by posts.Id DESC AND posts.Id DESC LIMIT " . $totalrows;

give this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND posts.Id DESC LIMIT 10' at line 1

[omoutop]

I think your Order BY clause is wrong.
Change the "AND" with "," like:
ORDER by posts.Id DESC , posts.Id DESC LIMIT ". $totalrows;

More info here: [dev.mysql.com...]