MYSQL - PHP pagination script - HELP

JordanSmith1111 · September 21, 2013

Hello,

I recently downloaded the following private message script which really suits my needs

http://www.webestools.com/scripts_tutorials-code-source-15-personal-message-system-in-php-mysql-pm-system-private-message-discussion.html

The only problem I am having is trying to incorporate a pagination script to it, I tried several scripts I found online with no luck because they use very simple MYSQL request such as SELECT * from TABLENAME.... as you can see in the script from that private message php script, it uses a "complex" request and I am having a hard time trying to make it work due to my little experience programming

Can anybody help me out incorporation a simple pagination script that loads the content of the next or previos page without reloading /refreshing the current page?

Thanks a lot!

mac_gyver · September 21, 2013

posting the actual query or just the php code that forms the query, would help someone to help you.

DFulg · September 21, 2013

PHPFreaks has a tutorial on basic pagination available: here

JordanSmith1111 · September 21, 2013

if(isset($_SESSION['username']))
{
//We list his messages in a table
//Two queries are executes, one for the unread messages and another for read messages
$req1 = mysql_query('select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username from pm as m1, pm as m2,users where ((m1.user1="'.$_SESSION['userid'].'" and m1.user1read="no" and users.id=m1.user2) or (m1.user2="'.$_SESSION['userid'].'" and m1.user2read="no" and users.id=m1.user1)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc');
$req2 = mysql_query('select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username from pm as m1, pm as m2,users where ((m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2) or (m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc');
?>
This is the list of your messages:<br />
<a href="new_pm.php" class="link_new_pm">New PM</a><br />
<h3>Unread Messages(<?php echo intval(mysql_num_rows($req1)); ?>):</h3>
<table>
	<tr>
    	<th class="title_cell">Title</th>
        <th>Nb. Replies</th>
        <th>Participant</th>
        <th>Date of creation</th>
    </tr>
<?php
//We display the list of unread messages
while($dn1 = mysql_fetch_array($req1))
{
?>
	<tr>
    	<td class="left"><a href="read_pm.php?id=<?php echo $dn1['id']; ?>"><?php echo htmlentities($dn1['title'], ENT_QUOTES, 'UTF-8'); ?></a></td>
    	<td><?php echo $dn1['reps']-1; ?></td>
    	<td><a href="profile.php?id=<?php echo $dn1['userid']; ?>"><?php echo htmlentities($dn1['username'], ENT_QUOTES, 'UTF-8'); ?></a></td>
    	<td><?php echo date('Y/m/d H:i:s' ,$dn1['timestamp']); ?></td>
    </tr>
<?php
}
//If there is no unread message we notice it
if(intval(mysql_num_rows($req1))==0)
{
?>
	<tr>
    	<td colspan="4" class="center">You have no unread message.</td>
    </tr>
<?php
}
?>
</table>
<br />
<h3>Read Messages(<?php echo intval(mysql_num_rows($req2)); ?>):</h3>
<table>
	<tr>
    	<th class="title_cell">Title</th>
        <th>Nb. Replies</th>
        <th>Participant</th>
        <th>Date or creation</th>
    </tr>
<?php
//We display the list of read messages
while($dn2 = mysql_fetch_array($req2))
{
?>
	<tr>
    	<td class="left"><a href="read_pm.php?id=<?php echo $dn2['id']; ?>"><?php echo htmlentities($dn2['title'], ENT_QUOTES, 'UTF-8'); ?></a></td>
    	<td><?php echo $dn2['reps']-1; ?></td>
    	<td><a href="profile.php?id=<?php echo $dn2['userid']; ?>"><?php echo htmlentities($dn2['username'], ENT_QUOTES, 'UTF-8'); ?></a></td>
    	<td><?php echo date('Y/m/d H:i:s' ,$dn2['timestamp']); ?></td>
    </tr>
<?php
}
//If there is no read message we notice it
if(intval(mysql_num_rows($req2))==0)
{
?>
	<tr>
    	<td colspan="4" class="center">You have no read message.</td>
    </tr>
<?php
}
?>
</table>
<?php
}
else
{
	echo 'You must be logged to access this page.';
}
?>

thats the code guys.... that mysql query is too complex for me to understand or incorporate to any pagination script i found online

JordanSmith1111 · September 21, 2013

I tried to adjust the script to the phpfreaks pagination tutorial and still no success

I am getting a Fatal error: SQL in /home/content/13/8078813/html/datespal/test2.php on line 51, (LINE 51 is: $result = mysql_query($sql, $conn) or trigger_error...................)

I think the problem might be the SQL query/request because its joining two tables.. but I've no idea... im not too expert. Without the pagination it works like a charm, I just dont want to load a bunch of messages in only one page.

<?php
$conn = mysql_connect('host_here','my_db_name','PASSWORD!') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('my_db_name',$conn) or trigger_error("SQL", E_USER_ERROR);

// database connection info


// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM pm";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 





$sql = mysql_query('select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username, users.avatar from pm as m1, pm as m2,users where(
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc LIMIT "$offset", "$rowsperpage"');
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
?>
<table width="600" cellpadding="6" cellspacing="6" class="blackfont">
  <tr align="center" bgcolor="#0066FF" class="whitefont">
    <td class="title_cell">Subject</td>
    <td># of Replies</td>
    <td>From</td>
    <td>Date / Time</td>
    <td>Delete Message?</td>
  </tr>
  <?php
//We display the list of read messages
?>
  <tr>
    <td class="left"><a href="read_pm.php?id=<?php echo $sql['id']; ?>"><?php echo htmlentities($sql['title'], ENT_QUOTES, 'UTF-8'); ?></a></td>
    <td><?php echo $sql['reps']-1; ?></td>
    <td align="center"><?php
//display avatar
if($sql['avatar']!='') {    echo '<img src="'.$sql['avatar'].'" style="max-width:50px;max-height:50px;" />';}?>
      <a href="profile.php?id=<?php echo $dn1['userid']; ?>"> </a><a href="profile.php?id=<?php echo $sql['userid']; ?>">
        <div class="tinyblackfont"> <?php echo ucfirst(htmlentities($sql['username'], ENT_QUOTES, 'UTF-8')); ?></div>
      </a></td>
    <td><?php echo date('Y/m/d H:i:s' ,$sql['timestamp']); ?></td>
    <td align="center" valign="middle"><form action="" method="post" name="form1" id="form2" onsubmit="return confirm('Are you sure you want to DELETE this message?')">
      <p>
        <label>
          <input type="submit" name="submit" id="submit" value="Delete message" />
          <input type="hidden" name="id2" value="<?php echo $sql['id']; ?>" />
          <input type="hidden" name="eraser" value="<?php echo $_SESSION['userid']; ?>" />
        </label>
        <br />
      </p>
    </form></td>
  </tr>
</table>
<?
} // end while
/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

Edited September 21, 2013 by JordanSmith1111

raymsmith · September 21, 2013

Can you give an example of the complex sql? I've always used two statements for pagination, the first using COUNT() to establish the total number of rows, the second using LIMIT and OFFSET to retrieve the contents of a specific page.

mac_gyver · September 21, 2013

pagination requires two queries, the first query to get a count of the matching rows (it needs to join the same tables with the same join/where conditions, but can leave off any order by term) and a second query to get just the block of rows via the LIMIT clause.

for your example of paginating the read messages, the first COUNT(*) query statement should be (untested) -

$sql = 'select COUNT(*) from pm as m1, pm as m2,users where (
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id';

the second error is because the kid that wrote this pm script did a technically correct, but minimal project, which means you cannot just copy/paste parts of it as is and expect it to work. the second query statement (untested) -

$sql = 'select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username, users.avatar from pm as m1, pm as m2,users where (
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc' . " LIMIT $offset, $rowsperpage";

JordanSmith1111 · September 21, 2013

pagination requires two queries, the first query to get a count of the matching rows (it needs to join the same tables with the same join/where conditions, but can leave off any order by term) and a second query to get just the block of rows via the LIMIT clause.

for your example of paginating the read messages, the first COUNT(*) query statement should be (untested) -
$sql = 'select COUNT(*) from pm as m1, pm as m2,users where (
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id';
the second error is because the kid that wrote this pm script did a technically correct, but minimal project, which means you cannot just copy/paste parts of it as is and expect it to work. the second query statement (untested) -
$sql = 'select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username, users.avatar from pm as m1, pm as m2,users where (
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc' . " LIMIT $offset, $rowsperpage";

Thanks for the reply... i replaced those and now the error is gone but all i get as result is:

> >>

JordanSmith1111 · September 21, 2013

Can you give an example of the complex sql? I've always used two statements for pagination, the first using COUNT() to establish the total number of rows, the second using LIMIT and OFFSET to retrieve the contents of a specific page.

$sql = mysql_query('select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username, users.avatar from pm as m1, pm as m2,users where(
(m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or
(m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc');
?>

JordanSmith1111 · September 21, 2013

Ok guys after messing around with it, realized i wasnt including the "session.php" where it collects user name among other stuff... Now i got it working 90%..

Ijust need help with one more thing, it is not showing me the page #2, or #3... etc etc, it only displays X amount of messages depending on what I specify on the value for $rowsperpage. In this case is 5, so it only shows up 5 messages and only [1] page, eventhough there are like 20 messages... should have 2, 3 and 4 below them.... please help me out realizing whats going on.. THANKS!

<?php
include("include/session.php");
// database connection info
$conn = mysql_connect('XXXXX','databasename','PASS') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('databasename',$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table 
$sql = 'select COUNT(*) from pm as m1, pm as m2,users where ((m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or (m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id';
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 5;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = 'select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, users.id as userid, users.username, users.avatar from pm as m1, pm as m2,users where ((m1.user1="'.$_SESSION['userid'].'" and m1.user1read="yes" and users.id=m1.user2 and m1.erasemsga!=m1.user1) or (m1.user2="'.$_SESSION['userid'].'" and m1.user2read="yes" and users.id=m1.user1 and m1.erasemsgb!=m1.user2)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc' . " LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
?>
<table width="600" cellpadding="6" cellspacing="6" class="blackfont">
      <?php
//We display the list of unread messages
{
?>
      <tr>
        <td class="left"><a href="read_pm.php?id=<?php echo $list['id']; ?>"><?php echo htmlentities($list['title'], ENT_QUOTES, 'UTF-8'); ?></a></td>
        <td><?php echo $list['reps']-1; ?></td>
        <td align="center"><a href="profile.php?id=<?php echo $list['userid']; ?>">
          <?php
//display avatar
if($list['avatar']!='') {	echo '<img src="'.htmlentities($list['avatar']).'" alt="Image Perso" style="max-width:50px;max-height:50px;" />';}?>
          <div class="tinyblackfont"><?php echo htmlentities($list['username'], ENT_QUOTES, 'UTF-8'); ?></div></a></td>
        <td><?php echo date('Y/m/d H:i:s' ,$list['timestamp']); ?></td>
        <td align="center"><form action="" method="post" name="form1" id="form1" onsubmit="return confirm('Are you sure you want to DELETE this message?')">
          <p>
            <label>
              <input type="hidden" name="id3" value="<?php echo $list['id']; ?>" />
              <input type="hidden" name="eraser2" value="<?php echo $_SESSION['userid']; ?>" />
            </label>
            <br />
            <label>
              <input type="submit" name="submit2" id="submit2" value="Delete message" />
            </label>
          </p>
        </form></td>
      </tr>
      <?php
}
//If there is no unread message we notice it
?>
    </table>

<?
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

Edited September 21, 2013 by JordanSmith1111

JordanSmith1111 · September 22, 2013

anybody ?

mac_gyver · September 23, 2013

@madhuvishwakarma

posting generic copy/past search results you found on the Internet doesn't help the OP with the specific problem in his code and isn't how programming help works.

Please use the forum's

bbcode tags (the edit form's <> button) when posting code.

Sign In

MYSQL - PHP pagination script - HELP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information