pagination working in mysl_deprecated but not working on PDO

[mutanx]

The pagination code below works well in mysql_deprecate d as follows

<?php

require('database.php');
									if (isset($_GET['chmsc']))
									{
									$chmsc=$_GET['chmsc'];
                                                                    $chmsc=strip_tags($chmsc);
                                                                 $chmsc=htmlentities($chmsc, ENT_QUOTES, "UTF-8");
									}
									else
										$chmsc=1;
									?>


<?php

$per_page=strip_tags(2);
									
				$total_pages=htmlentities(ceil($rows/$per_page), ENT_QUOTES, "UTF-8");
				echo"page $chmsc of $total_pages<br>";
										
										if($chmsc!=1)
										{
			echo "<a href='home.php?chmsc=1'>First </a>","  ";
			$previous=htmlentities($chmsc-1, ENT_QUOTES, "UTF-8");
			echo "<a href='home.php?chmsc=$previous'> Previous</a>", "  ";
										}
										if (($chmsc!=1) && ($chmsc!=$total_pages))
										echo "||";
										if($chmsc!=$total_pages)
										{
		$next=htmlentities($chmsc+ 1, ENT_QUOTES, "UTF-8");
		echo "<a href='home.php?chmsc=$next'>Next </a>","  ";
		echo "<a href='home.php?chmsc=$total_pages'> Last</a>";
										}
										echo "<br/><br/>";
			$x=($chmsc-1)*$per_page;
					


$query  = "SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE (username = '$user') ORDER BY post_id DESC limit $x, $per_page";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result)){	
// continue			

?>

When i tried to move it to PDO for sql injection protection it does not fetch any records from database below is the code

<?php
	require('database.php');

									if (isset($_GET['chmsc']))
									{
									$chmsc=$_GET['chmsc'];
                                                                    $chmsc=strip_tags($chmsc);
                                                                 $chmsc=htmlentities($chmsc, ENT_QUOTES, "UTF-8");
									}
									else
										$chmsc=1;
									?>

<?php

$per_page=strip_tags(2);
									
				$total_pages=htmlentities(ceil($rows/$per_page), ENT_QUOTES, "UTF-8");
				echo"page $chmsc of $total_pages<br>";
										
										if($chmsc!=1)
										{
			echo "<a href='home.php?chmsc=1'>First </a>","  ";
			$previous=htmlentities($chmsc-1, ENT_QUOTES, "UTF-8");
			echo "<a href='home.php?chmsc=$previous'> Previous</a>", "  ";
										}
										if (($chmsc!=1) && ($chmsc!=$total_pages))
										echo "||";
										if($chmsc!=$total_pages)
										{
		$next=htmlentities($chmsc+ 1, ENT_QUOTES, "UTF-8");
		echo "<a href='home.php?chmsc=$next'>Next </a>","  ";
		echo "<a href='home.php?chmsc=$total_pages'> Last</a>";
										}
										echo "<br/><br/>";
			$x=($chmsc-1)*$per_page;
					



$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC limit $x, $per_page');
		$result->execute(array(
			':username' => $user
		));

// continue

?>

I discovered that the problem is in the sql query statement

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC limit $x, $per_page');

if i used query statement like this

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC ');

it will query the records but it will not pagenate it. when i added the variables
$x, $per_page
it will fetch empty page. any help please

[mutanx]

i have tried this but the query still displays empty page

$result = $db->prepare('SELECT *,UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post WHERE username = :username ORDER BY post_id DESC LIMIT :X, :per_page');
        $result->execute(array(
            ':username1' =>$user,
                        ':x' => $x,
                         ':per_page' => $per_page
                    ));

The problem is from the query, any help

[Dormilich]

:X is not the same as :x.

you might also want to enable PDO's error reporting:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);