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> "; } ?> 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 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. 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 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 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? 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 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? 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? 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; } 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 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. 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. 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. 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. 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. 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); Link to comment https://forums.phpfreaks.com/topic/265368-2-columns-with-lists/#findComment-1360204 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.