获取数据表的全部信息并返回json编码
<?php
$host = "127.0.0.1"; // 服务器地址
$username = "root"; // 用户名
$password = ""; // 密码
$databaseName = "raceinfo"; // 数据库名
// 进行查询操作的sql语句
$query = "select * from runners";
// 连接数据库
$conn = db_connection($host, $username, $password, $databaseName);
// 执行查询操作
$result = mysqli_query($conn, $query);
// 创建一个空数组,为转换成json格式而准备
$runners = array();
// 遍历查询得到的每条记录
while($row = mysqli_fetch_array($result, MYSQL_ASSOC)){
// print_r($row);
// echo "<br>";
// 把当前记录放到$runners数组中,数组中的每一个元素也是一个数组
array_push($runners, array("fname" => $row["first_name"]));
}
echo json_encode(array("runners" => $runners));
// 该函数的作用是连接数据库,如果连接成功返回连接对象,否则输出错误提示信息,结束该php文件的运行
function db_connection($host, $username, $password, $databaseName){
$conn = mysqli_connect($host, $username, $password, $databaseName);
if (!$conn) {
die("Could not connect to database: " . mysqli_connect_errno());
}
return $conn; // 返回连接对象
}
?>
根据分页返回页面数量或者当前页信息
<?php
if(isset($_POST["index"])){
$pageIndex = $_POST["index"];
}
if(isset($_POST["size"])){
$pageSize = $_POST["size"];
}
$host = "127.0.0.1"; // 服务器地址
$username = "root"; // 用户名
$password = ""; // 密码
$databaseName = "raceinfo"; // 数据库名
// 首次查询,只需要得到记录数量即可
if($pageIndex==0){
$query = "select count(*) from runners";
// 连接数据库
$conn = db_connection($host, $username, $password, $databaseName);
// 执行查询操作
$result = $conn->query($query);
echo json_encode(array("total"=>mysqli_fetch_array($result)['0']));
}
else{
// 设置偏移记录数,即设置开始行号
$startRowNum = ($pageIndex-1) * $pageSize;
$numOfRows = $pageSize; // 返回的最多行数
$query = "select * from runners order by runner_id asc limit ".$startRowNum.",". $numOfRows;
// 连接数据库
$conn = db_connection($host, $username, $password, $databaseName);
// 执行查询操作
$result = $conn->query($query);
$runners = array();
while($row = mysqli_fetch_array($result)){
array_push($runners, $row);
}
echo json_encode($runners);
}
function db_connection($host, $username, $password, $databaseName){
$conn = mysqli_connect($host, $username, $password, $databaseName);
// 下面两条语句用来防止中文乱码
mysqli_query($conn,"set character set 'utf8'");
mysqli_query($conn,"set names 'utf8'");
if (mysqli_connect_errno()) {
echo "Could not connect to database.";
exit();
}
return $conn; // 返回连接对象
}
?>