freemancomputer Posted July 7, 2012 Share Posted July 7, 2012 I have a web site that is showing a list with pagination. Right now its listing in one column. I would like it to show 50 links on a page in 2 columns. I think I know in theory how this should work but I'm not sure. This is the code that i have now, and this is my site http://www.drinktothecredits.com/movies.php <?php include"scripts/connect.php" ; mysql_connect('localhost',$username,$password); @mysql_select_db($database) or trigger_error("SQL", E_USER_ERROR); $sql = "SELECT COUNT(*) FROM movie WHERE type LIKE 'movie'"; $result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r[0]; $rowsperpage = 25; $totalpages = ceil($numrows / $rowsperpage); if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { $currentpage = (int) $_GET['currentpage']; } else { $currentpage = 1; } if ($currentpage > $totalpages) { $currentpage = $totalpages; } if ($currentpage < 1) { $currentpage = 1; } $offset = ($currentpage - 1) * $rowsperpage; $sql = "SELECT title FROM movie WHERE type LIKE 'movie' ORDER BY title LIMIT $offset, $rowsperpage" ; $result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR); ?> <div align="center"><span class="rulemain ">Movies</span></div><br> <?php while ($list = mysql_fetch_assoc($result)) { ?> <li> <a class="nav" href=/rules.php?title=<?php echo urlencode($list['title']); ?>><?php echo $list['title']; ?></a> </li> <?php } $range = 3; if ($currentpage > 1) { echo " <a class='nav' href='movies.php?currentpage=1'><<</a> "; $prevpage = $currentpage - 1; echo " <a class='nav' href='movies.php?currentpage=$prevpage'><</a> "; } for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { if (($x > 0) && ($x <= $totalpages)) { if ($x == $currentpage) { echo " [<b>$x</b>] "; } else { echo " <a class='nav' href='movies.php?currentpage=$x'>$x</a> "; } } } if ($currentpage != $totalpages) { $nextpage = $currentpage + 1; echo " <a class='nav' href='movies.php?currentpage=$nextpage'>></a> "; echo " <a class='nav' href='movies.php?currentpage=$totalpages'>>></a> "; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/ Share on other sites More sharing options...
Psycho Posted July 8, 2012 Share Posted July 8, 2012 But, how do you want them listed in those two columns? Scenario #1 1 2 2 4 3 6 etc. Scenario #2 1 25 3 26 5 27 etc. Scenario #1 is the easiest, but #2 is not too difficult Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1359992 Share on other sites More sharing options...
Psycho Posted July 8, 2012 Share Posted July 8, 2012 I screwed up scenario #1, it should have looked like this Scenario #1 1 2 3 4 5 6 etc. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1359994 Share on other sites More sharing options...
Psycho Posted July 8, 2012 Share Posted July 8, 2012 Here is a rewrite of your script for scenario #1. I made a lot of other improvements int he code as well. <?php //Configurable variables $rec_per_page = 50; //Should be an even multiple of $rec_per_page $cols_per_page = 2; $page_link_range = 3; //Connect to database include"scripts/connect.php" ; mysql_connect('localhost',$username,$password); @mysql_select_db($database) or trigger_error("SQL", E_USER_ERROR); //Run query to get total records $query = "SELECT COUNT(*) FROM movie WHERE type = 'movie'"; $result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR); $total_records = mysql_result($result, 0); $total_pages = ceil($total_records / $rec_per_page); //Set current page $current_page = (isset($_GET['currentpage'])) ? (int) $_GET['currentpage'] : 1; if ($current_page < 1) { $current_page = 1; } if ($current_page > $totalpages) { $current_page = $totalpages; } //Run query to get records for current page $offset = ($current_page - 1) * $rec_per_page; $query = "SELECT title FROM movie WHERE type = 'movie' ORDER BY title LIMIT $offset, $rec_per_page"; $result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR); //Loop through results to create output $movie_list = ''; $rec_idx= 0; while ($row = mysql_fetch_assoc($result)) { //Open new row if needed if($rec_idx% $cols_per_page == 0) { $movie_list .= "<tr>\n"; } $movie_url = urlencode($row['title']); $movie_text = htmlspecialchars($row['title']); $movie_list .= "<td> <a class='nav' href=/rules.php?title={$movie_url}>{$movie_text}</a></td>\n"; //Close current row if needed if($rec_idx% $cols_per_page == $cols_per_page-1) { $movie_list .= "</tr>\n"; } $rec_idx++; } //Close out last row if needed if($rec_idx% $cols_per_page != $cols_per_page-1) { $movie_list .= "</tr>\n"; } //Create pagination links $page_links = ''; //First and Prev pages if ($current_page > 1) { $prevpage = $current_page - 1; $page_links .= " <a class='nav' href='movies.php?currentpage=1'><<</a> \n"; $page_links .= " <a class='nav' href='movies.php?currentpage={$prevpage}'><</a> \n"; } //Surrounding pages $beg_page = max($current_page - $page_link_range, 1); $end_page = min($current_page + $range, $totalpages); for ($page = $beg_page; $page <= $end_page; $page++) { if($page == $current_page) { $page_links .= " [<b>{$page}</b>] \n"; } else { $page_links .= " <a class='nav' href='movies.php?currentpage={$page}'>{$page}</a> \n"; } } //Next and last pages if ($current_page != $totalpages) { $nextpage = $current_page + 1; $page_links .= " <a class='nav' href='movies.php?currentpage={$nextpage}'>></a> \n"; $page_links .= " <a class='nav' href='movies.php?currentpage={$totalpages}'>>></a> \n"; } ?> <table align="center"> <th class="rulemain" colspan="<?php echo $cols_per_page; ?>">Movies</th> <?php echo $movie_list; ?> <tr><td colspan="<?php echo $cols_per_page; ?>"><?php echo $page_links; ?></td></tr> </table> Or, replace the relevant section with this should give you scenario #2 //Loop through results to create output $row_content = array(); $rec_idx = 0; $rows_per_page = $rec_per_page / $cols_per_page; while ($row = mysql_fetch_assoc($result)) { $row_idx = $rows_per_page % $rec_idx; $movie_url = urlencode($row['title']); $movie_text = htmlspecialchars($row['title']); $row_content[$row_idx][] = "<td><a class='nav' href=/rules.php?title={$movie_url}>{$movie_text}</a></td>\n"; } $movie_list = ''; foreach($row_content as $row) { $movie_list .= "<tr>\n"; $movie_list .= "<tr>" . implode("</tr>\n<tr>", $row) . "</tr>"; $movie_list .= "</tr>\n"; } I did not test any of this so there may be some minor bugs Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1359997 Share on other sites More sharing options...
freemancomputer Posted July 8, 2012 Author Share Posted July 8, 2012 Thanks for your help but I am getting this error Fatal error: SQL in /home/drinktot/public_html/movies.php on line 46 This is likes 45-47 //Run query to get records for current page $offset = ($current_page - 1) * $rec_per_page; $query = "SELECT title FROM movie WHERE type = 'movie' ORDER BY title LIMIT $offset, $rec_per_page"; $result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR); I been scratching my head on this for about an hour today. Thanks again for your help Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360143 Share on other sites More sharing options...
xyph Posted July 8, 2012 Share Posted July 8, 2012 Echo $query. What does it display? Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360144 Share on other sites More sharing options...
freemancomputer Posted July 8, 2012 Author Share Posted July 8, 2012 SELECT title FROM movie WHERE type = 'movie' ORDER BY title LIMIT -50, 50 Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360160 Share on other sites More sharing options...
xyph Posted July 8, 2012 Share Posted July 8, 2012 -50 seems wrong, doesn't it? Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360161 Share on other sites More sharing options...
freemancomputer Posted July 9, 2012 Author Share Posted July 9, 2012 ok the $offset is coming back as -50 because $current_page is coming back as 0. Is $current_page just not being set as 1 for the first page? Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360168 Share on other sites More sharing options...
PFMaBiSmAd Posted July 9, 2012 Share Posted July 9, 2012 If you are using the code that Psycho posted, you will want to reverse the order of the following two lines so that if $totalpages is zero, you end up setting the $current_page to 1 - if ($current_page < 1) { $current_page = 1; } if ($current_page > $totalpages) { $current_page = $totalpages; } Should be - // limit the maximum first, then the minimum - if ($current_page > $totalpages) { $current_page = $totalpages; } if ($current_page < 1) { $current_page = 1; } Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360172 Share on other sites More sharing options...
freemancomputer Posted July 9, 2012 Author Share Posted July 9, 2012 That makes sense and works, but this brings up another problem, it has the arrows at the bottom that are supost to point to the 2nd or 3rd page but after clicking the arrows it stays on the first page, the url says its on page 2. I haven't looked at this yet but ill take any ideas. http://drinktothecredits.com/movies.php Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360175 Share on other sites More sharing options...
xyph Posted July 9, 2012 Share Posted July 9, 2012 Again, echo your $query It's simple debugging. If something goes wrong, the first thing you check is your input. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360179 Share on other sites More sharing options...
PFMaBiSmAd Posted July 9, 2012 Share Posted July 9, 2012 The variable holding the total page count is $total_pages. You need to change all occurrences of $totalpages to $total_pages. You also need to be developing and debugging your code on a system with php's error_reporting set to E_ALL and display_errors set to ON so that php will help you by reporting and displaying all the errors it detects. You will save a ton of time. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360180 Share on other sites More sharing options...
freemancomputer Posted July 9, 2012 Author Share Posted July 9, 2012 Thanks for the help and tips, I didn't even think about using echo and i kept passing over the $totalpages. Its all up and running now. Thanks again. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360186 Share on other sites More sharing options...
Psycho Posted July 9, 2012 Share Posted July 9, 2012 If you are using the code that Psycho posted, you will want to reverse the order of the following two lines so that if $totalpages is zero, you end up setting the $current_page to 1 - if ($current_page < 1) { $current_page = 1; } if ($current_page > $totalpages) { $current_page = $totalpages; } Should be - // limit the maximum first, then the minimum - if ($current_page > $totalpages) { $current_page = $totalpages; } if ($current_page < 1) { $current_page = 1; } Maybe I'm just not seeing it, but I don't think the order of those two would matter. One would only do something if $current_page was less than 1 and the other would only do something if $current_page was greater than $total_pages. Assuming $total_pages is 1 or greater, only one of those conditions could ever be true - not both. So, the order of the two would not matter. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360187 Share on other sites More sharing options...
PFMaBiSmAd Posted July 9, 2012 Share Posted July 9, 2012 If there are zero rows matching the WHERE clause, the $totalpages/$total_pages calculation results in a zero. The code with the $current_page > $totalpages comparison last will set $current_page to zero, producing a query error because a negative value in the LIMIT is a sql error. By reversing the two comparisons, $current_page will be left as a 1, a valid page number, and the query won't fail due to a sql syntax error. Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360199 Share on other sites More sharing options...
Psycho Posted July 9, 2012 Share Posted July 9, 2012 If there are zero rows matching the WHERE clause, the $totalpages/$total_pages calculation results in a zero. The code with the $current_page > $totalpages comparison last will set $current_page to zero, producing a query error because a negative value in the LIMIT is a sql error. By reversing the two comparisons, $current_page will be left as a 1, a valid page number, and the query won't fail due to a sql syntax error. OK, gotcha. But, I would think the better solution is to fix $total_pages. Since we want the current page to default to 1, even when there are no records, then $total_pages should always be at least 1. I would have done this $total_pages = max(ceil($total_records / $rec_per_page), 1); Quote Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360204 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.