Really Struggling to Paginate Results MSSQL/PHP/PDO

PHP
21

Okay, post your entire file again, as there is something wrong, as when I run the following; I get the correct values:

CREATE TABLE #Temp 
(
	id INT,
	title VARCHAR(10)
)

CREATE TABLE #Pub
(
	publication_id INT,
	viewcount INT
)

INSERT INTO #Temp VALUES (1, 'Title 1')
INSERT INTO #Temp VALUES (2, 'Title 2')
INSERT INTO #Temp VALUES (3, 'Title 3')
INSERT INTO #Temp VALUES (4, 'Title 4')
INSERT INTO #Temp VALUES (5, 'Title 5')
INSERT INTO #Temp VALUES (6, 'Title 6')
INSERT INTO #Temp VALUES (7, 'Title 7')
INSERT INTO #Temp VALUES (8, 'Title 8')
INSERT INTO #Temp VALUES (9, 'Title 9')
INSERT INTO #Temp VALUES (10, 'Title 10')
INSERT INTO #Temp VALUES (11, 'Title 11')
INSERT INTO #Temp VALUES (12, 'Title 12')
INSERT INTO #Temp VALUES (13, 'Title 13')
INSERT INTO #Temp VALUES (14, 'Title 14')
INSERT INTO #Temp VALUES (15, 'Title 15')

INSERT INTO #Pub VALUES (1, 3)
INSERT INTO #Pub VALUES (2, 1)
INSERT INTO #Pub VALUES (3, 6)
INSERT INTO #Pub VALUES (4, 8)
INSERT INTO #Pub VALUES (5, 2)
INSERT INTO #Pub VALUES (6, 5)
INSERT INTO #Pub VALUES (7, 7)
INSERT INTO #Pub VALUES (8, 9)
INSERT INTO #Pub VALUES (9, 0)
INSERT INTO #Pub VALUES (10, 1)
INSERT INTO #Pub VALUES (11, 6)
INSERT INTO #Pub VALUES (12, 3)
INSERT INTO #Pub VALUES (13, 8)
INSERT INTO #Pub VALUES (14, 6)
INSERT INTO #Pub VALUES (15, 9)

SELECT   
     a.id  
     , a.title
     , p.viewcount
   FROM (  
      SELECT   
        id  
        , title   
        , ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber'   
      FROM #Temp  
   ) AS a  
     INNER JOIN #Pub AS p ON a.id = p.publication_id  
   WHERE a.RowNumber BETWEEN 0 AND 9 -- AND tags LIKE 'MyTag'
   
SELECT COUNT(*) AS TotalRecords FROM #Temp

DROP TABLE #Temp
DROP TABLE #Pub
22

Your query above outputs records 7 through 15.

Here is my whole script:


<?php 
ini_set('display_errors',1); // try turning on errors
$Per_Page = 5;  // 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.id AS issue_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;
$query->execute(array(':start'=>$Page_Start, ':end'=>$Page_End,':tags'=>'%e%'));  
var_dump($_GET, $Page_Start, $Page_End, $Per_Page); // used to figure out what is being placed below

?>
<ul style=""> 
<?php
while ($row = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { 
    $totalRecords = $row['totalRecords'];
?>
  <li><?php echo "id: ".$row['issue_id']." ".$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"]; 
//echo $Num_Rows;

  
//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; 
?>
23

Yes, it would because it is numbering the rows by ID descending. Thus 15 through 7 is exactly what I asked for.

24

Okay, I’m stumped. But try this code

<?php 
ini_set('display_errors',1); // try turning on errors
$Per_Page = 5;  // 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) + 1;  //added plus 1
  
$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.id AS issue_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;
$query->execute(array(':start'=>$Page_Start, ':end'=>$Page_End,':tags'=>'%e%'));  
$publications = $query->fetchAll(); // added this line

var_dump($_GET, $Page_Start, $Page_End, $Per_Page); // used to figure out what is being placed below
var_dump($publications);
?>
<ul style=""> 
<?php
foreach ($publications as $row) { // change while loop to foreach
?>
  <li><?php echo "id: ".$row['issue_id']." ".$row['title'];?></li> 
<?php
}
?>
</ul>
<?php
$query = $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication");
$query->execute();
$totalRecords = $query->fetch(PDO::FETCH_ASSOC);

var_dump($totalRecords);

$Num_Rows = $totalRecords["TotalRecords"]; 
//echo $Num_Rows;

  
//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; 
?>

Copy and Paste the output from the var_dump calls into your response so I can see what you are getting.

25

So does this indicate a problem with the script or my table data?

26

array(1) { [“id”]=> string(1) “e” } int(1) int(6) int(5) array(15) { [0]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “68” [14]=> string(2) “68” [“issue_number”]=> string(1) “0” [15]=> string(1) “0” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “228” [18]=> string(3) “228” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [1]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “71” [14]=> string(2) “71” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “119” [18]=> string(3) “119” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [2]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [12]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “72” [14]=> string(2) “72” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(2) “30” [18]=> string(2) “30” [“Expr3”]=> string(1) “0” [19]=> string(1) “0” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [3]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [12]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “73” [14]=> string(2) “73” [“issue_number”]=> string(1) “2” [15]=> string(1) “2” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “7” [18]=> string(1) “7” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [4]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [12]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “74” [14]=> string(2) “74” [“issue_number”]=> string(1) “3” [15]=> string(1) “3” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “1” [18]=> string(1) “1” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [5]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “75” [14]=> string(2) “75” [“issue_number”]=> string(1) “0” [15]=> string(1) “0” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “228” [18]=> string(3) “228” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [6]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “76” [14]=> string(2) “76” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “118” [18]=> string(3) “118” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [7]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [12]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “77” [14]=> string(2) “77” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(2) “21” [18]=> string(2) “21” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [8]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [12]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “78” [14]=> string(2) “78” [“issue_number”]=> string(1) “2” [15]=> string(1) “2” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “6” [18]=> string(1) “6” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [9]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [12]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “79” [14]=> string(2) “79” [“issue_number”]=> string(1) “3” [15]=> string(1) “3” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “2” [18]=> string(1) “2” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [10]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “80” [14]=> string(2) “80” [“issue_number”]=> string(1) “0” [15]=> string(1) “0” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “228” [18]=> string(3) “228” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [11]=> array(44) { [“id”]=> string(3) “272” [0]=> string(3) “272” [“title”]=> string(20) “Cheshire Independent” [1]=> string(20) “Cheshire Independent” [“text”]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [2]=> string(79) “Cheshire Independent is the local newspaper for Cheshire and surrounding areas.” [“country”]=> NULL [3]=> NULL [“city”]=> NULL [4]=> NULL [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(2) “33” [6]=> string(2) “33” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(37) “newspaper, local news,cheshire, news” [9]=> string(37) “newspaper, local news,cheshire, news” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(38) “…/publication/33/272/1365670090_8.jpg” [12]=> string(38) “…/publication/33/272/1365670090_8.jpg” [“publication_id”]=> string(3) “272” [13]=> string(3) “272” [“issue_id”]=> string(2) “81” [14]=> string(2) “81” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-11” [16]=> string(10) “2013-04-11” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(3) “118” [18]=> string(3) “118” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(2) “33” [21]=> string(2) “33” } [12]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [12]=> string(45) “…/publication/125/275/1366800169_cover_1.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “82” [14]=> string(2) “82” [“issue_number”]=> string(1) “1” [15]=> string(1) “1” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(2) “15” [18]=> string(2) “15” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [13]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [12]=> string(45) “…/publication/125/275/1366801191_cover_2.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “83” [14]=> string(2) “83” [“issue_number”]=> string(1) “2” [15]=> string(1) “2” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “2” [18]=> string(1) “2” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } [14]=> array(44) { [“id”]=> string(3) “275” [0]=> string(3) “275” [“title”]=> string(17) “Cheshire Standard” [1]=> string(17) “Cheshire Standard” [“text”]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [2]=> string(90) “The Cheshire & District Standard is a local newspaper for the Cheshire and district areas.” [“country”]=> string(7) “England” [3]=> string(7) “England” [“city”]=> string(7) “Chester” [4]=> string(7) “Chester” [“cat”]=> string(2) “10” [5]=> string(2) “10” [“user_id”]=> string(3) “125” [6]=> string(3) “125” [“use_ad”]=> string(1) “0” [7]=> string(1) “0” [“viewcount”]=> string(1) “0” [8]=> string(1) “0” [“tags”]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [9]=> string(76) “cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr” [“status”]=> string(1) “1” [10]=> string(1) “1” [“mod_status”]=> string(1) “1” [11]=> string(1) “1” [“img”]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [12]=> string(45) “…/publication/125/275/1366801225_cover_3.jpg” [“publication_id”]=> string(3) “275” [13]=> string(3) “275” [“issue_id”]=> string(2) “84” [14]=> string(2) “84” [“issue_number”]=> string(1) “3” [15]=> string(1) “3” [“upload_date”]=> string(10) “2013-04-24” [16]=> string(10) “2013-04-24” [“Expr1”]=> string(2) “10” [17]=> string(2) “10” [“Expr2”]=> string(1) “1” [18]=> string(1) “1” [“Expr3”]=> string(1) “1” [19]=> string(1) “1” [“Expr4”]=> string(1) “1” [20]=> string(1) “1” [“Expr5”]=> string(3) “125” [21]=> string(3) “125” } }
id: 68 Cheshire Independent
id: 71 Cheshire Independent
id: 72 Cheshire Standard
id: 73 Cheshire Standard
id: 74 Cheshire Standard
id: 75 Cheshire Independent
id: 76 Cheshire Independent
id: 77 Cheshire Standard
id: 78 Cheshire Standard
id: 79 Cheshire Standard
id: 80 Cheshire Independent
id: 81 Cheshire Independent
id: 82 Cheshire Standard
id: 83 Cheshire Standard
id: 84 Cheshire Standard
array(1) { [“TotalRecords”]=> string(1) “4” } 1

27

Now I think I know what is going on. Your publication_issue has multiple records for each record in publication. It isn’t a 1-1 relationship.

Example:
You have a publication with the title of Cheshire Standard in your publication table. Which is particularly evident in the below part of the output:

	[0]=> array(44) { 
		["id"]=> string(3) "272" 
		[0]=> string(3) "272" 
		["title"]=> string(20) "Cheshire Independent" 
		[1]=> string(20) "Cheshire Independent" 
		["text"]=> string(79) "Cheshire Independent is the local newspaper for Cheshire and surrounding areas." 
		[2]=> string(79) "Cheshire Independent is the local newspaper for Cheshire and surrounding areas." 
		["country"]=> NULL 
		[3]=> NULL 
		["city"]=> NULL 
		[4]=> NULL 
		["cat"]=> string(2) "10" 
		[5]=> string(2) "10" 
		["user_id"]=> string(2) "33" 
		[6]=> string(2) "33" 
		["use_ad"]=> string(1) "0" 
		[7]=> string(1) "0" 
		["viewcount"]=> string(1) "0" 
		[8]=> string(1) "0" 
		["tags"]=> string(37) "newspaper, local news,cheshire, news" 
		[9]=> string(37) "newspaper, local news,cheshire, news" 
		["status"]=> string(1) "1" 
		[10]=> string(1) "1" 
		["mod_status"]=> string(1) "1" 
		[11]=> string(1) "1" 
		["img"]=> string(38) "../publication/33/272/1365670090_8.jpg" 
		[12]=> string(38) "../publication/33/272/1365670090_8.jpg" 
		["publication_id"]=> string(3) "272" 
		[13]=> string(3) "272" 
		["issue_id"]=> string(2) "68" 
		[14]=> string(2) "68" 
		["issue_number"]=> string(1) "0" 
		[15]=> string(1) "0" 
		["upload_date"]=> string(10) "2013-04-11" 
		[16]=> string(10) "2013-04-11" 
		["Expr1"]=> string(2) "10" 
		[17]=> string(2) "10" 
		["Expr2"]=> string(3) "228" 
		[18]=> string(3) "228" 
		["Expr3"]=> string(1) "1" 
		[19]=> string(1) "1" 
		["Expr4"]=> string(1) "1" 
		[20]=> string(1) "1" 
		["Expr5"]=> string(2) "33" 
		[21]=> string(2) "33" 
	} 
	[1]=> array(44) { 
		["id"]=> string(3) "272" 
		[0]=> string(3) "272" 
		["title"]=> string(20) "Cheshire Independent" 
		[1]=> string(20) "Cheshire Independent" 
		["text"]=> string(79) "Cheshire Independent is the local newspaper for Cheshire and surrounding areas." 
		[2]=> string(79) "Cheshire Independent is the local newspaper for Cheshire and surrounding areas." 
		["country"]=> NULL 
		[3]=> NULL 
		["city"]=> NULL 
		[4]=> NULL 
		["cat"]=> string(2) "10" 
		[5]=> string(2) "10" 
		["user_id"]=> string(2) "33" 
		[6]=> string(2) "33" 
		["use_ad"]=> string(1) "0" 
		[7]=> string(1) "0" 
		["viewcount"]=> string(1) "0" 
		[8]=> string(1) "0" 
		["tags"]=> string(37) "newspaper, local news,cheshire, news" 
		[9]=> string(37) "newspaper, local news,cheshire, news" 
		["status"]=> string(1) "1" 
		[10]=> string(1) "1" 
		["mod_status"]=> string(1) "1" 
		[11]=> string(1) "1" 
		["img"]=> string(38) "../publication/33/272/1365670090_8.jpg" 
		[12]=> string(38) "../publication/33/272/1365670090_8.jpg" 
		["publication_id"]=> string(3) "272" 
		[13]=> string(3) "272" 
		["issue_id"]=> string(2) "71" 
		[14]=> string(2) "71" 
		["issue_number"]=> string(1) "1" 
		[15]=> string(1) "1" 
		["upload_date"]=> string(10) "2013-04-11" 
		[16]=> string(10) "2013-04-11" 
		["Expr1"]=> string(2) "10" 
		[17]=> string(2) "10" 
		["Expr2"]=> string(3) "119" 
		[18]=> string(3) "119" 
		["Expr3"]=> string(1) "1" 
		[19]=> string(1) "1" 
		["Expr4"]=> string(1) "1" 
		[20]=> string(1) "1" 
		["Expr5"]=> string(2) "33" 
		[21]=> string(2) "33" 
	} 
	[2]=> array(44) { 
		["id"]=> string(3) "275" 
		[0]=> string(3) "275" 
		["title"]=> string(17) "Cheshire Standard" 
		[1]=> string(17) "Cheshire Standard" 
		["text"]=> string(90) "The Cheshire & District Standard is a local newspaper for the Cheshire and district areas." 
		[2]=> string(90) "The Cheshire & District Standard is a local newspaper for the Cheshire and district areas." 
		["country"]=> string(7) "England" 
		[3]=> string(7) "England" 
		["city"]=> string(7) "Chester" 
		[4]=> string(7) "Chester" 
		["cat"]=> string(2) "10" 
		[5]=> string(2) "10" 
		["user_id"]=> string(3) "125" 
		[6]=> string(3) "125" 
		["use_ad"]=> string(1) "0" 
		[7]=> string(1) "0" 
		["viewcount"]=> string(1) "0" 
		[8]=> string(1) "0" 
		["tags"]=> string(76) "cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr" 
		[9]=> string(76) "cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr" 
		["status"]=> string(1) "1" 
		[10]=> string(1) "1" 
		["mod_status"]=> string(1) "1" 
		[11]=> string(1) "1" 
		["img"]=> string(45) "../publication/125/275/1366800169_cover_1.jpg" 
		[12]=> string(45) "../publication/125/275/1366800169_cover_1.jpg" 
		["publication_id"]=> string(3) "275" 
		[13]=> string(3) "275" 
		["issue_id"]=> string(2) "72" 
		[14]=> string(2) "72" 
		["issue_number"]=> string(1) "1" 
		[15]=> string(1) "1" 
		["upload_date"]=> string(10) "2013-04-24" 
		[16]=> string(10) "2013-04-24" 
		["Expr1"]=> string(2) "10" 
		[17]=> string(2) "10" 
		["Expr2"]=> string(2) "30" 
		[18]=> string(2) "30" 
		["Expr3"]=> string(1) "0" 
		[19]=> string(1) "0" 
		["Expr4"]=> string(1) "1" 
		[20]=> string(1) "1" 
		["Expr5"]=> string(3) "125" 
		[21]=> string(3) "125" 
	} 
	[3]=> array(44) { 
		["id"]=> string(3) "275" 
		[0]=> string(3) "275" 
		["title"]=> string(17) "Cheshire Standard" 
		[1]=> string(17) "Cheshire Standard" 
		["text"]=> string(90) "The Cheshire & District Standard is a local newspaper for the Cheshire and district areas." 
		[2]=> string(90) "The Cheshire & District Standard is a local newspaper for the Cheshire and district areas." 
		["country"]=> string(7) "England" 
		[3]=> string(7) "England" 
		["city"]=> string(7) "Chester" 
		[4]=> string(7) "Chester" 
		["cat"]=> string(2) "10" 
		[5]=> string(2) "10" 
		["user_id"]=> string(3) "125" 
		[6]=> string(3) "125" 
		["use_ad"]=> string(1) "0" 
		[7]=> string(1) "0" 
		["viewcount"]=> string(1) "0" 
		[8]=> string(1) "0" 
		["tags"]=> string(76) "cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr" 
		[9]=> string(76) "cheshire, cheshire standard, nwn media, nwn, the cheshire standard newsapepr" 
		["status"]=> string(1) "1" 
		[10]=> string(1) "1" 
		["mod_status"]=> string(1) "1" 
		[11]=> string(1) "1" 
		["img"]=> string(45) "../publication/125/275/1366801191_cover_2.jpg" 
		[12]=> string(45) "../publication/125/275/1366801191_cover_2.jpg" 
		["publication_id"]=> string(3) "275" 
		[13]=> string(3) "275" 
		["issue_id"]=> string(2) "73" 
		[14]=> string(2) "73" 
		["issue_number"]=> string(1) "2" 
		[15]=> string(1) "2" 
		["upload_date"]=> string(10) "2013-04-24" 
		[16]=> string(10) "2013-04-24" 
		["Expr1"]=> string(2) "10" 
		[17]=> string(2) "10" 
		["Expr2"]=> string(1) "7" 
		[18]=> string(1) "7" 
		["Expr3"]=> string(1) "1" 
		[19]=> string(1) "1" 
		["Expr4"]=> string(1) "1" 
		[20]=> string(1) "1" 
		["Expr5"]=> string(3) "125" 
		[21]=> string(3) "125" 
	}
28

Do you think there’s anything I can do to solve the issue?

The way the whole website is set up is like:

  1. User signs up
  2. Upon signup folder is created using user id
  3. User creates a publication - folder created within userid folder
  4. User adds issues to publication
29

Well what do you want it to do. Do you want it to show each issue? And limit the number of issues per page, or do you want to show each publication (joining on its latest issue)?

30

I could cope with only showing the last issue, but ideally I’d like to have everything and order it by date.

The publication table only contains generic information it remains the same for every issue, I didn’t think there’d be a problem with a 1 to many here.

31

Okay, now that I know that, give me a bit more time to rework your queries.

32

I’ve changed the count query to:

SELECT COUNT(publication.id) AS Publications
FROM publication INNER JOIN
publication_issue ON publication.id = publication_issue.publication_id
WHERE (publication.tags LIKE N’%e%')

And I now get the following var_dump which looks correct if it helps:

array(1) { [“Publications”]=> string(2) “15” } 1

33

Okay, try the following:

<?php
ini_set('display_errors',1); // try turning on errors
$Per_Page = 5;  // 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) + 1;  //added plus 1

$query = $dbconn->prepare("
   SELECT
     id
     , title
     , text
     , country
     , city
     , cat
     , user_id
     , use_ad
     , viewcount
     , tags
     , status
     , mod_status
     , img
     , publication_id
     , issue_id
     , issue_number
     , upload_date
     , Expr1
     , Expr2
     , Expr3
     , Expr4
     , Expr5
     , RowNumber
   FROM (
      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.id AS issue_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
         , ROW_NUMBER() OVER (ORDER BY upload_date, i.id DESC) AS 'RowNumber'
      FROM publication AS p LEFT JOIN publication_issue AS i ON p.id = i.publication_id
   ) AS a
   WHERE RowNumber BETWEEN :start AND :end AND tags LIKE :tags
", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$Page_End = $Page_Start + $Per_Page - 1;
$query->execute(array(':start'=>$Page_Start, ':end'=>$Page_End,':tags'=>'%e%'));
$publications = $query->fetchAll(); // added this line

var_dump($_GET, $Page_Start, $Page_End, $Per_Page); // used to figure out what is being placed below
var_dump($publications);
?>
<ul style="">
<?php
foreach ($publications as $row) { // change while loop to foreach
?>
  <li><?php echo "id: ".$row['issue_id']." ".$row['title'];?></li>
<?php
}
?>
</ul>
<?php
$query = $dbconn->prepare("SELECT COUNT(*) AS TotalRecords FROM publication AS p LEFT JOIN publication_issue AS i ON p.id = i.publication_id WHERE tags LIKE :tags");
$query->execute(array(':tags'=>'%e%');
$totalRecords = $query->fetch(PDO::FETCH_ASSOC);

var_dump($totalRecords);

$Num_Rows = $totalRecords["TotalRecords"];
//echo $Num_Rows;


//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;
?>
34

Yep. I updated that query to do the same thing :slight_smile:

35

Oh, more progress I think.

I now have page numbers and the var_dump changes with each page.

I have no data output though…

Page 1:

array(2) { [“id”]=> string(0) “” [“Page”]=> string(1) “1” } int(1) int(5) int(5) array(0) { }
array(1) { [“TotalRecords”]=> string(2) “17” } 1 2 3 4 Next>>

Page 2:

array(2) { [“id”]=> string(0) “” [“Page”]=> string(1) “2” } int(6) int(10) int(5) array(0) { }
array(1) { [“TotalRecords”]=> string(2) “17” } << Back 1 2 3 4 Next>>

Page 3:

array(2) { [“id”]=> string(0) “” [“Page”]=> string(1) “3” } int(11) int(15) int(5) array(0) { }
array(1) { [“TotalRecords”]=> string(2) “17” } << Back 1 2 3 4 Next>>

Page 4:

array(2) { [“id”]=> string(0) “” [“Page”]=> string(1) “4” } int(16) int(20) int(5) array(0) { }
array(1) { [“TotalRecords”]=> string(2) “17” } << Back 1 2 3 4

36

Crap, copy and paste issue

Change the “a.” to “p.” in the following section:

         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

You can also change “publication_issue.” to “i.” in the following:

         , publication_issue.img   
         , publication_issue.publication_id   
         , publication_issue.id AS issue_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
37

Still the same unfortunately.

38

When you run the following query in SSMS (or Query Analyzer) what do you get?

   SELECT    
     id
     , title
     , text
     , country
     , city
     , cat
     , user_id
     , use_ad
     , viewcount
     , tags
     , status
     , mod_status
     , img
     , publication_id
     , issue_id
     , issue_number
     , upload_date
     , Expr1
     , Expr2
     , Expr3
     , Expr4
     , Expr5
     , RowNumber
   FROM (  
      SELECT   
         p.id  
         , p.title  
         , p.text  
         , p.country  
         , p.city  
         , p.cat  
         , p.user_id  
         , p.use_ad  
         , p.viewcount  
         , p.tags  
         , p.status  
         , p.mod_status  
         , i.img  
         , i.publication_id  
         , i.id AS issue_id
         , i.issue_number  
         , i.upload_date  
         , i.cat AS Expr1  
         , i.viewcount AS Expr2  
         , i.status AS Expr3  
         , i.mod_status AS Expr4  
         , i.user_id AS Expr5 
         , ROW_NUMBER() OVER (ORDER BY upload_date, i.id DESC) AS 'RowNumber'   
      FROM publication AS p LEFT JOIN publication_issue AS i ON p.id = i.publication_id
   ) AS a 
   WHERE RowNumber BETWEEN 0 AND 5 AND tags LIKE '%e%'
39

Right, we’re nearly there. I’m just having another small issue.

Page 1 contains 3 results, page 4 contains 2.

I’ve only set it as 5 per page as I didn’t add enough testing data to have 12 per page.

Is there any reason why the script is not showing 3 pages of 5 results?

Thanks again for your help, you’ve been excellent.

40

Yep. You’d think I’d begin getting this right :slight_smile: Here is the updated query (I hope):

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