JordanSmith1111 Posted September 21, 2013 Share Posted 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! Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 21, 2013 Share Posted September 21, 2013 posting the actual query or just the php code that forms the query, would help someone to help you. Quote Link to comment Share on other sites More sharing options...
DFulg Posted September 21, 2013 Share Posted September 21, 2013 PHPFreaks has a tutorial on basic pagination available: here Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 21, 2013 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 21, 2013 Author Share Posted September 21, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
raymsmith Posted September 21, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 21, 2013 Share Posted 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"; Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 21, 2013 Author Share Posted 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: > >> Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 21, 2013 Author Share Posted 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'); ?> Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 21, 2013 Author Share Posted September 21, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
JordanSmith1111 Posted September 22, 2013 Author Share Posted September 22, 2013 anybody ? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 23, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.