PDO Query Help

Hi

I'm trying to paginate some results from a PDO query. I can get the count and get the pagination working, but for some reason can't output the data.

I'm not too good at handling arrays, but I can var_dump the data and get an output, but when I try and output it as a $row, I don't get a result.

What am I missing?

Thanks

 
<?php 
$sth = $dbconn->prepare ("SELECT     publication.id, publication.title, publication.text, publication.country, publication.city, publication.cat, publication.user_id, publication.use_ad, publication.viewcount, 
                      publication.tags, publication.status, publication.mod_status, publication_issue.publication_id, publication_issue.img, publication_issue.id AS Expr1
FROM         publication INNER JOIN
                      publication_issue ON publication.id = publication_issue.publication_id
WHERE     (publication.tags LIKE :tags)"); 
 
 
$results = $sth->execute(array(":tags"=>'%e%'));
 
while ($row = $sth->fetchAll(PDO::FETCH_ASSOC)) {
 
 
 
    $pages = ceil(count($row)/4); 
    $page  = (isset ($_GET['page']))  ? (int) $_GET['page'] : 1 ;
    $start = ($page - 1) *  $per_page; 
 
    echo '<br>' . $row['id'] . '<br>' . $row['title'] . '<br>' . $row['img'] . '<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>';}
    }
 
 
}
 
 
 
?>
 

PHP:

 

You need to be using LIMIT.

At the end of your query you need to set the start point (0 for page 1) and then the end point (total shown per page), these numbers increase as someone goes through the pages.

LIMIT $start, $end

Code (markup):

I don't have time to write the code but have a look it up online, there's tons of examples.

 

Any idea on how the limit would translate into MSSQL? Seen some examples but all of them have rownum in the WHERE clause rather than my wildcard tag...

 

Just looked on Google and found this, I have no experience with MSSQL so can't help directly.

select * from
(select Row_Number() over ( ORDER BY FLD_AGE DESC ) as RowIndex, FLD_NAME, FLD_AGE FROM TBL_USERS WHERE FLAG='1') as pager Where pager.RowIndex >= 10 and pager.RowIndex < 40

Code (markup):

Also check this out: http://vorg.ca/626-the-MS-SQL-equivalent-to-MySQLs-limit-command

 

That's why I'm having so many issues getting this to work. There doesn't appear to be enough people with the knowledge to help me unfortunately.

With your query I get:

 

That's why the only people who use mssql tend to be in .NET to begin with, and then don't usually build their queries by hand.

though are you on MSSQL 2012? If so they dragged it kicking and screaming into the bleeding edge of 1985 dBase by adding these:

	OFFSET 10 ROWS
	FETCH NEXT 10 ROWS ONLY

Code (markup):

Older MSSQL engines it's a bit trickier... You're in the right ballpark with row_number, but you need to use common table expressions to do it.

$sth = $dbconn->prepare('
	WITH publicationCTE AS (
		SELECT 
		
			publication.id, publication.title, publication.text, publication.country, publication.city, publication.cat, publication.user_id, publication.use_ad, publication.viewcount, publication.tags, publication.status, publication.mod_status, 
			
			publication_issue.publication_id, publication_issue.img, publication_issue.id,
			
			ROW_NUMBER() OVER(ORDER BY publication.id) AS rowNumber
			
		FROM publication
		
		INNER JOIN publication_issue 
			ON publication.id = publication_issue.publication_id
			
		WHERE publication.tags LIKE :tags
	)
	SELECT *
	FROM publicationCTE
	WHERE rowNumber BETWEEN :startRow AND :endRow
');

Code (markup):

I THINK that's right... but I'm not sure.

There's so many gaps in MSSQL I don't know how anyone does anything useful with it -- it's outshined by dBase, clipper and Paradox from the DOS days in terms of query building!

 

I only switched to MSSQL last month and to be honest, I've found it alright so far. I'm just struggling to build this one query and I'm determined to get it done. I've also got MySQL on the server so I could build the query using that but I'm not going to waste resources doing it.

I'll give the above a go and see what I come up with.

Thanks.