2 columns with lists

[freemancomputer]

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> ";
} 
?>

[Psycho]

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

[Psycho]

I screwed up scenario #1, it should have looked like this

Scenario #1

1   2
3   4
5   6
etc.

[Psycho]

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

[freemancomputer]

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

[xyph]

Echo $query. What does it display?

[freemancomputer]

SELECT title FROM movie WHERE type = 'movie' ORDER BY title LIMIT -50, 50

 

[xyph]

-50 seems wrong, doesn't it?

[freemancomputer]

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?

[PFMaBiSmAd]

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; }

 

[freemancomputer]

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

[xyph]

Again, echo your $query

 

It's simple debugging. If something goes wrong, the first thing you check is your input.

[PFMaBiSmAd]

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.

[freemancomputer]

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.

[Psycho]

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.

[PFMaBiSmAd]

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.

[Psycho]

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);