Paginal conclusion from MySQL

So, you many times saw, how melon are deduced page by page and you always wanted to make so on the site. Today I shall tell as to draw a paginal conclusion from MySQL.

For the beginning there is some theory. What it is necessary to make? First, whether it is necessary to check up the page of viewing is chosen. Then it is necessary to count up whence to deduce{remove} recordings. It is done{made} simply: the current page we multiply on quantity{amount} of recordings on page. At last, it is necessary to make SQL-search to a database and to deduce{remove} result. And eventually to write nivigaciju.

Let's start. Execute SQL-search:

CREATE TABLE tbl_books (

  book_id int (11) NOT NULL auto_increment,

  book_name varchar (50) NOT NULL default ",

  book_cash float NOT NULL default '0',

  PRIMARY KEY (book_id)

) TYPE=MyISAM;

INSERT INTO tbl_books VALUES (1, ' the Book number{room} 1 ', '2');

INSERT INTO tbl_books VALUES (2, ' the Book number{room} 2 ', '3');

INSERT INTO tbl_books VALUES (3, ' the Book number{room} 3 ', '4');

INSERT INTO tbl_books VALUES (4, ' the Book number{room} 4 ', '5');

INSERT INTO tbl_books VALUES (5, ' the Book number{room} 5 ', '6');

INSERT INTO tbl_books VALUES (6, ' the Book number{room} 6 ', '7');

INSERT INTO tbl_books VALUES (7, ' the Book number{room} 7 ', '8');

INSERT INTO tbl_books VALUES (8, ' the Book number{room} 8 ', '9');

INSERT INTO tbl_books VALUES (9, ' the Book number{room} 9 ', '10');

INSERT INTO tbl_books VALUES (10, ' the Book number{room} 10 ', '11');

INSERT INTO tbl_books VALUES (11, ' the Book number{room} 11 ', '12');

INSERT INTO tbl_books VALUES (12, ' the Book number{room} 12 ', '13');

INSERT INTO tbl_books VALUES (13, ' the Book number{room} 13 ', '14');

INSERT INTO tbl_books VALUES (14, ' the Book number{room} 14 ', '15');

INSERT INTO tbl_books VALUES (15, ' the Book number{room} 15 ', '16');

I suggest all to issue as functions obrabotchikov. We shall create function page () which processes the data on requested number{room} of page:

<? php

function page ()

{

    if (empty ($ _GET ["page"])) {

        $page = 0;

} else {

if (! is_numeric ($ _GET ["page"])) die (" the Wrong format of number{room} of page! ");

        $page = $ _GET ["page"];

}

        return $page;

}

What does she do{make}? Whether checks the page and if at her there are letters deduces the message on a mistake is chosen. Further we shall make SQL-search:

function sql_query ($onpage, $page, $table)

{

    $begin = $page * $ onpage; // whence to start

        $sql = " SELECT * FROM ". $table. "LIMIT." $begin. ",". $onpage;

        $result = mysql_query ($sql) or die (mysql_error ());

        return $result;

}

Function of navigation:

function navigation ($onpage, $page, $table)

{

        $return = null;

    $count = mysql_query (" SELECT COUNT (*) FROM tbl_books ") or die (mysql_error ());

        $count = mysql_fetch_array ($count);

        $count = $count [0];

        $pages = $count / $ onpage;

        if ($page! == 0) {

            $prev = " <A HREF = "? page = ". ($ page-1). " "> *lt; </A> ";

} else {

            $prev = "<";

}

        if ($page <round ($pages-1)) {

            $next = " <A HREF = "? page = ". ($ page+1). " "> *gt; </A> ";

} else {

            $next = ">";

}

        for ($i=0; $i <$pages; $i ++)

        {

            if ($i == $page) {

                $return. = "[". ($ i+1). "]";

} else {

                $return. = " <A HREF = "? page = ". $ i. ""> [". ($i+1). "] </A> ";

}

}

        return $prev. $ return. $ next;

}

With functions have finished! How them to use?

$onpage = 4; // recordings on page

$table = "tbl_books"; // from what table

mysql_connect ("localhost", "root", "pass") or die (mysql_error ()); // a connection to a DB

mysql_select_db ("test"); // a choice of a DB

$page = page (); // we determine page

$result = sql_query ($onpage, $page, $table); // sql - search

while ($data = mysql_fetch_array ($result)) // a cycle of a conclusion

{

    // The pattern of a conclusion needs to be changed

        // $data ["nazvanie_polja"] - the maintenance{contents} of a field

    echo $data ["book_id."] "<B.>" $data ["book_name."] " </B> <BR> ";

        echo $data ["book_cash."] " $ <P> ";

}

$navigation = navigation ($onpage, $page, $table); // we shall define{determine} navigation

echo $navigation; // we shall deduce{remove} her{it}

mysql_close (); // disconnect from a DB

?>