vLIB & mysql & browse pages

[lumi]

Hi

First of all let me say that vLIB is a really cool thingie - makes the daily work a lot easier

I got the following Code:

Code: [Select]

tmpl-newLoopentries;
    while row = mysql_fetch_array result, MYSQL_ASSOC
    {
        tmpl-addRow row;
    }
    tmpl-addLoop;
It works as ment, but it would be even better if the output would be divided in pages (ex. 1 [2] 3 4 5... page 2 is active). I really became desperate because i cant figure out how the template and the code must look like (I already tried the array_push function but i didnt get it work with vLIB)

Please excuse my poor english (Im Austrian) ... however any help is highly appreciated
Thank you in advance

Kind regards
Hannes

[releasedj]

English is fine.

I have written some code below but havent tested it, so take it with a pinch of salt.

I would say that the best way of paginating is to control the records displayed by LIMITing the SQl.

Use the following process:
- determine how many records to show in each page, i.e. 20
- do a count(*) for the statement that you require to determine how many records are in the database.
- do ceil(num_results / rows_per_page) to determine the amount of pages
- create a seperate loop for the number of pages in your template.
- perform your SQL statement with the correct limit clause on the end to limit the records returned to those required.

Code: [Select]

?php

// stuff to connect to mysql
// ...

// set default variables

rows_per_page = 20;

if empty_GETpage || is_int_GETpage _GETpage = 1;

// get number of records in query
num_reqults_query = mysql_querySELECT COUNT* FROM people;
num_results    = mysql_resultnum_reqults_query, 0;

// following sets current page to the maximum possible amount in case
// someone changes page number by hand in adress bar
if _GETpage-1 * rows_per_page+1 num_results _GETpage = ceilnum_results / rows_per_page;

// how many pages? where to start and stop limit
num_pages = ceilnum_results / rows_per_page;
limit_start = rows_per_page * _GETpage-1;
limit_stop = rows_per_page;

// and now we get the relevant records
results_query = mysql_querySELECT id, first_name, last_name FROM people LIMIT .limit_start.,.limit_stop;

// now for the template stuff
// first we set the loop for the page numbers
tmpl-newLooppages;
for page=1; page = num_pages; page++
{
    tmpl-addRowarraypage = page,
                        is_current = page == _GETpage ? 1 0
                        ;
}
tmpl-addLoop;

// now we add the records
tmpl-newLooppeople;
while row = mysql_fetch_arrayresult_query, MYSQL_ASSOC
{
    tmpl-addRow row;
}
tmpl-addLoop;

// do rest of tmpl stuff
?


Template
table
    tr
        thID/th
        thFirst Name/th
        thLast Name/th
    /tr

    tmpl_loop name=people
    tr
        tdtmpl_var name=id //td
        tdtmpl_var name=first_name //td
        tdtmpl_var name=last_name //td
    /tr
    /tmpl_loop

    -- pages --

    tr
        td colspan=3
        tmpl_loop name=pages
            tmpl_if name=is_current
                tmpl_var name=page /
            tmpl_else /
                a href=?page=tmpl_var name=page /
                    tmpl_var name=page /
                /a
            /tmpl_if
        /tmpl_loop
        /td
    /tr
/table
Let me know if this helps or if something is confusing.

Regards,
Kelvin

[lumi]

Thank you very much for your reply, Kelvin!
I think I got the loop-thing now :)

You got only one typo in your code:
> while ($row = mysql_fetch_array ($result_query, MYSQL_ASSOC))

-->

> while ($row = mysql_fetch_array ($results_query, MYSQL_ASSOC))


Regards
Hannes

[area32]

if (empty(_GET[page]) || is_int(_GET[page])) _GET[page] = 1;

The code doesnt work because _GET[page] isnt an integer but a numeric string.

Change it by

Code: [Select]

if empty_GETpage || is_numeric_GETpage _GETpage = 1;
Now it should work fine.

[ClausVB]

I have completed the whole example.

All posts (vlib.clausvb.de/forum) are displayed with 20 rows per page.

Regards,
Claus