Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Show latest posts

     
4:25 pm on Jan 9, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2004
posts: 130
votes: 0


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);
?>
4:52 pm on Jan 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 8, 2003
posts:1141
votes: 0


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;
5:27 pm on Jan 9, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2004
posts: 130
votes: 0


Its not showing the latest posts
6:13 pm on Jan 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 8, 2003
posts:1141
votes: 0


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;
6:20 pm on Jan 9, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2004
posts: 130
votes: 0


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;
6:23 pm on Jan 9, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2004
posts: 130
votes: 0


$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
6:42 pm on Jan 9, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2004
posts: 130
votes: 0


$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
11:57 am on Jan 10, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2004
posts:941
votes: 0


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...]