mysql problem with status and pagination_问答_开发者

I am calling a mysql query which as various different states for different results (closed open, pending, all) and paginating and all is fine apart from when i click on the next page link or any other page I lose the status set eg 'closed' and all results are shown.

I'm a bit of a novice with php and mysql, so don't really no how to implement this so that somehow a session is stored perhaps so that once pagination is used the status is still set.

please see code.

Any help really appreciated.

<?php 


/* Connect to DB */

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Sorry, there seems to be a technical problem at the moment - please try again later');
mysql_select_db (DB_NAME) OR die ('Sorry, there seems to be a technical problem at the moment - please try again later');
开发者_运维技巧



/* Write view state control buttons */

echo "<div id=\"shownew\"><table class=\"showhide\"><tr><td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI']). "\"><INPUT TYPE=\"submit\" name=\"showopen\" VALUE=\"\" class=\"show_open\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showpending\" VALUE=\"\" class=\"show_pending\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showclosed\" VALUE=\"\" class=\"show_closed\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"show_all\" VALUE=\"\" class=\"show_all\"></form></p></td></tr></table></div>";


/* Status set code */

if (isset($_POST['close'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Closed', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
    ;

}

if (isset($_POST['open'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Open', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_ami'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending AMI', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_arp'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending ARP', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

/* View state set code */

if (isset($_POST['showopen'])) {
    $viewstate='open';
}

if (isset($_POST['showpending'])) {
    $viewstate='pending';
}


if (isset($_POST['showclosed'])) {
    $viewstate='closed';
}

if (isset($_POST['show_all'])) {
    $viewstate='all';
}
function supportquery($viewstate) {

 $display = $start+6;

        if (isset($_GET['np'])) {
    $num_pages = $_GET['np'];
} else {

    $where = "1"; // default
    switch ($viewstate) {
        case "open": $where = "status='Open'"; break;
        case "pending": $where = "status LIKE 'Pending%'"; break;
        case "closed": $where = "status='Closed'"; break;
    }
    $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
        message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number ASC";
    $query_result = mysql_query ($query); 
    $num_records = @mysql_num_rows ($query_result);
    if ($num_records > $display) {
        $num_pages = ceil ($num_records/$display);
    } else {
        $num_pages = 1;
    }
}


if (isset($_GET['startoftable'])) {
    $start = $_GET['startoftable'];
} else {
    $start = 0;

}

    $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev ORDER BY ticket_number ASC LIMIT $start, $display";
    if ($viewstate=='open') {

        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Open' ORDER BY ticket_number ASC LIMIT $start, $display";
        }
            elseif ($viewstate=='pending') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status LIKE 'Pending%' ORDER BY ticket_number ASC LIMIT $start, $display";
        }
        elseif ($viewstate=='closed') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC LIMIT $start, $display";}
        $result = @mysql_query ($query);
        $num = mysql_num_rows ($result);

    if ($num > 0) {

    if ($num_pages > 1) {
        echo '<p>';
        $current_page = ($start/$display) + 1;

        if ($current_page != 1) {
            echo '<a href="?page_id=1072&amp;startoftable=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
        }
        for ($i = 1; $i <= $num_pages; $i++) {
            if ($i != $current_page) {
                echo '<a href="?page_id=1072&amp;startoftable=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' .$i .'</a> ';
            } else {
                echo $i . ' ';
            }
        }
        if ($current_page != $num_pages) {

            echo '<a href="?page_id=1072&amp;startoftable=' . ($start + $display) . '&np=' . $num_pages . '&status=' . $viewstate .'">Next</a> '; 
        }
        echo '</p><br />'; 

    }


     if ($result) {
         echo '
    <div id="supviewwrapperheader">
    <div id="supviewticket"><p>Ticket</p></div> 
    <div id="supviewfirst"><p>First Name</p></div>
    <div id="supviewlast"><p>Last Name</p></div>
    <div id="supviewemail"><p>Email</p></div>
    <div id="supviewproduct"><p>Product</p></div>
    <div id="supviewretailer"><p>Retailer</p></div>
    <div id="supviewdop"><p>D.O.P.</p></div>
    <div id="supviewmessage"><p>Message</p></div>
    <div id="supviewaddress"><p>Address</p></div>
    <div id="supviewcreated"><p>Date created</p></div>
    <div id="supviewstatus"><p>Status</p></div>
    <div id="supviewbuttons"><p></p></div>
    </div>
    ';

    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        echo "
        <div id=\"supviewwrapper\" class=\"".($row[10])."\">
        <div id=\"supviewticket\"><p><a name=\"a".$row[0]."\"></a>$row[0]</p></div>
        <div id=\"supviewfirst\"><p>$row[1]</p></div>
        <div id=\"supviewlast\"><p>$row[2]</p></div>
        <div id=\"supviewemail\"><p>$row[3]</p></div>
        <div id=\"supviewproduct\"><p>$row[4]</p></div>
        <div id=\"supviewretailer\"><p>$row[5]</p></div>
        <div id=\"supviewdop\"><p>$row[6]</p></div>
        <div id=\"supviewmessage\"><p>$row[7]</p></div>
        <div id=\"supviewaddress\"><p>$row[8]</p></div>
        <div id=\"supviewcreated\"><p>$row[9]</p></div>
        <div id=\"supviewstatus\"><p>$row[10]</p></div>
        <div id=\"supviewbuttons\"><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."#a".$row[0]."\"><input type=\"hidden\" name=\"close_row\" value=\"".($row[0])."\" /><input type=\"hidden\" name=\"viewstate\" value=\"".$viewstate."\" /><INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br /><INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br /><INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br /><INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br /><INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\"></form></p></div></div>";
    }
     }

    mysql_free_result ($result);

     }  



if (is_null($viewstate)) {
    $viewstate='all';
}

}

supportquery($viewstate);

?>

The solution here is to pass your $viewstate to all your pagination links. Example for Previous Link, add &viewstate=$viewstate

    if ($current_page != 1) {
        echo '<a href="?page_id=1072&amp;startoftable=' . ($start - $display) . '&np=' . $num_pages . '&viewstate=' .$viewstate. '">Previous</a> ';
    }

Then, in your view state set code, add $_GET to your conditions so you can determine what is the current view state.

/* View state set code */

if (isset($_POST['showopen']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'open')) {
    $viewstate='open';
}

if (isset($_POST['showpending']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'pending')) {
    $viewstate='pending';
}


if (isset($_POST['showclosed']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'closed')) {
    $viewstate='closed';
}

if (isset($_POST['show_all']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'all')) {
    $viewstate='all';
}

Hope this works.