1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with PHP MYSQL filtering/pagination

Discussion in 'PHP' started by bananabread_, Jan 28, 2012.

  1. #1
    Im trying to filter the results my browse page displays via $_POST and form on the page.
    The results filter in its current state, and display the correct amount of page numbers, but when I try and change page it defaults back to the base select query.

    Heres my rendering/filtering code;
    //pagination
    //set max items per page
    $per_page = 12;
    $pages_query = mysql_query("SELECT COUNT('product_id') FROM stock");
    //identify number of pages
    $pages = ceil(mysql_result($pages_query, 0) / $per_page);
    //read page no# from url, if not set, set to 1
    $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
    $start = ($page - 1) * $per_page;
    //filters
    if(isset($_POST['submit']) ){ //if filters have been set    
        if(isset($_POST['category']) && $_POST['category'] !== "all") { //if category has been set, and is not "all"
            if(isset($_POST['orderby']) && $_POST['ascdesc'] !== "") { //and if orderby has been set
                $categoryFilter = $_POST['category'];
                $orderbyFilter = $_POST['orderby'];
                $ascdescFilter = $_POST['ascdesc'];
                $pages_query = mysql_query("SELECT COUNT('product_id'), category FROM stock WHERE category='$categoryFilter'");
                $pages = ceil(mysql_result($pages_query, 0) / $per_page);
                $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
                $start = ($page - 1) * $per_page;
                $query = "SELECT product_id, product_name, product_price, category FROM stock WHERE category='$categoryFilter' ORDER BY $orderbyFilter $ascdescFilter LIMIT $start, $per_page";
            } else { //else if just category has been set
            $categoryFilter = $_POST['category'];
            $pages_query = mysql_query("SELECT COUNT('product_id'), category FROM stock WHERE category='$categoryFilter'");
            $pages = ceil(mysql_result($pages_query, 0) / $per_page);
            $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
            $start = ($page - 1) * $per_page;
            $query = "SELECT product_id, product_name, product_price, category FROM stock WHERE category='$categoryFilter' LIMIT $start, $per_page";
        }}
        if($_POST['category'] == "all") { //if category is "all"
            if(isset($_POST['orderby']) && $_POST['ascdesc'] !== "") { //and if orderby has been set
                $orderbyFilter = $_POST['orderby'];
                $ascdescFilter = $_POST['ascdesc'];
                $pages_query = mysql_query("SELECT COUNT('product_id'), category FROM stock");
                $pages = ceil(mysql_result($pages_query, 0) / $per_page);
                $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
                $start = ($page - 1) * $per_page;
                $query = "SELECT product_id, product_name, product_price, category FROM stock ORDER BY $orderbyFilter $ascdescFilter LIMIT $start, $per_page";
            } else { //else if just category is set, and is "all"
            $pages_query = mysql_query("SELECT COUNT('product_id'), category FROM stock");
            $pages = ceil(mysql_result($pages_query, 0) / $per_page);
            $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
            $start = ($page - 1) * $per_page;
            $query = "SELECT product_id, product_name, product_price, category FROM stock LIMIT $start, $per_page";
        }} //else if blank filter
    } else { $query = "SELECT product_id, product_name, product_price FROM stock LIMIT $start, $per_page"; }
    //set filtered/unfiltered $query as query
    
    //product rendering
    $sql = mysql_query($query);
    $counter = 0;//initialise counter
    $gridDisplay = '<table width="100%" border="0" cellspacing="2" cellpadding="2" class="browseinnertable">';
    $pageDisplay = '<p>';
        while($row = mysql_fetch_array($sql))//query into variables
            {
                $product_id = $row["product_id"];
                $product_name = $row["product_name"];
                $product_price = $row["product_price"];
                if($counter % 4 == 0)//if counter is divisable by 4
                    {//insert new row
                        $gridDisplay .= '<tr>
                  <td height="100"><a href="product.php?id='.$product_id.'"><img src="images/'.$product_id.'.jpg" width="100" height="100" alt="'.$product_id.'"/></a>
                  <p><strong>' . $product_name . '</strong></p>
                  <p>£' . $product_price . '</p>
                  <p><a href="product.php?id='.$product_id.'">more info</a></p></td>'; 
                    } else {//if not, continue entering cells
                        $gridDisplay .= '<td height="100"><a href="product.php?id='.$product_id.'"><img src="images/'.$product_id.'.jpg" width="100" height="100" alt="'.$product_id.'" /></a>
                        <p><strong>' . $product_name . '</strong></p>
                  <p>£' . $product_price . '</p>
                  <p><a href="product.php?id='.$product_id.'">more info</a></p></td>';
                    }
                $counter++;//increment counter
            }
    $gridDisplay .= '</tr></table>';//add table close code to string
    
    //page next rendering
    if($page < $pages){
        $pageDisplay .= '<div align="right"><a href="?page='.($page+1).'">Next</a></div>';
    }
    //page number rendering
    $pageDisplay .= '<div align="center">';
    if($pages >= 1 && $page <= $pages) {
        for($p = 1; $p <= $pages; $p++) {
            if($p == $page) {
                //renders current page number in bold
                $pageDisplay .= '<strong><a href="?page='.$p.'"> '.$p.' </a></strong>|';    
            } else {
            $pageDisplay .= '<a href="?page='.$p.'"> '.$p.' </a>|';    
            }
        }
    } 
    //page previous rendering
    $pageDisplay .= '</div>';
    if($page <= $pages && $page > 1) {
        $pageDisplay .= '<div align="left"><a href="?page='.($page-1).'">Previous</a>';
    }
    $pageDisplay .= '</p>';
    PHP:
    and the filter form;
    <form action="browse.php" method="post" name="filterForm">
                  <p>Category</p>
                <select name="category">
                <p><option selected="selected" value="all">All</option></p>
                <?php echo $categoryList; ?>
                </select>
                <strong><p>Order by;</p></strong>
                <p><input type="radio" name="orderby" value="product_name">Name</input>
                <p><input type="radio" name="orderby" value="product_price">Price</input>
                <p><input type="radio" name="orderby" value="qty_sold">Popularity</input>
                <p><select name="ascdesc">
                <option selected="selected" value=""></option>
                <option value="asc">Ascending</option>
                <option value="desc">Descending</option>
                </select>
                <p><input name="submit" type="submit" value="Filter"/></input>
              </form>
    HTML:
    I think the problem is when the hyperlink passes the $_GET page variable.
    Ive tried passing the $_POST variables into $_SESSION then filtering via those but that wont work either.

    Can anyone help me with this problem?

    Thanks in advance.
    SEMrush
     
    Solved! View solution.
    Last edited: Jan 28, 2012
    bananabread_, Jan 28, 2012 IP
    SEMrush
  2. #2
    The problem is obviously because when you click the link you're submitting a GET page request and losing the post data. Your idea with the session would work if executed properly.

    Another option is to submit the search data through the URL parameters (so you can retrieve them via $_GET). That way they're easily transferable to links with a page variable.
     
    Alex Roxon, Jan 28, 2012 IP
  3. bananabread_

    bananabread_ Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Tried transferring the post data to session like this;
    if(isset($_POST['submit']) ){
        session_name("filter");
        session_start();
        $_SESSION['category'] = $_POST['category'];
        $_SESSION['orderby'] = $_POST['orderby'];
        $_SESSION['ascdesc'] = $_POST['ascdesc'];
    PHP:
    Then filtering via those instead, but it still does the same when I change page. If I echo out session at the top of my code it just unsets when I change page.

    Am I missing something?

    EDIT: Fixed it. Just had to change some ifs around, my mistake. Thanks for your help :)
     
    Last edited: Jan 28, 2012
    bananabread_, Jan 28, 2012 IP