How to: implement a basic php paging with mysql, ajax AND linq_问答_开发者

Am trying to implement a simple mysql data paging using php, ajax and (the real kicker) linq. There is a linq implementation for php, called phplinq

I have been able to implement the paging without the linq part, and the relevant code is this: (split into several files)

pagination.php, where it all starts.

Pagination with Jquery, Ajax, PHP
```
     <script type="text/javascript"
```
src="js/jquery1_5_2_min.js">

load_data.php, the file where the data is all pulled

    $query_pag_data = "SELECT id as msg_id,name as message from student LIMIT $start, $per_page";
    $result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());
    $msg = "";
    while ($row = mysql_fetch_array($result_pag_data)) {
        $htmlmsg=htmlentities($row['message']);
        $msg .= "<li><b>" . $row['msg_id'] . "</b> " . $htmlmsg . "</li>";
    }
    $msg = "<div class='data'><ul>" . $msg . "</ul></div>"; // Content for Data

    /* --------------------------------------------- */
    $query_pag_num = "SELECT COUNT(*) AS count FROM student";
    $result_pag_num = mysql_query($query_pag_num);
    $row = mysql_fetch_array($result_pag_num);
    $count = $row['count'];
    $no_of_paginations = ceil($count / $per_page);

    /* ---------------Calculating the starting and endign values for the loop----------------------------------- */
    if ($cur_page >= 7) {
        $start_loop = $cur_page - 3;
        if ($no_of_paginations > $cur_page + 3)
            $end_loop = $cur_page + 3;
        else if ($开发者_开发知识库cur_page <= $no_of_paginations && $cur_page > $no_of_paginations - 6) {
            $start_loop = $no_of_paginations - 6;
            $end_loop = $no_of_paginations;
        } else {
            $end_loop = $no_of_paginations;
        }
    } else {
        $start_loop = 1;
        if ($no_of_paginations > 7)
            $end_loop = 7;
        else
            $end_loop = $no_of_paginations;
    }
    /* ----------------------------------------------------------------------------------------------------------- */
    $msg .= "<div class='pagination'><ul>";

    // FOR ENABLING THE FIRST BUTTON
    if ($first_btn && $cur_page > 1) {
        $msg .= "<li p='1' class='active'>First</li>";
    } else if ($first_btn) {
        $msg .= "<li p='1' class='inactive'>First</li>";
    }

    // FOR ENABLING THE PREVIOUS BUTTON
    if ($previous_btn && $cur_page > 1) {
        $pre = $cur_page - 1;
        $msg .= "<li p='$pre' class='active'>Previous</li>";
    } else if ($previous_btn) {
        $msg .= "<li class='inactive'>Previous</li>";
    }
    for ($i = $start_loop; $i <= $end_loop; $i++) {

        if ($cur_page == $i)
            $msg .= "<li p='$i' style='color:#fff;background-color:#006699;' class='active'>{$i}</li>";
        else
            $msg .= "<li p='$i' class='active'>{$i}</li>";
    }

    // TO ENABLE THE NEXT BUTTON
    if ($next_btn && $cur_page < $no_of_paginations) {
        $nex = $cur_page + 1;
        $msg .= "<li p='$nex' class='active'>Next</li>";
    } else if ($next_btn) {
        $msg .= "<li class='inactive'>Next</li>";
    }

    // TO ENABLE THE END BUTTON
    if ($last_btn && $cur_page < $no_of_paginations) {
        $msg .= "<li p='$no_of_paginations' class='active'>Last</li>";
    } else if ($last_btn) {
        $msg .= "<li p='$no_of_paginations' class='inactive'>Last</li>";
    }
    $goto = "<input type='text' class='goto' size='1' style='margin-top:-1px;margin-left:60px;'/><input type='button' id='go_btn' class='go_button' value='Go'/>";
    $total_string = "<span class='total' a='$no_of_paginations'>Page <b>" . $cur_page . "</b> of <b>$no_of_paginations</b></span>";
    $msg = $msg . "</ul>" . $goto . $total_string . "</div>";  // Content for pagination
    echo $msg;
}

dbconnect.php, containing login stuff
paginator.js, the file with javascript functions.

$(document).ready(function(){ function loading_show(){ $('#loading').html("").fadeIn('fast'); } function loading_hide(){ $('#loading').fadeOut('fast'); }
function loadData(page){ loading_show();
$.ajax ({ type: "POST", url: "load_data.php", data: "page="+page, success: function(msg) { $("#container").ajaxComplete(function(event, request, settings) { loading_hide(); $("#container").html(msg); }); } }); } loadData(1); // For first time page load default results $('#container .pagination li.active').live('click',function(){ var page = $(this).attr('p'); loadData(page);
```
});           
$('#go_btn').live('click',function(){
    var page = parseInt($('.goto').val());
    var no_of_pages = parseInt($('.total').attr('a'));
    if(page != 0 && page <= no_of_pages){
        loadData(page);
    }
    else{
        alert('Enter a PAGE between 1 and '+no_of_pages);
        $('.goto').val("").focus();
        return false;            
    }
});
```
});

And finally, table.css, the file with some basic styling.

body{
    width: 800px;
    margin: 0 auto;
    padding: 0;
}
#loading{
    width: 100%;
    position: absolute;
    top: 100px;
    left: 100px;
    margin-top:200px;
}
#container .pagination ul li.inactive,
#container .pagination ul li.inactive:hover{
    background-color:#ededed;
    color:#bababa;
    border:1px solid #bababa;
    cursor: default;
}
#container .data ul li{
    list-style: none;
    font-family: verdana;
    margin: 5px 0 5px 0;
    color: #000;
    font-size: 13px;
}

#container .pagination{
    width: 800px;
    height: 25px;
}
#container .pagination ul li{
    list-style: none;
    float: left;
    border: 1px solid #006699;
    padding: 2px 6px 2px 6px;
    margin: 0 3px 0 3px;
    font-family: arial;
    font-size: 14px;
    color: #006699;
    font-weight: bold;
    background-color: #f2f2f2;
}
#container .pagination ul li:hover{
    color: #fff;
    background-color: #006699;
    cursor: pointer;
}
.go_button
{
background-color:#f2f2f2;border:1px solid #006699;color:#cc0000;padding:2px 6px 2px 6px;cursor:pointer;position:absolute;margin-top:-1px;
}
.total
{
float:right;font-family:arial;color:#999;
}

if youre going to replicate this, there is a css folder and a js folder and i have downloaded the jquery 1.5.2 from google. you can replace the line

<script type="text/javascript" src="js/jquery1_5_2_min.js"></script>

in pagination.php with

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>

Also, the table being used is

CREATE TABLE student    (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(150)
);
my

objective here is get rid of all direct mysql query calls inside the files with whatever i can put there using linq. I have used linq extensively with .NET 4.0, so basically what am trying to do here is abstracting the data access layer, as if i were using .NET objects to interface with data instead of pure mysql query calls.

Does this make sense? my end goal is being able to replace all mysql query calls with phplinq and completely isolate the data access code.(If linq is an overkill or somehow inadequate, ill drop it) I dont want to go with any ORM here because am going to heavily customize the grid (In this example there is just a list) and the future Data Access Layer

Am not that proficient with php, so please dont direct me to extensive reading. something i could pick up and play with would be much better

Well I guess you're having a problem with fetching records from database.. I have worked in php paging alot and trust me php works better with mySQL queries than LINQ.. You can check out a blog post on LINQ Paging here . Its using ASP.net but im pretty sure you can get atleast and idea to do your work.. Whenever I go for paging with LINQ i usually use this query

(from ac in dc.students where StudentIDs.Contains(ac.Ids) select ac).Skip((PageNumber - 1) * NumberOfRecordsInPage).Take(NumberOfRecordsInPage)

Hope it helps :)