Paging and query string for individual post MsSQL & PHP

Question

I have written a paging script to page all the posts in my blog. Now I would like to create and endpoint such that I can go to mydomain/index.php?blogID1, mydomain/index.php?blogID2, mydomain/index.php?blogID3, mydomain/index.php?blogID4, etc.

Where can I read about doing this, and how do I implement it the easiest way possible, given my current script:

<?php
        $rowsPerPage = 2;
        try
        {
            $conn = new PDO( "sqlsrv:server=localhost ; Database=blog", "******", "*******");
            $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        }
        catch(Exception $e)
        { 
            die( print_r( $e->getMessage() ) ); 
        }

        try
        {
            $tsql = "SELECT COUNT(blogID) FROM blog_posts";
            $stmt = $conn->query($tsql);
            $rowsReturned = $stmt->fetch(PDO::FETCH_NUM);
            if($rowsReturned[0] == 0)
            {
                echo "No rows returned.";
            }
            else
            {     
                $numOfPages = ceil($rowsReturned[0]/$rowsPerPage);  
                for($i = 1; $i<=$numOfPages; $i++)     
                {
                    $pageNum = "index.php?pageNum=$i";
                    print("<a href='$pageNum' class='btn btn-primary active btn-sm'>$i</a>&nbsp;&nbsp;");
                }
            }
            $tsql = "SELECT * FROM 
            (SELECT ROW_NUMBER() OVER(ORDER BY blogID DESC) 
               AS RowNumber,
               blog_title,    
               blog_post,
               blog_author,
               blog_category,
               blog_date,
               blogID
               FROM blog_posts)
AS Temp
WHERE RowNumber BETWEEN ? AND ?";
$stmt2 = $conn->prepare($tsql);
if(isset($_GET['pageNum']))
{
$highRowNum = $_GET['pageNum'] * $rowsPerPage;
$lowRowNum = $highRowNum - $rowsPerPage + 1;
}
else
{
$lowRowNum = 1; 
$highRowNum = $rowsPerPage; 
}
$params = array(&$lowRowNum, &$highRowNum);
$stmt2->execute(array($lowRowNum, $highRowNum));
while($row = $stmt2->fetch(PDO::FETCH_NUM) )
{
echo   "$row[1]"; 
echo   "$row[2]";
echo   "$row[3]"; 
echo   date_format( new DateTime($row['5']), 'd M Y, H:i' ); 
echo  "$row[4]"; 
echo  "$row[6]";

}
}
catch(Exception $e)
{ 
die( print_r( $e->getMessage() ) ); 
}
?>

It would help if you did some Googling and tried something. SO is more for fixing issues, not providing solutions. — Aleksander Lidtke, Mar 03 '16 at 18:08
Hi! Welcome to StackOverflow! Unfortunately, your question reads very much like a "please implement x feature" request, which is not a good fit for StackOverflow. Please refer to this page to learn how to write super-effective questions! http://www.stackoverflow.com/help/mcve — Nathaniel Ford, Mar 03 '16 at 18:15
@AleksanderLidtke i have done alot of googeling, but i dont know where to start... I dont want anyone to write this for me, i just want some points in the right direction / some tips as i dont know anyone who even can write basic html code i figured this is my best choice in getting some decent pointers — per källström, Mar 03 '16 at 18:52

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

Is row a post? You can order your rows by any column, then limit the number of results and set an offset depending which page was requested. Here's about mysql that endpoint: MySQL Data - Best way to implement paging?

For example if page is requested like this: index.php?pageNum=15 and you are showing 5 posts per page, Grab that value with $page = $_GET['pageNum'] and set your PDO prepared statements for result number and offset (LIMIT).

Declare $resultsPerPage = 5

SELECT * 
FROM Posts
LIMIT :offset, :maxResults

And push in those prepared statements:

offset --> ($page - 1) * $resultsPerPage
maxResults --> $resultsPerPage

edited May 23 '17 at 12:07

Community

1
1

answered Mar 03 '16 at 18:30

Bramastic

399
4
16

Okey, however does that apply to Microsoft sql aswell? The paging part is working as intended, i just want to add so that i can go to each individual post with a url like mydomain/index.php?blogID1. the $row[1] - [6] are a post basically, where $row[1] contains the unique id for the posts – per källström Mar 03 '16 at 18:51
1

To get a n individual post, use a different GET key. Like postID `index.php?postID=83`. Then you can check in your index.php script if you have pageNum or postID set (like this: `isset($_GET['postID'])` ) and if it is, show only a single post. – Bramastic Mar 03 '16 at 20:11
I'm sorry. Limit doesn't work for ms sql but you got it right as I see. Just use those variables in a php script and create anothery query where you'll pass a requested post ID. – Bramastic Mar 03 '16 at 20:17
Thanks for the help, i appreciate it :) – per källström Mar 03 '16 at 20:26
No problem! If this helped to solve your problem, please mark it as an answer. Thank you! – Bramastic Mar 03 '16 at 20:27
It will eventually solve my problem im sure of, just gotta figure all out now when you have pointed me in the right direction :) – per källström Mar 03 '16 at 20:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/105307/discussion-between-per-kallstrom-and-bramastic). – per källström Mar 03 '16 at 20:47

Paging and query string for individual post MsSQL & PHP

1 Answers1