Page 1 of 1

Pagination/Conditional problem

Posted: Wed Jul 05, 2006 3:19 pm
by Ross
Hey, I'm trying to display 5 db rows per page, sorted in descending order, and greater than the current date (ie: haven't come to pass). My table manages events.

The problem I am having is that when I go to throw out all the rows with the date already past, things are not working as expected. When I view the script in the browser no entries appear. I have used this same conditional in other scripts and it has worked fine. Also, if I take this conditional out of the script, it works fine. Can anyone see where I am going wrong here? Is there a better way to sort limit only future dates? Thanks for the help, code listed below. The conditional I mentioned is inside the while loop.

Ross

Code: Select all

<?php 


    @mysql_connect(localhost, user, password) or die("ERROR--CAN'T CONNECT TO SERVER"); 
    @mysql_select_db("database") or die("ERROR--CAN'T CONNECT TO DB");
	
	$limit          = 5;                
    //$query_count    = "SELECT count(*) FROM events";     
	$query_count    = "SELECT * FROM events";     
    $result_count   = mysql_query($query_count);     
    $totalrows      = mysql_num_rows($result_count);  
	
	$curDate = date('Y-m-d', gmmktime() + (-6 * 60 * 60)); 

    if(empty($page)){ 
        $page = 1; 
    } 
         

    $limit_value = $page * $limit - ($limit);  
    $query  = "SELECT * FROM events ORDER BY DATE ASC LIMIT $limit_value, $limit";         
    $result = mysql_query($query) or die("Error: " . mysql_error());  

    if(mysql_num_rows($result) == 0){ 
        echo("Nothing to Display!"); 
    } 

    $bgcolor = "#E0E0E0";

    echo("<table>"); 
     
    while($row = mysql_fetch_array($result)){
		if(strtotime($row["DATE"]) >= strtotime(date('Y-m-d', gmmktime() + (-6 * 60 * 60)))){
			if ($bgcolor == "#E0E0E0"){ 
				$bgcolor = "#FFFFFF"; 
			}else{ 
				$bgcolor = "#E0E0E0"; 
			} 
	
			echo("<tr bgcolor=".$bgcolor."><td>"); 
			echo($row["DATE"]); 
			echo("</td><td>"); 
			echo($row["ESTABLISHMENT"]); 
			echo("</td><td>"); 
			echo($row["CITY"]);
			echo("</td><td>"); 
			echo($row["TIME"]);
			echo("</td></tr>"); 
		}
    } 

    echo("</table>"); 

    if($page != 1){  
        $pageprev = --$page; 
         
        echo("<a href=\"pager.php?page=$pageprev\">PREV</a> ");  
    }else{ 
        //echo("PREV".$limit." "); 
		echo("PREV  ");
    } 

    $numofpages = $totalrows / $limit;  
     
    for($i = 1; $i <= $numofpages; ++$i){ 
        if($i == $page){ 
            echo($i." "); 
        }else{ 
            echo("<a href=\"pager.php?page=$i\">$i</a> "); 
        } 
    } 


    if(($totalrows % $limit) != 0){ 
        if($i == $page){ 
            echo($i." "); 
        }else{ 
            echo("<a href=\"pager.php?page=$i\">$i</a> "); 
        } 
    } 

    if(($totalrows - ($limit * $page)) > 0){ 
        $pagenext = ++$page; 
          
        echo("<a href=\"pager.php?page=$pagenext\">NEXT</a>");  
    }else{ 
        //echo("NEXT".$limit);  
		echo("  NEXT");  
    } 
     
    mysql_free_result($result); 

?>

Re: Pagination/Conditional problem

Posted: Wed Jul 05, 2006 4:23 pm
by Yossarian
Ross wrote:Hey, I'm trying to display 5 db rows per page, sorted in descending order, and greater than the current date (ie: haven't come to pass). My table manages events.

The problem I am having is that when I go to throw out all the rows with the date already past, things are not working as expected.
Perhaps I am not understanding your problem, but shouldnt you make sure that your SQL select only gets events whose date fields are greater than today?

You seem to be doing the filtering in PHP, is there a reason for this?

(if not your select should contain something like WHERE date>now() depending on how you store the dates).

success

Posted: Wed Jul 05, 2006 10:31 pm
by Ross
Thanks for the help. By changing my sql query to this:

Code: Select all

$query  = "SELECT * FROM chipEvents WHERE DATE>=now() ORDER BY DATE ASC LIMIT $limit_value, $limit";
I am now achieving the results I wanted. Thank you again.

Ross

And now for the next problem with this code:

Posted: Thu Jul 06, 2006 7:46 pm
by Ross
I'm having another problem now. The pagination works but the current page is not unlinked. The page directly before the current page is unlinked.

Example:

When the url is: "http://www.belmontbands.com/Chip_Housto ... php?page=3"...
Page two is unlinked while page three is an active link and when clicked brings up page three.

Any idea how to fix this problem. I've been staring at this code block for the past few hours with no luck.

I think the error is within this loop.

Code: Select all

for($i = 1; $i <= $numofpages; ++$i){ 
        if($i <= ceil($numofpages)){
			if($i == $page){ 
				echo($i." "); 
			}else{ 
				echo("<a href=\"pager.php?page=$i\">$i</a> "); 
			} 
		}
    }