PHP pagination

[jovalex]

I have a php file for pagination content and everything works ok, but I cannot figure why I cannot limit content on one page to show more than one article on a page. Here's the code.

<link href="/index.php_files/default.css" rel="stylesheet" 

<?php

include '/admin/baza.php';
$conn = mysql_connect ($hostname, $username, $password) or die ('Connection Failed.<br>');
$db = mysql_select_db ($database) or die ('DB selection failed.');

// Number of records to show per page
$recordsPerPage = 3; # 0

// default startup page
$pageNum = 1;

if(isset($_GET['p'])) {
$pageNum = $_GET['p'];
settype($pageNum, 'integer');
}

//$offset = ($pageNum - 1) * 3;
$offset = ($pageNum - 1) * $recordsPerPage;


//$query = "SELECT name, date FROM tblfour WHERE name='a' LIMIT $offset, $recordsPerPage;"; # 1. Main query
//$query2 = "SELECT Naslov FROM Vijesti WHERE Ime='Vijesti' LIMIT $offset, $recordsPerPage;"; # 1. Main query

$query  = "SELECT Vijesti.ID, LEFT(VijestiText,600), Naslov, Procitano, AID, Datum2, VijestiDatum, slikaID," .
          "DATE_FORMAT(VijestiDatum, '%d.%m.%Y.') as formated_date " .
          "FROM Vijesti, Kategorije, VijestiProvjera  WHERE Ime LIKE '%Vijesti - Blok%' AND CID=Kategorije.ID AND JID=Vijesti.ID ORDER BY VijestiDatum DESC LIMIT $offset, $recordsPerPage;";

$result = mysql_query($query) or die('Mysql Err. 1');

# 2 change/add columns name
while($row = mysql_fetch_assoc($result)) {
// moj kod 
  $id = $row['ID'];
  $naslov = $row['Naslov'];
  $newstext = $row['LEFT(VijestiText,600)'];
  $podnaslov = $row['Podnaslov']; 
  $jdate = $row['formated_date'];
  $jdate2 = $row['Datum2']; 
  $slikaID = $row['slikaID']; 
 // kraj mog koda 
}


# Update this query with same where clause you are using above.
//$query = "SELECT COUNT(date) AS dt FROM tblfour WHERE name='a';"; # 3
$query  = "SELECT COUNT(VijestiDatum) AS dt " .
          //"DATE_FORMAT(VijestiDatum, '%d.%m.%Y.') as formated_date " .
          "FROM Vijesti, Kategorije, VijestiProvjera  WHERE Ime LIKE '%Vijesti - Blok%' AND CID=Kategorije.ID AND JID=Vijesti.ID";

$result = mysql_query($query) or die('Mysql Err. 2');
$row = mysql_fetch_assoc($result);
$numrows = $row['dt']; # 4

$maxPage = ceil($numrows/$recordsPerPage);

$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
//$nav .= "<div class=\"pNo\">$page</div>";
$nav .= "<div class=\"pNo\">&nbsp;&nbsp;</div>";


}
else
{
//$nav .= "<div class=\"pNo\"><a href=\"?p=$page\">$page</a></div>";
//$nav .= "<div class=\"pNo\"><a href=\"?p=$page\">$naslov</a></div>";

}
}

if ($pageNum > 1) {

$page = $pageNum - 1;
$prev = "<a href=\"?p=$page\"><strong><</strong></a>";
//$first = "<a href=\"?p=1\"><strong><<</strong></a>";
}
else {
$prev = '<strong> </strong>';
//$first = '<strong> </strong>';
}

if ($pageNum < $maxPage) {
$page = $pageNum + 1;
$next = "<a href=\"?p=$page\"><strong>></strong></a>";

$last = "<a href=\"?p=$maxPage\"><strong>>></strong></a>";
}
else {
$next = '<strong> </strong>';
$last = '<strong> </strong>';
}

//echo $data;
echo $newstext;
echo "<div class=\"pagingDiv\">

<div class=\"pNo\">$prev</div>
$nav
<div class=\"pNo\">$next</div>
</div>";
?>

[spiderling]

The query should not have the semicolon after $recordsPerPage, mysql_query(), and may be the issue.

$query = "...LIMIT $offset, $recordsPerPage"; 

Just some other observations.

Your link tag for the css file is missing the closing "greater than" sign.

It would be better to assign an alias for the VijestiText column. I noticed you did assign an alias for others.

$query  = "SELECT Vijesti.ID, LEFT(VijestiText,600) AS VijestiText, ...";
while($row = mysql_fetch_assoc($result)) {
  $newstext = $row['VijestiText'];
} 

When querying multiple tables, it would be more efficient to use the JOIN syntax.

When using special characters in HTML such as '>', it is better to use the html entity.

$last = "<a href=\"?p=$maxPage\"><strong>>&gt;</strong></a>"; 

[spiderling]

Sorry, I just noticed the $newstext echo at the bottom.

In the while statement $newstext is equal to $row['VijestiText'] and gets replaced with the next $row['VijestiText'] as it loops. So, at the end of the loop $newstext contains the last value only.

You would have to concatenate the value as it loops.

while($row = mysql_fetch_assoc($result)) {
  $newstext .= $row['VijestiText'].'<BR><BR>';
} 

or

$newstextarray = array();
while($row = mysql_fetch_assoc($result)) {
  $newstextarray[] = $row['VijestiText'];
}

$newstext = implode('<BR><BR>',$newstextarray); # this way you won't have the extra double break at the end, just in between the articles 

[jovalex]

solved, now it's working.
thanks