Really Struggling to Paginate Results MSSQL/PHP/PDO

I’m trying to create a pagination script using a MSSQL database. I think I’m half way there, but finding limited resources to help get it finished up.

So far, I can output the limited results and set the page numbers correctly. However, the next pages contain the same data.

If somebody could take a look at this for me and let me know where I’m going wrong, that would be great.

<?php
$sth = $dbconn->prepare ("SELECT     TOP (5) a.id, a.title, a.text, a.country, a.city, a.cat, a.user_id, a.use_ad, a.viewcount, a.tags, a.status, a.mod_status, publication_issue.img,
                      publication_issue.publication_id, publication_issue.issue_number, publication_issue.upload_date, publication_issue.cat AS Expr1,
                      publication_issue.viewcount AS Expr2, publication_issue.status AS Expr3, publication_issue.mod_status AS Expr4, publication_issue.user_id AS Expr5
FROM         (SELECT     TOP (75) id, title, text, country, city, cat, user_id, use_ad, viewcount, tags, status, mod_status
                       FROM          publication
                       ORDER BY id) AS a INNER JOIN
                      publication_issue ON a.id = publication_issue.publication_id
ORDER BY a.id DESC");

$sth->execute();

while ($results = $sth->fetchAll(PDO::FETCH_ASSOC)){
//use $results
    $pages = ceil(count($results)/4);
    $page  = (isset ($_GET['page']))  ? (int) $_GET['page'] : 1 ;
    $start = ($page - 1) *  $per_page;



foreach ($results as $row){

echo "title: ".$row['title']."<br />";

}




}
    ?>
    <br><br>
    <?php
    if ($pages >=  1 && $page <= $pages){
    //if ($page>1 && $page <= $pages){$previous=($page -1); echo '<a href="?page=' .$previous. '">Previous</a>';}
    for($x=1; $x<=$pages; $x++){ echo ($x == $page) ? ' <strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : ' <a href="?page='.$x.'">'.$x.'</a> ';}
    //if ($page>1  && $page <= $pages){ $next=($page+1) ; echo '<a href="?page=' .$next. '">Next</a>';}
    }






?>

Okay, the problem is your query. You are using TOP(5) to limit your results, and that is not the appropriate way when working with paging. Personally I think Microsoft just hates all of us for not providing a nice way like MySQL did…

So Let’s start by redoing your query:
The trick is to use OVER (which is also referred to as PARTITION BY)
This process lets you specify a specific order for your results to be “numbered”, thus allowing you to eventually ask for rows 1-5 or 6-10, etc.

I also like to use a CTE (Common Table Expression) for this as it helps organize your query, but you don’t have to use one.

So with a CTE:


$query = <<<QUERY
   WITH LimitPublications AS (
      SELECT
        id
        , title
        , text
        , country
        , city
        , cat
        , user_id
        , use_ad
        , viewcount
        , tags
        , status
        , mod_status
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'
      FROM publication
   )
   SELECT
     a.id
     , a.title
     , a.text
     , a.country
     , a.city
     , a.cat
     , a.user_id
     , a.use_ad
     , a.viewcount
     , a.tags
     , a.status
     , a.mod_status
     , publication_issue.img
     , publication_issue.publication_id
     , publication_issue.issue_number
     , publication_issue.upload_date
     , publication_issue.cat AS Expr1
     , publication_issue.viewcount AS Expr2
     , publication_issue.status AS Expr3
     , publication_issue.mod_status AS Expr4
     , publication_issue.user_id AS Expr5
   FROM LimitPublications AS a
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id
   WHERE a.RowNumber BETWEEN 0 AND 5
QUERY;
$sth = $dbconn->prepare ($query);

Without a CTE:


$query = <<<QUERY
   SELECT
     a.id
     , a.title
     , a.text
     , a.country
     , a.city
     , a.cat
     , a.user_id
     , a.use_ad
     , a.viewcount
     , a.tags
     , a.status
     , a.mod_status
     , publication_issue.img
     , publication_issue.publication_id
     , publication_issue.issue_number
     , publication_issue.upload_date
     , publication_issue.cat AS Expr1
     , publication_issue.viewcount AS Expr2
     , publication_issue.status AS Expr3
     , publication_issue.mod_status AS Expr4
     , publication_issue.user_id AS Expr5
   FROM (
      SELECT
        id
        , title
        , text
        , country
        , city
        , cat
        , user_id
        , use_ad
        , viewcount
        , tags
        , status
        , mod_status
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'
      FROM publication
   ) AS a
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id
   WHERE a.RowNumber BETWEEN 0 AND 5
QUERY;
$sth = $dbconn->prepare ($query);

In both examples I used the HEREDOC syntax to assign the query to a variable so it is easy to read. If you want to bunch them up on one line, feel free.

Also, you will need a separate query to get the number of records stored in your table so you can determine the number of pages you need, a simple “SELECT COUNT(*) AS TotalRows FROM publication” is perfect for that (granted if you wanted to be “clever” you could return MAX(a.RowNumber) on every record from the queries above and that will give it to you too).

Hopefully this gets you past the brick wall and towards what you want to see being returned/shown.

Thanks for any help you give me, I’ve been posting around a few forums and help is quite hard to come by when it comes to MSSQL.

I built the above query in the Visual Studio query builder, and got an output, but trying to return the values in PHP does nothing.

How does this look?

<?php
 
$query = $dbconn->prepare("
   SELECT  
     a.id 
     , a.title 
     , a.text 
     , a.country 
     , a.city 
     , a.cat 
     , a.user_id 
     , a.use_ad 
     , a.viewcount 
     , a.tags 
     , a.status 
     , a.mod_status 
     , publication_issue.img 
     , publication_issue.publication_id 
     , publication_issue.issue_number 
     , publication_issue.upload_date 
     , publication_issue.cat AS Expr1 
     , publication_issue.viewcount AS Expr2 
     , publication_issue.status AS Expr3 
     , publication_issue.mod_status AS Expr4 
     , publication_issue.user_id AS Expr5 
   FROM ( 
      SELECT  
        id 
        , title 
        , text 
        , country 
        , city 
        , cat 
        , user_id 
        , use_ad 
        , viewcount 
        , tags 
        , status 
        , mod_status 
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'  
      FROM publication 
   ) AS a 
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id 
   WHERE a.RowNumber BETWEEN 0 AND 5 
");

//It fails here
 while ($row = $query->fetch(PDO::FETCH_ASSOC)) {


$Num_Rows = count($row);


 
//Set limit per page
 
$Per_Page = 9;  // Per Page
 
//Get the page number
 
$Page = $_GET["Page"];
 
//Determine if it is the first page
 
if(!$_GET["Page"])
{
    $Page=1;
}
 
//Declare previous/next page row guide
 
$Prev_Page = $Page-1;
$Next_Page = $Page+1;
 
//Determine page start
 
$Page_Start = (($Per_Page*$Page)-$Per_Page);
if($Num_Rows<=$Per_Page)
{
    $Num_Pages =1;
}
else if(($Num_Rows % $Per_Page)==0)
{
    $Num_Pages =($Num_Rows/$Per_Page) ;
}
else
{
    $Num_Pages =($Num_Rows/$Per_Page)+1;
    $Num_Pages = (int)$Num_Pages;
}
 
//Determine where the page will end
 
$Page_End = $Per_Page * $Page;
IF ($Page_End > $Num_Rows)
{
    $Page_End = $Num_Rows;
}
 
?>
<ul style="">
 
<?
 
//Make it show next rows
 
for($i=$Page_Start;$i<$Page_End;$i++)
{
?>
 
  <li><?php echo $row['title'];?></li>
 
 
 
<?
}
 
echo '
</ul>
';
 
//Previous page
 
if($Prev_Page)
{
    echo " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> ";
}
 
//Display total pages
 
for($i=1; $i<=$Num_Pages; $i++){
    if($i != $Page)
    {
        echo "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;";
    }
    else
    {
        echo "<b> $i </b>";
    }
}
 
//Create next page link
 
if($Page!=$Num_Pages)
{
    echo " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> ";
}
 
//Adios
 }
$sth = null;
?>

Okay, I might be crazy here (as I’m still a bit new to PDO myself), but I believe you have to execute your prepared statement before you can fetch from it.

However, before I get to that, there is a lot of re-working of your code that needs to happen, you have too much mixed into the while loop and you’ll run into problems that way.

Okay so for the code:

<?php 
$Per_Page = 9;  // Per Page 
  
//Get the page number 
  
$Page = 1;
  
//Determine if it is the first page 
  
if(isset($_GET["Page"]))
{ 
    $Page=(int)$_GET["Page"]; 
		if ($Page < 1)
			$Page = 1;
} 

$Page_Start = (($Per_Page*$Page)-$Per_Page); 
  
$query = $dbconn->prepare(" 
   SELECT   
     a.id  
     , a.title  
     , a.text  
     , a.country  
     , a.city  
     , a.cat  
     , a.user_id  
     , a.use_ad  
     , a.viewcount  
     , a.tags  
     , a.status  
     , a.mod_status  
     , publication_issue.img  
     , publication_issue.publication_id  
     , publication_issue.issue_number  
     , publication_issue.upload_date  
     , publication_issue.cat AS Expr1  
     , publication_issue.viewcount AS Expr2  
     , publication_issue.status AS Expr3  
     , publication_issue.mod_status AS Expr4  
     , publication_issue.user_id AS Expr5  
		 , MAX(id) AS 'totalRecords'
   FROM (  
      SELECT   
        id  
        , title  
        , text  
        , country  
        , city  
        , cat  
        , user_id  
        , use_ad  
        , viewcount  
        , tags  
        , status  
        , mod_status  
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM publication  
   ) AS a  
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
   WHERE a.RowNumber BETWEEN ? AND ?  
"); 

$totalRecords = 0;
$query->execute(array($Page_Start, $Page_Start + $Per_Page)); // more on this later
?>
<ul style=""> 
<?php
while ($row = $query->fetch(PDO::FETCH_ASSOC)) { 
	$totalRecords = $row['totalRecords'];
?>
  <li><?php echo $row['title'];?></li> 
<?php
}
?>
</ul>
<?php
$Num_Rows = $totalRecords; 
  
//Declare previous/next page row guide 
  
$Prev_Page = $Page-1; 
$Next_Page = $Page+1;
  
if($Num_Rows<=$Per_Page) 
{ 
    $Num_Pages =1; 
} 
else if(($Num_Rows % $Per_Page)==0) 
{ 
    $Num_Pages =($Num_Rows/$Per_Page) ; 
} 
else 
{ 
    $Num_Pages =($Num_Rows/$Per_Page)+1; 
    $Num_Pages = (int)$Num_Pages; 
} 
  
//Determine where the page will end 
  
$Page_End = $Per_Page * $Page; 
IF ($Page_End > $Num_Rows) 
{ 
    $Page_End = $Num_Rows; 
} 
    
//Previous page 
  
if($Prev_Page) 
{ 
    echo " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "; 
} 
  
//Display total pages 
  
for($i=1; $i<=$Num_Pages; $i++){ 
    if($i != $Page) 
    { 
        echo "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"; 
    } 
    else 
    { 
        echo "<b> $i </b>"; 
    } 
} 
  
//Create next page link 
  
if($Page!=$Num_Pages) 
{ 
    echo " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "; 
} 
  
//Adios
$sth = null; 
?>

You’ll quickly notice the first thing I did was move some of your logic to the top of your page, the $Per_Page, $Page, and $Page_Start are going to be important to your query.

I also validated the $_GET[‘Page’] variable, to ensure we end up with a number we want (not 0, not -1, etc).

I then updated the query and replaced 0 and 5 with ? and ? (placeholders). Next I added an execute statement passing the $Page_Start and the ($Page_Start + $Per_Page) so we are grabbing the records for the page we want. Those two values will replace the ? and ? I put in the query.

Next we loop through the records and store the totalRecords column into $totalRecords (which will be used later – oh, I added that column) and we echo the title from our query for each record in a List.

Following the output, we store $totalRecords into $Num_Rows and continue on with your paging output (much of which I left unchanged).

So give it a shot now and see if you get any output.

Thanks again, really appreciated.

The script fails at the while loop, can’t even echo out…

Okay, I altered the prepared statement to have array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) and I altered the fetch to contain PDO::FETCH_ORI_NEXT

Not sure if either of those are the issue though… otherwise, you may need to get to your error log and see what error you are receiving.

<?php 
ini_set('display_errors',1); // try turning on errors
$Per_Page = 9;  // Per Page 
  
//Get the page number 
  
$Page = 1;
  
//Determine if it is the first page 
  
if(isset($_GET["Page"]))
{ 
    $Page=(int)$_GET["Page"]; 
        if ($Page < 1)
            $Page = 1;
} 

$Page_Start = (($Per_Page*$Page)-$Per_Page); 
  
$query = $dbconn->prepare(" 
   SELECT   
     a.id  
     , a.title  
     , a.text  
     , a.country  
     , a.city  
     , a.cat  
     , a.user_id  
     , a.use_ad  
     , a.viewcount  
     , a.tags  
     , a.status  
     , a.mod_status  
     , publication_issue.img  
     , publication_issue.publication_id  
     , publication_issue.issue_number  
     , publication_issue.upload_date  
     , publication_issue.cat AS Expr1  
     , publication_issue.viewcount AS Expr2  
     , publication_issue.status AS Expr3  
     , publication_issue.mod_status AS Expr4  
     , publication_issue.user_id AS Expr5  
         , MAX(id) AS 'totalRecords'
   FROM (  
      SELECT   
        id  
        , title  
        , text  
        , country  
        , city  
        , cat  
        , user_id  
        , use_ad  
        , viewcount  
        , tags  
        , status  
        , mod_status  
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM publication  
   ) AS a  
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
   WHERE a.RowNumber BETWEEN ? AND ?  
", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

$totalRecords = 0;
$query->execute(array($Page_Start, $Page_Start + $Per_Page)); // more on this later
?>
<ul style=""> 
<?php
while ($row = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { 
    $totalRecords = $row['totalRecords'];
?>
  <li><?php echo $row['title'];?></li> 
<?php
}
?>
</ul>
<?php
$Num_Rows = $totalRecords; 
  
//Declare previous/next page row guide 
  
$Prev_Page = $Page-1; 
$Next_Page = $Page+1;
  
if($Num_Rows<=$Per_Page) 
{ 
    $Num_Pages =1; 
} 
else if(($Num_Rows % $Per_Page)==0) 
{ 
    $Num_Pages =($Num_Rows/$Per_Page) ; 
} 
else 
{ 
    $Num_Pages =($Num_Rows/$Per_Page)+1; 
    $Num_Pages = (int)$Num_Pages; 
} 
  
//Determine where the page will end 
  
$Page_End = $Per_Page * $Page; 
IF ($Page_End > $Num_Rows) 
{ 
    $Page_End = $Num_Rows; 
} 
    
//Previous page 
  
if($Prev_Page) 
{ 
    echo " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "; 
} 
  
//Display total pages 
  
for($i=1; $i<=$Num_Pages; $i++){ 
    if($i != $Page) 
    { 
        echo "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"; 
    } 
    else 
    { 
        echo "<b> $i </b>"; 
    } 
} 
  
//Create next page link 
  
if($Page!=$Num_Pages) 
{ 
    echo " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "; 
} 
  
//Adios
$sth = null; 
?>

Still no luck, no errors either.

If it helps, in the VB Query Builder I get an error:

Ambiguous column name ‘id’

Ah, yes, change , MAX(id) AS ‘totalRecords’ to be , MAX(a.id) AS ‘totalRecords’

Thanks, now receiving another error in the query:

Column a.id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Okay, let’s simplify it a bit and do two queries to get the Total Records and the Limited Number of Records

<?php 
ini_set('display_errors',1); // try turning on errors
$Per_Page = 9;  // Per Page 
  
//Get the page number 
  
$Page = 1;
  
//Determine if it is the first page 
  
if(isset($_GET["Page"]))
{ 
    $Page=(int)$_GET["Page"]; 
        if ($Page < 1)
            $Page = 1;
} 

$Page_Start = (($Per_Page*$Page)-$Per_Page); 
  
$query = $dbconn->prepare(" 
   SELECT   
     a.id  
     , a.title  
     , a.text  
     , a.country  
     , a.city  
     , a.cat  
     , a.user_id  
     , a.use_ad  
     , a.viewcount  
     , a.tags  
     , a.status  
     , a.mod_status  
     , publication_issue.img  
     , publication_issue.publication_id  
     , publication_issue.issue_number  
     , publication_issue.upload_date  
     , publication_issue.cat AS Expr1  
     , publication_issue.viewcount AS Expr2  
     , publication_issue.status AS Expr3  
     , publication_issue.mod_status AS Expr4  
     , publication_issue.user_id AS Expr5  
   FROM (  
      SELECT   
        id  
        , title  
        , text  
        , country  
        , city  
        , cat  
        , user_id  
        , use_ad  
        , viewcount  
        , tags  
        , status  
        , mod_status  
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM publication  
   ) AS a  
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
   WHERE a.RowNumber BETWEEN ? AND ?  
", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

$query->execute(array($Page_Start, $Page_Start + $Per_Page)); // more on this later
?>
<ul style=""> 
<?php
while ($row = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { 
    $totalRecords = $row['totalRecords'];
?>
  <li><?php echo $row['title'];?></li> 
<?php
}
?>
</ul>
<?php
$query = $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication");
$query->execute();
$totalRecords = $query->fetch(PDO::FETCH_ASSOC);

$Num_Rows = $totalRecords["TotalRecords"]; 
  
//Declare previous/next page row guide 
  
$Prev_Page = $Page-1; 
$Next_Page = $Page+1;
  
if($Num_Rows<=$Per_Page) 
{ 
    $Num_Pages =1; 
} 
else if(($Num_Rows % $Per_Page)==0) 
{ 
    $Num_Pages =($Num_Rows/$Per_Page) ; 
} 
else 
{ 
    $Num_Pages =($Num_Rows/$Per_Page)+1; 
    $Num_Pages = (int)$Num_Pages; 
} 
  
//Determine where the page will end 
  
$Page_End = $Per_Page * $Page; 
IF ($Page_End > $Num_Rows) 
{ 
    $Page_End = $Num_Rows; 
} 
    
//Previous page 
  
if($Prev_Page) 
{ 
    echo " <a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Prev_Page#related'><< Back</a> "; 
} 
  
//Display total pages 
  
for($i=1; $i<=$Num_Pages; $i++){ 
    if($i != $Page) 
    { 
        echo "<a href='$_SERVER[SCRIPT_NAME]?id=$id&Page=$i#related'>$i</a>&nbsp;"; 
    } 
    else 
    { 
        echo "<b> $i </b>"; 
    } 
} 
  
//Create next page link 
  
if($Page!=$Num_Pages) 
{ 
    echo " <a href ='$_SERVER[SCRIPT_NAME]?id=$id&Page=$Next_Page#related'>Next>></a> "; 
} 
  
//Adios
$sth = null; 
?>

Right, I think we’re making progress.

We now have the results displaying.

The publication is 1 to many publication_issue so I switched the second (count) query to publication_issue which outputs 15 so that is now correct, but all 15 results are showing on page 1 and none on page 2 or 3.

Would it help if I send you a dump of the data in those 2 tables?

That’s definitely progress, so we still have an issue (that’s okay).

No, I can help you debug it (it will be a bit slower, but in the end, you’ll pick up some tricks :slight_smile:

So let’s start out figuring out why page 2 and 3 show nothing. I need to see what the script is receiving for input, and how that affects its output, so add this line before “$query->execute(array($Page_Start, $Page_Start + $Per_Page));”:

var_dump($_GET, $Page_Start);

What does that line do? It will write out all of the variables we receive in the URL (such as the Page Number), and it will tell us how $Page_Start got calculated.

What I need you to do, is copy and paste the output from that line and post it here for me to see it.

Sorry for the slow reply, didn’t get chance to test over the weekend.

Having posted just before the execute I see:

array(2) { [“id”]=> string(0) “” [“Page”]=> string(1) “1” } int(0)

Ahhh, I see the problem, there’s nothing in the db query to say what to fetch. In this part of the query:

WHERE a.RowNumber BETWEEN ? AND ?

How can I integrate:

WHERE tags LIKE :tags

Thanks again for your help, greatly appreciated.

EDIT

I changed it to:

   WHERE a.RowNumber BETWEEN ? AND ? AND tags LIKE :tags

And while I’m still getting the correct output $_ROW[‘title’]; its still all on one page, so on the second query: COUNT(*) I did an echo and got a result of 4 on $Num_Rows which isn’t correct, I should be getting a result of 15 divided by 5 per page.

Any ideas?

Okay, something else must be going on, as you shouldn’t see all rows…

So let’s update the the query section to this:

$query = $dbconn->prepare(" 
   SELECT   
     a.id  
     , a.title  
     , a.text  
     , a.country  
     , a.city  
     , a.cat  
     , a.user_id  
     , a.use_ad  
     , a.viewcount  
     , a.tags  
     , a.status  
     , a.mod_status  
     , publication_issue.img  
     , publication_issue.publication_id  
     , publication_issue.issue_number  
     , publication_issue.upload_date  
     , publication_issue.cat AS Expr1  
     , publication_issue.viewcount AS Expr2  
     , publication_issue.status AS Expr3  
     , publication_issue.mod_status AS Expr4  
     , publication_issue.user_id AS Expr5  
   FROM (  
      SELECT   
        id  
        , title  
        , text  
        , country  
        , city  
        , cat  
        , user_id  
        , use_ad  
        , viewcount  
        , tags  
        , status  
        , mod_status  
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM publication  
   ) AS a  
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
   WHERE a.RowNumber BETWEEN :start AND :end AND tags LIKE :tags  
", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); 

$Page_End = $Page_Start + $Per_Page;
var_dump($_GET, $Page_Start, $Page_End, $Per_Page); // used to figure out what is being placed below
$query->execute(array(':start'=>$Page_Start, ':end'=>$Page_End,':tags'=>'MyTag'));

The two queries are unrelated (or at least they should be). So let’s start small, take the following queries and run them in SQL Server and tell me how many rows you get and for the second query, tell me what the TotalRecords column says.


   SELECT   
     a.id  
     , a.title  
     , a.text  
     , a.country  
     , a.city  
     , a.cat  
     , a.user_id  
     , a.use_ad  
     , a.viewcount  
     , a.tags  
     , a.status  
     , a.mod_status  
     , publication_issue.img  
     , publication_issue.publication_id  
     , publication_issue.issue_number  
     , publication_issue.upload_date  
     , publication_issue.cat AS Expr1  
     , publication_issue.viewcount AS Expr2  
     , publication_issue.status AS Expr3  
     , publication_issue.mod_status AS Expr4  
     , publication_issue.user_id AS Expr5  
   FROM (  
      SELECT   
        id  
        , title  
        , text  
        , country  
        , city  
        , cat  
        , user_id  
        , use_ad  
        , viewcount  
        , tags  
        , status  
        , mod_status  
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM publication  
   ) AS a  
     INNER JOIN publication_issue ON a.id = publication_issue.publication_id  
   WHERE a.RowNumber BETWEEN 0 AND 9 AND tags LIKE 'MyTag'  
SELECT COUNT(*) AS TotalRecords FROM publication

Okay…

The first updated code outputs:

array(1) { [“id”]=> string(6) “Search” } int(0) int(5) int(5)
id: Title 1
id: Title 2
id: Title 3
id: Title 4
id: Title 5
id: Title 6
id: Title 7
id: Title 8
id: Title 9
id: Title 10
id: Title 11
id: Title 12
id: Title 13
id: Title 14
id: Title 15

The query I entered into query builder first prompts an error:

The OVER SQL construct or statement is not supported.

Then when I click continue, I see the column names with no data in them.

The final query in query builder outputs:

TotalRecords
4

This will seem like a troll but this is precisely why I love ActiveRecord and gems like will_paginate. You simply don’t need to worry about this stuff anymore.

Publication.paginate(page: params[:page])

I’d rather stick to what I know, I’ve never coded in Ruby before and don’t envision needing to whereas I will use MSSQL/PHP over and over again.

It depends what you’re doing this for I guess… if you are a full time web developer sticking to what you know isn’t a good rule.
You’re probably right though that for this case moving to Ruby isn’t the right solution, I’m just illustrating that a lot of the complex scripts that people write don’t need to be written at all most of the time.

Here’s what the full script would look like:


# publications_controller.rb
@publications = Publication.paginate(page: params[:page]

# publications/index.haml
- @publications.each do |publication|
  title:
  = publication.title
  %br
= will_paginate(@publications)

You are right. I’m still quite new to developing and haven’t come across the need for Ruby yet within the project I’m currently working on. I probably will in the future though, I’m quite open minded to learning new languages and want to get into iPhone development which uses JSON naturally and would give me a good head start with Ruby.