Format of data required

[openqubit]

Hello,i am selecting data from mysql database using php and i can't seem to have it working correctly. I want to use server side pagination and i am not able to know the format of the data required.

So far this is the script

<?php
$limit = $_GET['limit'];
$offset = $_GET['offset'];

$sql = "SELECT code,name from country limit $limit,$offset";

function connectDB(){

        $server = "localhost";
        $user = "root";
        $pass = "123456";
        $bd = "world";

    $conexion = mysqli_connect($server, $user, $pass,$bd);

    return $conexion;
}

function disconnectDB($conexion){

    $close = mysqli_close($conexion);

    return $close;
}

            function getArraySQL($sql){

                $conexion = connectDB();

                    mysqli_set_charset($conexion, "utf8"); 

                if(!$result = mysqli_query($conexion, $sql)) die(); 

                $rawdata = array(); 
                $i=0;

                while($row = mysqli_fetch_array($result))
                {
                    $rawdata[$i] = $row;
                    $i++;
                }

                disconnectDB($conexion); 

                return $rawdata; 
            }       

        function cr($sql){
            $mysqli = new mysqli("localhost", "root", "123456", "world");
            if ($result = $mysqli->query($sql)) {

            $row_cnt = $result->num_rows;

            return $row_cnt;
        }
        }

        $myArray = getArraySQL($sql);
        $count = cr($sql);
        $test= array(
        'total' => $count, 
        'rows' => $myArray,
        );

        header('Content-Type: application/json');
        echo json_encode($test);
?>

The code gives this json which displays the table correctly

{"total":30,"rows":[{"0":"ASM","code":"ASM","1":"American Samoa","name":"American Samoa"},{"0":"ATA"
,"code":"ATA","1":"Antarctica","name":"Antarctica"},{"0":"ATF","code":"ATF","1":"French Southern territories"
,"name":"French Southern territories"},{"0":"ATG","code":"ATG","1":"Antigua and Barbuda","name":"Antigua
 and Barbuda"},{"0":"AUS","code":"AUS","1":"Australia","name":"Australia"},{"0":"AUT","code":"AUT","1"
:"Austria","name":"Austria"},{"0":"AZE","code":"AZE","1":"Azerbaijan","name":"Azerbaijan"},{"0":"BDI"
,"code":"BDI","1":"Burundi","name":"Burundi"},{"0":"BEL","code":"BEL","1":"Belgium","name":"Belgium"
},{"0":"BEN","code":"BEN","1":"Benin","name":"Benin"},{"0":"BFA","code":"BFA","1":"Burkina Faso","name"
:"Burkina Faso"},{"0":"BGD","code":"BGD","1":"Bangladesh","name":"Bangladesh"},{"0":"BGR","code":"BGR"
,"1":"Bulgaria","name":"Bulgaria"},{"0":"BHR","code":"BHR","1":"Bahrain","name":"Bahrain"},{"0":"BHS"
,"code":"BHS","1":"Bahamas","name":"Bahamas"},{"0":"BIH","code":"BIH","1":"Bosnia and Herzegovina","name"
:"Bosnia and Herzegovina"},{"0":"BLR","code":"BLR","1":"Belarus","name":"Belarus"},{"0":"BLZ","code"
:"BLZ","1":"Belize","name":"Belize"},{"0":"BMU","code":"BMU","1":"Bermuda","name":"Bermuda"},{"0":"BOL"
,"code":"BOL","1":"Bolivia","name":"Bolivia"},{"0":"BRA","code":"BRA","1":"Brazil","name":"Brazil"},
{"0":"BRB","code":"BRB","1":"Barbados","name":"Barbados"},{"0":"BRN","code":"BRN","1":"Brunei","name"
:"Brunei"},{"0":"BTN","code":"BTN","1":"Bhutan","name":"Bhutan"},{"0":"BVT","code":"BVT","1":"Bouvet
 Island","name":"Bouvet Island"},{"0":"BWA","code":"BWA","1":"Botswana","name":"Botswana"},{"0":"CAF"
,"code":"CAF","1":"Central African Republic","name":"Central African Republic"},{"0":"CAN","code":"CAN"
,"1":"Canada","name":"Canada"},{"0":"CCK","code":"CCK","1":"Cocos (Keeling) Islands","name":"Cocos (Keeling
) Islands"},{"0":"CHE","code":"CHE","1":"Switzerland","name":"Switzerland"}]}

I need help to complete the script and also make amends on the client side if i have to to enable me have the server side pagination that i want.

[dabros]

@openqubit - from the doco for data-pagination-side

With server-side pagination

Look at the examples: http://issues.wenzhixin.net.cn/bootstrap-table/

You can see what happens is, by default, the request url looks something like /data?order=asc&limit=10&offset=10

These param are sent and the response thats returned should match that subset of data requested.

Total is always total in DB, but just return needed subset.

If you need more help look at almost any php+mysql pagination script - exact same principle just be certain to encode and return as json that matches the format above (or use data-response-handler to tweak return before its loaded)

[openqubit]

From the client side this are the params being sent

limit   10
offset 0
order asc

This is my client side code

<table id="table"
 data-toggle="table"
 data-url="http://localhost:8080/index.php"
 data-height="400"
data-side-pagination="server"
 data-pagination="true"
data-page-list="[5, 10, 20, 50, 100, 200]"
data-search="true">
<thead>
<tr>
<th data-field="state" data-checkbox="true"></th>
<th data-field="id">Id</th>
<th data-field="code">Item Name</th>
<th data-field="name">Item Price</th>
</tr>
</thead>
</table>

@dabros I can get the table to display okay but its the search and pagination that are the issue.

[dabros]

@openqubit - what is the problem???

The actual pagination is in your server side script, nothing to do with what you are showing insofar as those param are actually sent.

ie, something like this (taken from #25)

$limit = $_GET['limit'];
$offset = $_GET['offset'];

... // http://www.tutorialspoint.com/php/mysql_select_php.htm

echo json_encode(array(
    'total' => 800, // select count(*) from table ...
    'rows' = $rows // select * from table limit ...
));

You still havent said what the actual issue is.

Please read my post above again and state what exactly the current output vs desired output.

[openqubit]

@dabros The problem is that the pagination is not working. If you try running the script you will see what i mean.

[dabros]

@openqubit - that first never had php when i saw it, try mentioning that if you update after or close to someone posting

[wenzhixin]

limit $limit,$offset";

correct to: limit $offset, $limit";

search: $search= $_GET['search'];

[dabros]

@openqubit - i have no active enviornment atm, even if i had your DB, so frankly i cant test that and I know the client side you copied it from works since i already showed an working example, up to you to check for consistency and console errors, or provide a fiddle.

What you need to look at is the current ouput vs desired output.

What is the ouput??

Quite obviously this means the data requests both ways, whether any console errors, and what "not working" actually means in exact details.

Is there a console error? Does running the generated sql direct produce an sql error? Is there an error msg in the data return? Is the data returning actually offset? Is the param sent being updated each time? ect.

There is no script that i can 'run' that uses all your parts.

If you want to show an example then use a fiddle: https://github.com/wenzhixin/bootstrap-table/blob/master/CONTRIBUTING.md#bug-reports

[openqubit]

@dabros Okay sir,understood.

[openqubit]

@wenzhixin My php script index.php

<?php

$limit = $_GET['limit'];
$offset = $_GET['offset'];
//$search = $_GET['search'];

$sql = "SELECT code,name from country order by code asc limit $offset, $limit";

function connectDB(){

        $server = "localhost";
        $user = "root";
        $pass = "123456";
        $bd = "world";

    $conexion = mysqli_connect($server, $user, $pass,$bd);

    return $conexion;
}

function disconnectDB($conexion){

    $close = mysqli_close($conexion);

    return $close;
}

function getArraySQL($sql){

    $conexion = connectDB();

        mysqli_set_charset($conexion, "utf8"); 

    if(!$result = mysqli_query($conexion, $sql)) die(); 

    $rawdata = array(); 
    $i=0;

    while($row = mysqli_fetch_array($result))
    {
        $rawdata[$i] = $row;
        $i++;
    }

    disconnectDB($conexion); 

    return $rawdata; 
}       
function cr($sql){
    $mysqli = new mysqli("localhost", "root", "123456", "world");
    if ($result = $mysqli->query($sql)) {

    $row_cnt = $result->num_rows;

    return $row_cnt;
}
}
        $myArray = getArraySQL($sql);
        $count = cr($sql);
        $test= array(
        'total' => $count, 
        'rows' => $myArray,
        );

        header('Content-Type: application/json');
        echo json_encode($test);
?>

and my html

<table id="table"
 data-toggle="table"
 data-url="http://localhost:8080/index.php"
data-height="400"
data-side-pagination="server"
data-pagination="true"
 data-page-list="[5, 10, 20, 50, 100, 200]"
data-search="true">
<thead>
<tr>
<th data-field="state" data-checkbox="true"></th>
<th data-field="id">Id</th>
<th data-field="code">Item Name</th>
<th data-field="name">Item Price</th>
</tr>
</thead>
</table>

That code produces this

Notice that the pagination is missing.

[dabros]

@openqubit - pagination isnt showing because its reading only 10 rows as 'total' index, so as i said earlier check the data return

Bit hard to tell from script, but it looks like your using the same sql to generate total as to generate return

That will not work

It should be obvious from the data return

You need to debug by hitting F12 and monitoring this

In the first post you share json which says total 30, that cant still be true as it would then say "showing 1 to 10 of 30 rows", with pagination to the right.

Total rows query cannot have a 'limit', else ofcourse num_rows will be broken.

[openqubit]

@dabros I have since corrected the pagination issues i was having. Thanks for the tip.