homepage Welcome to WebmasterWorld Guest from 54.167.130.74
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Show latest posts
aff_dan

10+ Year Member



 
Msg#: 4250985 posted 4:25 pm on Jan 9, 2011 (gmt 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);
?>

 

jecasc

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4250985 posted 4:52 pm on Jan 9, 2011 (gmt 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;

aff_dan

10+ Year Member



 
Msg#: 4250985 posted 5:27 pm on Jan 9, 2011 (gmt 0)

Its not showing the latest posts

jecasc

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4250985 posted 6:13 pm on Jan 9, 2011 (gmt 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;

aff_dan

10+ Year Member



 
Msg#: 4250985 posted 6:20 pm on Jan 9, 2011 (gmt 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;

aff_dan

10+ Year Member



 
Msg#: 4250985 posted 6:23 pm on Jan 9, 2011 (gmt 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

aff_dan

10+ Year Member



 
Msg#: 4250985 posted 6:42 pm on Jan 9, 2011 (gmt 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

omoutop

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4250985 posted 11:57 am on Jan 10, 2011 (gmt 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...]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved