Welcome to WebmasterWorld Guest from 54.159.250.110

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Show latest posts

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

10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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)

10+ Year Member



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

WebmasterWorld Senior Member 10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



$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)

10+ Year Member



$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)

WebmasterWorld Senior Member 10+ Year Member



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