php - 在 DataTable 服务器端显示名称行而不是 id

Question

我在数据库 (MySql) 中有两个表，一个是国家名称，另一个是用户。现在，当一些用户被添加到网站时，他/她从他/她所在的位置选择..一切都很好，但由于用户数据库增长到 2,000 个用户，我需要强制更改为服务器端数据表以显示所有用户而不脚本强制卡住屏幕或网站。现在我看不到每个用户的国家/地区名称，只能看到 ID...所以我的问题是需要添加或更改什么才能看到国家/地区名称而不是 ID。

我关注这些帖子:

1. > how-relating-two-tables-to-show-name-row-and-not-the-id-in-mysqlpdo
2. > server-side

这是我的数据库:

|----------USERS-------------|
|---id-----------int---------|
|---nombres---varchar(150)---| is the user name
|---fnac-------datetime------| is the birthday
|---direccion----text--------| is the address
|---paisid--------int(3)-----| is the country id
|---foto-------varchar(50)---| is the user photo
|----------------------------|

|-----------PAISES-----------|
|---id---------int(11)-------| is the country id
|---paises---varchar(100)----| is the country name
|----------------------------|

这是我的代码(现在我没有使用它，因为我不知道我需要将它插入哪里):

<?php $query = "SELECT U.paisid, P.pais,
(SELECT pais FROM PAISES WHERE id = paisid)
AS pais
FROM USERS U
INNER JOIN PAISES P
ON  U.paisid = P.id";
$stmt = $conn->prepare( $query );   
$stmt->execute();
$rows = $stmt->fetchAll();
foreach( $rows as $row )
{ ?><?php echo $row['pais']; ?><?php } ?>

JS:

$('#userTabla').dataTable( {
    "bProcessing": true,
    "bServerSide": true,
    "sDom": "<'row-fluid'<'span6'l><'span6'f>r>t<'row-fluid'<'span12'i><'span12 center'p>>",
    "sAjaxSource": "includes/server_process.php",
    "aoColumns": [
        { "mData": "id" },
        { "mData": "nombres" },
        { "mData": "paisid" },
        {
            "mData": null,
            "sClass": "center",
            "sDefaultContent": "",
            "fnRender": function (o) {
            return '<a href="perfil.php?id=' + o.aData[0] + '" class="btn btn-success"><i class="icon-user icon-white"></i> Ver perfil</a> <a href="editar.php?id=' + o.aData[0] + '" class="btn btn-info"><i class="icon-edit icon-white"></i> Editar</a> <a id="' + o.aData[0] + '" class="btn btn-danger" href="#"><i class="icon-trash icon-white"></i> Borrar</a>'
        },
        "aTargets": [3]
        }
    ],
    "sPaginationType": "bootstrap",
    "oLanguage": {
    "sLengthMenu": "_MENU_ registros por pag"
    }
});

server_process.php(我更改示例数据的部分)

$aColumns = array( 'id', 'nombres', 'paisid');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "USERS";

/* Database connection information */
$gaSql['user']       = "dbuser";
$gaSql['password']   = "XXXXXX";
$gaSql['db']         = "dbmega";
$gaSql['server']     = "localhost";

Answer 1

 <?php 
$query = "SELECT USERS.* , PAISES.`paises` as countryName
FROM USERS 
INNER JOIN PAISES 
ON  paisid = id";

$stmt = $conn->prepare( $query );   
$stmt->execute();
$rows = $stmt->fetchAll();

 $returnArray = array();
foreach( $rows as $row )
{ 
returnArray[] = array($row['id'],$row['nombres'],$row['countryName']);
}

请将paisid类型改为int

var myDataTable = $('#userTabla').dataTable( {
    "bProcessing": true,
    "bServerSide": true,
    "sDom": "<'row-fluid'<'span6'l><'span6'f>r>t<'row-fluid'<'span12'i><'span12  center'p>>",
    "sAjaxSource": "includes/server_process.php",
    "aoColumns": [
        { "mData": "id" },
        { "mData": "nombres" },
        { "mData": "paisid" },
         {
           "mData": null,
           "sClass": "center",
           "sDefaultContent": "",
           "fnRender": function (o) {
           return '<a href="perfil.php?id=' + o.aData[0] + '" class="btn btn-success"><i class="icon-user icon-white"></i> Ver perfil</a> <a href="editar.php?id=' + o.aData[0] + '" class="btn btn-info"><i class="icon-edit icon-white"></i> Editar</a> <a id="' + o.aData[0] + '" class="btn btn-danger" href="#"><i class="icon-trash icon-white"></i> Borrar</a>'
         },
         "aTargets": [3]
        }
    ],
    "sPaginationType": "bootstrap",
    "oLanguage": {
    "sLengthMenu": "_MENU_ registros por pag"
    }
});

myDataTable.fnAddData( <? echo json_encode(returnArray[]); ?>);