nezbo Posted July 4, 2007 Share Posted July 4, 2007 Hi all I have this code and i want it to be seperated the results over more than one page? with a "<prev 1 2 3 4 5 next>" etc.... Any advise welcome.. $result = mysql_query("SELECT * FROM job WHERE Completed='Yes' ORDER BY JobID DESC")or die(mysql_error()); echo "<table border=1 align = center width=90%><tr>"; echo "<tr><td colspan=10 align=center><h2>Check Jobs</h2><tr><td>"; echo " <tr bgcolor = #acacac> <td>Call ID <td>PCIU Number <td>Reported By <td>Passed To <td>Job Completed <td>Problem Discription <td>Outcome <td>Date <td>Time <td>Site Name"; $bgcolour = "#ffffff"; while ($row = mysql_fetch_array($result)) { if ($bgcolour == "#ffffff") { echo "<tr bgcolor = #ffffff> <td><a href=editJob.php?job_identifier=$row[JobID] >$row[JobID] </a></td> <td align=left>$row[PCIUno]</td> <td align=left>$row[RepBy]</td> <td align=left>$row[PassedTo]</td> <td align=left>$row[Completed]</td> <td align=left>$row[Problem]</td> <td align=left>$row[OutCome]</td> <td align=left>$row[callDate]</td> <td align=left>$row[callTime]</td> <td align=left>$row[site]</td> </tr>"; $bgcolour = "#ececec"; } else { echo "<tr bgcolor = #ececec> <td><a href=editJob.php?job_identifier=$row[JobID] >$row[JobID] </a></td> <td align=left>$row[PCIUno]</td> <td align=left>$row[RepBy]</td> <td align=left>$row[PassedTo]</td> <td align=left>$row[Completed]</td> <td align=left>$row[Problem]</td> <td align=left>$row[OutCome]</td> <td align=left>$row[callDate]</td> <td align=left>$row[callTime]</td> <td align=left>$row[site]</td> </tr>"; $bgcolour = "#ffffff"; } } Quote Link to comment Share on other sites More sharing options...
RichardRotterdam Posted July 4, 2007 Share Posted July 4, 2007 I see you use mysql. you could use the LIMIT function in mysql look up some info on that Quote Link to comment Share on other sites More sharing options...
nezbo Posted July 4, 2007 Author Share Posted July 4, 2007 Cheers Would the "LIMIT 20" function not just give me the top 20 rows. I see you use mysql. you could use the LIMIT function in mysql look up some info on that Quote Link to comment Share on other sites More sharing options...
Yesideez Posted July 4, 2007 Share Posted July 4, 2007 You can add another value to LIMIT like this: LIMIT a,b Start reading "a" number of rows in and limit to show "b" number of rows. Quote Link to comment Share on other sites More sharing options...
nezbo Posted July 4, 2007 Author Share Posted July 4, 2007 Hi again i have looked at Limit and i have come up with this code and i am geting an error like this : crap!!!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 It is something to do with the LIMIT $start, $diaplay part of the code, it is like the start isnt geting a value. any ideas? $display = 20; $query = mysql_query("SELECT COUNT(*) FROM job WHERE Completed='Yes' ORDER BY JobID DESC")or die("oh dear" . mysql_error()); if (isset($_GET['np'])) { $num_pages = $_GET['np']; } if ($num_records > $display) { $num_pages = ceil ($num_records/$display); } else { $num_pages = 1; } if (isset($_GET['s'])) { $start = $_GET["s"]; } else { $start = 0; } if ($num_pages > 1) { echo "<br /><p>"; $current_page = ($start/$display) + 1; if ($current_page != 1) { echo "<a href=checkCompletedJobs.php?s=" . ($display * ($i - 1)) . "$np=" . $num_pages . $i . "</a>"; } for ($i = 1; $i <= $num_pages; $i++) { if($i != $current_page) { echo "<a href=checkCompletedJobs.php?s=" . ($start - $display) . "$np=" . $num_pages . ">Previous</a>"; } else { echo $i . ""; } } if ($current_page != $num_pages) { echo "<a href=checkCompletedJobs.php?s=" . ($start + $display) . "$np=" . $num_pages . ">Next</a>"; } } $completedJobs = mysql_query("SELECT * FROM job WHERE Completed='Yes' ORDER BY JobID DESC LIMIT $start, $diaplay")or die("crap!!!" . mysql_error()); Quote Link to comment Share on other sites More sharing options...
nezbo Posted July 4, 2007 Author Share Posted July 4, 2007 Sorry school boy error, it was just a typo but it is not showing the pages ? Hi again i have looked at Limit and i have come up with this code and i am geting an error like this : crap!!!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 It is something to do with the LIMIT $start, $diaplay part of the code, it is like the start isnt geting a value. any ideas? $display = 20; $query = mysql_query("SELECT COUNT(*) FROM job WHERE Completed='Yes' ORDER BY JobID DESC")or die("oh dear" . mysql_error()); if (isset($_GET['no problem'])) { $num_pages = $_GET['no problem']; } if ($num_records > $display) { $num_pages = ceil ($num_records/$display); } else { $num_pages = 1; } if (isset($_GET['s'])) { $start = $_GET["s"]; } else { $start = 0; } if ($num_pages > 1) { echo "<br /><p>"; $current_page = ($start/$display) + 1; if ($current_page != 1) { echo "<a href=checkCompletedJobs.php?s=" . ($display * ($i - 1)) . "$no problem=" . $num_pages . $i . "</a>"; } for ($i = 1; $i <= $num_pages; $i++) { if($i != $current_page) { echo "<a href=checkCompletedJobs.php?s=" . ($start - $display) . "$no problem=" . $num_pages . ">Previous</a>"; } else { echo $i . ""; } } if ($current_page != $num_pages) { echo "<a href=checkCompletedJobs.php?s=" . ($start + $display) . "$no problem=" . $num_pages . ">Next</a>"; } } $completedJobs = mysql_query("SELECT * FROM job WHERE Completed='Yes' ORDER BY JobID DESC LIMIT $start, $diaplay")or die("crap!!!" . mysql_error()); Quote Link to comment Share on other sites More sharing options...
Yesideez Posted July 4, 2007 Share Posted July 4, 2007 Your typo - was it $diaplay instead of $display? Quote Link to comment Share on other sites More sharing options...
nezbo Posted July 4, 2007 Author Share Posted July 4, 2007 yep eeek I now have it working 'of a fasion', it is only displaying one page at a time i.e. <prev 5 next> insted of <prev 1 2 3 4 5 etc... and there is something weared going on, i have about 80 records and it will now only display the top 'x' if i change the $display = 20; to 3 it will let me click next and work well ish but when i upload it to the server where there are loads of records it dosnt diaplay <prev 1 next> does this make sence? Neil Your typo - was it $diaplay instead of $display? Quote Link to comment Share on other sites More sharing options...
Yesideez Posted July 4, 2007 Share Posted July 4, 2007 This is an entire script I wrote for one of my sites. All this does is list all the topics in a particular forum and supplies Next, Previous and page numbers that can be clicked. Should give you an idea... <?php /* forum.php ** ** Created: 11-Jun-2007 (Zeb) ** Edited : 11-Jun-2007 (Zeb) *****************************************************************************/ define('INT_PAGESIZE',30); //NUMBER OF TOPICS TO SHOW PER PAGE define('INT_POSTDELAY',10); //NUMBER OF SECONDS BETWEEN NEW POSTS include("includes/dbconfig.php"); include("includes/validateuser.php"); include("includes/bbcode.php"); $clsMySQL=new clsMySQL(); $clsHTML=new clsHTML(); $intDelID=intval($_GET['del']); $intPageNum=intval($_GET['page']); switch (strtolower($_REQUEST['type'])) { case 'banter':$strForumType='banter';$strAlt='Banter';break; case 'blackmarket':$strForumType='blackmarket';$strAlt='Black Market';break; default:$strForumType='game';$strAlt='Game'; } /************************************* ** PLAYER WANTS TO POST A NEW TOPIC ** *************************************/ if ($_POST['subpost']) { $newPost=false; $strNewTitle=trim($_POST['strnewtitle']); $txtNewMessage=trim($_POST['txtnewmessage']); if (empty($strNewTitle)) { $strMsg=$clsHTML->makeMessage('You can\'t make a post with an empty title!',1); } else { if (empty($txtNewMessage)) { $strMsg=$clsHTML->makeMessage('You can\'t make a post with an empty message!',1); } else { if (mysql_query("INSERT INTO `forum_".$strForumType."` (`userid`,`title`,`message`,`lastreply`) VALUES ('".$user['userid']."','".$strNewTitle."','".$txtNewMessage."','".time()."')")) { mysql_query("UPDATE `users` SET `".$strForumType."mute`='".(time()+INT_POSTDELAY)."' WHERE `userid`='".$user['userid']."'"); //ADD POST DELAY LIMIT $strNewTitle=''; $txtNewMessage=''; $newPost=true; } else { $strMsg=$clsHTML->makeMessage('ERROR: Unable to add the new topic',1); } } } } /********************************** ** STAFF WANTS TO DELETE A TOPIC ** **********************************/ if ($intDelID>0) { if ($user['level']>0) { $delTopic=mysql_fetch_assoc(mysql_query("SELECT `type` FROM `forum_".$strForumType."` WHERE `topicid`='".$intDelID."'")); if ($delTopic['type']=='1normal') { mysql_query("DELETE FROM `forum_".$strForumType."` WHERE `topicid`='".$intDelID."' LIMIT 1"); mysql_query("DELETE FROM `forum_".$strForumType."_replies` WHERE `topicid`='".$intDelID."'"); } else { $strMsg=$clsHTML->makeMessage('That topic must be downgraded before it can be deleted',1); } } } /******************************************************* ** COUNT NUMBER OF PAGES AND MAKE HTML FOR PAGE INDEX ** *******************************************************/ $getPages=mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS `total` FROM `forum_".$strForumType."`")); $intPageCount=ceil($getPages['total']/INT_PAGESIZE); if ($intPageNum>$intPageCount) {$intPageNum=$intPageCount;} if ($intPageNum<1) {$intPageNum=1;} if ($intPageCount>1) { $htmPageList=''; for ($i=1;$i<=$intPageCount;$i++) { $htmPageList.=($i==$intPageNum ? ' <span style="font: 10px verdana; color: #000000; background-color: #ffffff; font-weight: bold">'.$i.'</span>' : ' <a href="forum.php?type='.$strForumType.'&page='.$i.'" class="nav">'.$i.'</a>'); } } /************************* ** BUILD LIST OF TOPICS ** *************************/ $htmTopics=''; $query=mysql_query("SELECT * FROM `forum_".$strForumType."` ORDER BY `type` DESC, `lastreply` DESC LIMIT ".(($intPageNum-1)*INT_PAGESIZE).",".INT_PAGESIZE); while ($fetch=mysql_fetch_assoc($query)) { $countReplies=mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS `total` FROM `forum_".$strForumType."_replies` WHERE `topicid`='".$fetch['topicid']."'")); switch ($fetch['type']) { case '2sticky':$cssColor=' style="background-color: #778877"';$strTitle='<a href="forumreplies.php?type='.$strForumType.'&topic='.$fetch['topicid'].'" class="url2b">'.htmlentities($fetch['title']).'</a>';break; case '3important':$cssColor=' style="background-color: #997777"';$strTitle='<a href="forumreplies.php?type='.$strForumType.'&topic='.$fetch['topicid'].'" class="url2b">'.htmlentities($fetch['title']).'</a>';break; default:$cssColor='';$strTitle='<a href="forumreplies.php?type='.$strForumType.'&topic='.$fetch['topicid'].'" class="url2b">'.htmlentities($fetch['title']).'</a>'; } if ($user['level']>0) { //IS USER A STAFF MEMBER? if ($fetch['type']!='1normal'||$fetch['userid']==0) { //YES $htmAction='<td'.$cssColor.'><span style="font: 10px verdana; color: #888888"> DEL </span></td>'; } else { $htmAction=''; //YES } } else { //NOT STAFF MEMBER $htmAction='<td'.$cssColor.' style="font:10px verdana; color: #888888"> DEL </td>'; } if ($fetch['userid']==0) { //IS TOPIC SYSTEM-GENERATED? $strTitle=$fetch['title']; //YES (CAN'T BE LOCKED) } else { $strTitle.=($fetch['locked']=='yes' ? ' <em>locked</em>' : ''); //NO } $htmTopics.='<tr>'.$htmAction.'<td width="100%"'.$cssColor.'>'.$strTitle.'</td><td'.$cssColor.'>'.($fetch['userid']==0 ? 'System' : $clsHTML->makeProfileLink($clsMySQL->getUsername($fetch['userid']))).'</td><td align="right"'.$cssColor.'>'.number_format($countReplies['total']).'</td></tr>'."\n"; } ?> <html> <head> <title>Drug Culture</title> <link rel="stylesheet" type="text/css" href="main.css" /> </head> <?php include("includes/headermenu.php"); ?> <table width="90%" cellspacing="1" cellpadding="0" class="tblbg"> <tr><th colspan="4"><img src="gfx/hdr_<?=$strForumType?>forum.gif" alt="<?=$strAlt?> Forum" width="150" height="16" /></th></tr> <?=$htmTopics?> </table> <?php if (!empty($htmPageList)) { ?> <br /> <table cellspacing="1" cellpadding="0" class="tblbg"> <tr> <td style="font: 10px verdana; color: #000000"><?=($intPageNum<2 ? '<<<' : '<a href="forum.php?type='.$strForumType.'&page='.($intPageNum-1).'" class="nav"><<<</a>')?></td> <td style="font: 10px verdana; color: #ffffff">Page<?=$htmPageList?></td> <td style="font: 10px verdana; color: #000000"><?=($intPageNum==$intPageCount ? '>>>' : '<a href="forum.php?type='.$strForumType.'&page='.($intPageNum+1).'" class="nav">>>></a>')?></td> </tr> </table> <?php } ?> <?php $intPostMute=$user[$strForumType.'mute']-time(); if ($intPostMute>0||$newPost) { echo '<br />'.$clsHTML->makeMessage('You will be free to post again in '.($newPost ? INT_POSTDELAY : $intPostMute).' second'.($intPostMute==1 ? '' : 's')); } else { ?> <br /> <table cellspacing="1" cellpadding="0" class="tblbg"> <tr><th colspan="2">New Topic</th></tr><input type="hidden" name="type" value="<?=$strForumType?>" /> <tr><td width="30">Title</td><td><input type="text" name="strnewtitle" maxlength="80" size="60" value="<?=stripslashes($strNewTitle)?>" class="gadtext" /></td></tr> <tr><td colspan="2"><textarea name="txtnewmessage" style="width: 500px" rows="10" class="gaddrop"><?=stripslashes($txtNewMessage)?></textarea></td></tr> <tr><td colspan="2" align="right"><input type="submit" name="subpost" value="Post" class="gadbutton" /></td></tr> </table> <?php } ?> <?php include("includes/footer.php"); ?> 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.