Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm a novice with php and mysql so please be kind. The problem is I'm calling a database which has various states open, closed, pending etc, all this works fine.

The problem is when I click next on my paging links the page just reloads to the start page with all states visible and the first 6 rows visible.

My code seems to work fine as the url of next, prev page as a different start number for the start of the database query eg (http:/wordpress/?page_id=1072?s=6&np=3).

I was wondering if this could be to with wordpress?

help much appreciated.

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) ;
mysql_select_db (DB_NAME) ;

/*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>";


$self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ;
/* 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';
}


$display = 6;

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

} else {

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


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

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

}
else {
        $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";
        $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['s'])) {
    $start = $_GET['s'];
} 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?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
        }
        for ($i = 1; $i <= $num_pages; $i++) {
            if ($i != $current_page) {
                echo '<a href="?page_id=1072?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' .$i .'</a> ';
            } else {
                echo $i . ' ';
            }
        }
        if ($current_page !=$num_pages) {
            echo '<a href="' . $query_result . '?s=' . ($start + $display) . '&np=' . $num_pages . '">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';
}



?>
share|improve this question
1  
What makes this a WordPress question? (aside from the fact you're doing this coding inside a WordPress site)... It looks more like a PHP/MySQL to me, i'd encourage you to update the question so it's more centric to WordPress else it runs the risk of being closed as off-topic. –  Mark Duncan Aug 1 '11 at 16:00
add comment

1 Answer

Lee, looks like you need to look at OFFSET in MySQL, there is a pretty good explantation here.

You can just multiply the offset by the page number -1 so page 2 will only offset 10 instead of 20 and so on.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.