search
HomeDatabaseMysql TutorialHow to obtain record data from MySQL database in PHP

Get the total number of records in the data table

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select count(*) from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result = $mysqli->query($sql);//以索引的方式返回一个结果集
list($rowCount)=$result->fetch_row();
echo &#39;记录总数:&#39;.$rowCount;
$result->close();
$mysqli->close();
?>

It can also be obtained like this:

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select count(*) from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result = $mysqli->query($sql);//以索引的方式返回一个结果集
$row = $result->fetch_row();
echo &#39;记录总数:&#39;.$row[0];
$result->close();
$mysqli->close();
?>

Other forms: ($row['total'] replaced by $ row[0] can also be used)

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select count(*) as total from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result = $mysqli->query($sql);//以索引的方式返回一个结果集
$row = $result->fetch_array(MYSQLI_BOTH);
echo &#39;记录总数:&#39;.$row[&#39;total&#39;];
$result->close();
$mysqli->close();
?>

I checked the PHP operation manual and found that there are too many similar functions, which means that PHP has many processing methods for the same needs.

Get the records of the database query table and return it in JSON format

<?php
$sql = "select * from druserlist";//查询语句
$res = $mysqli->query($sql);//执行查询
$result = array();//准备一个空数组
while ($row = $res->fetch_assoc()){
    //对结果集进行逐行取值并压入到数组中
    array_push($result,$row);
}
echo json_encode($result);//返回前端JSON格式数据
?>

Returned data:

[
  {
    "C01": "00924001",
    "C02": "经理办01",
    "C03": "1",
    "C04": "普通用户",
    "C05": "1"
  },
  {
    "C01": "00924002",
    "C02": "经理办02",
    "C03": "2",
    "C04": "普通用户",
    "C05": "1"
  },
  {
    "C01": "00923128",
    "C02": "人事科01",
    "C03": "1",
    "C04": "普通用户",
    "C05": "人事科的张小勇"
  }
]

Get the paging record data of the data table and return it in JSON format (Take LayUI's paging data as an example)

<?php
$page  = isset($_POST[&#39;page&#39;]) ? intval($_POST[&#39;page&#39;]) : 1;//获取页数
$limit = isset($_POST[&#39;limit&#39;]) ? intval($_POST[&#39;limit&#39;]) : 10;//获取每页的显示记录数
$offset = ($page-1)*$limit;//计算起始位置
 
require &#39;linkConfig.php&#39;;//连接数据库
$sql1 = "select count(*) from druserlist";
$result1 = $mysqli->query($sql1);
$rowCount = $result1->fetch_row();
 
$returnArr[&#39;code&#39;]=0;
$returnArr[&#39;msg&#39;]="";
$returnArr[&#39;count&#39;]=$rowCount[0];//总记录数
 
$sql2 = "select * from druserlist order by C01 limit $offset,$limit";
$res = $mysqli->query($sql2);
$result = array();
while ($row = $res->fetch_assoc()){
    array_push($result,$row);
}
$returnArr[&#39;data&#39;]=$result;
echo json_encode($returnArr);//返回JSON格式数据
$res->free();
$mysqli->close();
?>

Returned data:

{
  "code": 0,
  "msg": "",
  "count": "12",
  "data": [
    {
      "C01": "00101078",
      "C02": "HaoR",
      "C03": "2",
      "C04": "管理员",
      "C05": "1"
    },
    {
      "C01": "00323007",
      "C02": "研究中心01",
      "C03": "1",
      "C04": "管理员",
      "C05": "1"
    },
    {
      "C01": "00616001",
      "C02": "财务科01",
      "C03": "1",
      "C04": "管理员",
      "C05": "1"
    },
    {
      "C01": "00616002",
      "C02": "财务科02",
      "C03": "1",
      "C04": "管理员",
      "C05": "1"
    },
    {
      "C01": "00616003",
      "C02": "财务科03",
      "C03": "1",
      "C04": "管理员",
      "C05": "1"
    },
    {
      "C01": "00923127",
      "C02": "admin",
      "C03": "1",
      "C04": "管理员",
      "C05": "系统管理员"
    },
    {
      "C01": "00923128",
      "C02": "人事科01",
      "C03": "1",
      "C04": "普通用户",
      "C05": "人事科的张小勇"
    },
    {
      "C01": "00923129",
      "C02": "人事科02",
      "C03": "1",
      "C04": "管理员",
      "C05": "1"
    }
  ]
}

can return different data styles according to the front-end paging requirements. Once you know the PHP processing, it will be easier to handle. .

PHP’s processing of mysql database query result set.

⑴ fetch_array()

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
while($row = $result->fetch_array()){
    $rows[] = $row;
}
foreach($rows as $row){
    echo $row[&#39;C01&#39;].&#39;  &#39;.$row[&#39;C02&#39;].&#39;<br>&#39;;
}
$result->close();
$mysqli->close();
?>

Results returned line by line:

00924001 Manager Office 01
00924002 Manager Office 02
00923128 Personnel Department 01

Return JSON format:

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
while($row = $result->fetch_array()){
    $rows[] = $row;
}
echo json_encode($rows);//返回JSON格式数据
$result->close();
$mysqli->close();
?>

Returned results:

[
  {
    "0": "00924001",
    "1": "经理办01",
    "C01": "00924001",
    "C02": "经理办01"
  },
  {
    "0": "00924002",
    "1": "经理办02",
    "C01": "00924002",
    "C02": "经理办02"
  },
  {
    "0": "00923128",
    "1": "人事科01",
    "C01": "00923128",
    "C02": "人事科01"
  }
]

It can be seen from the above output that the output is in the form of numbers and field names. According to the PHP operation manual, you can get the record value by giving the parameters of the fetch_array() function, that is, the following three outputs are the same.

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
//第一种输出
while($row = mysqli_fetch_array($result, MYSQLI_NUM)){
    echo $row[0].&#39;  &#39;.$row[1].&#39;<br>&#39;;
}
//第二种输出
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
    echo $row[&#39;C01&#39;].&#39;  &#39;.$row[&#39;C02&#39;].&#39;<br>&#39;;
}
//第三种输出
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
    echo $row[0].&#39;  &#39;.$row[&#39;C02&#39;].&#39;<br>&#39;;
}
$result->close();
$mysqli->close();
?>

The three outputs are the following results:

00924001 Manager Office 01
00924002 Manager Office 02
00923128 Personnel Department 01

⑵ fetch_row()

The above output can be performed using the following method:

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
while ($row = $result->fetch_row()) {
    printf ("%s %s <br>", $row[0], $row[1]);
}
$result->close();
$mysqli->close();
?>

⑶fetch_assoc()

The above output can be performed using the following method :

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
while ($row = $result->fetch_assoc()) {
    printf ("%s %s <br>", $row["C01"], $row["C02"]);
}
$result->close();
$mysqli->close();
?>

⑷ fetch_all()

Similar to fetch_array, there are parameter selections, namely: MYSQLI_ASSOC, MYSQLI_NUM or MYSQLI_BOTH. The default is MYSQLI_BOTH.

<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
$rows=$result->fetch_all(MYSQLI_NUM);
$r=0;
while ( $r < mysqli_num_rows($result) ) {
    printf ("%s %s <br>", $rows[$r][0], $rows[$r][1]);
    $r++;
}
$result->close();
$mysqli->close();
?>
<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
$rows=$result->fetch_all(MYSQLI_ASSOC);
$r=0;
while ( $r < mysqli_num_rows($result) ) {
    printf ("%s %s <br>", $rows[$r][&#39;C01&#39;], $rows[$r][&#39;C02&#39;]);
    $r++;
}
$result->close();
$mysqli->close();
?>
<?php
require &#39;linkConfig.php&#39;;//根据连接信息连接数据库
$sql = "select C01,C02 from druserlist where C04=&#39;普通用户&#39;";//查询数据表
$result=$mysqli->query($sql);//以索引的方式返回一个结果集
$rows=$result->fetch_all(MYSQLI_BOTH);
$r=0;
while ( $r < mysqli_num_rows($result) ) {
    printf ("%s %s <br>", $rows[$r][0], $rows[$r][&#39;C02&#39;]);
    $r++;
}
$result->close();
$mysqli->close();
?>

The output of the above three pieces of code is the same.

The above is the detailed content of How to obtain record data from MySQL database in PHP. For more information, please follow other related articles on the PHP Chinese website!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
MySQL String Types: Storage, Performance, and Best PracticesMySQL String Types: Storage, Performance, and Best PracticesMay 10, 2025 am 12:02 AM

MySQLstringtypesimpactstorageandperformanceasfollows:1)CHARisfixed-length,alwaysusingthesamestoragespace,whichcanbefasterbutlessspace-efficient.2)VARCHARisvariable-length,morespace-efficientbutpotentiallyslower.3)TEXTisforlargetext,storedoutsiderows,

Understanding MySQL String Types: VARCHAR, TEXT, CHAR, and MoreUnderstanding MySQL String Types: VARCHAR, TEXT, CHAR, and MoreMay 10, 2025 am 12:02 AM

MySQLstringtypesincludeVARCHAR,TEXT,CHAR,ENUM,andSET.1)VARCHARisversatileforvariable-lengthstringsuptoaspecifiedlimit.2)TEXTisidealforlargetextstoragewithoutadefinedlength.3)CHARisfixed-length,suitableforconsistentdatalikecodes.4)ENUMenforcesdatainte

What are the String Data Types in MySQL?What are the String Data Types in MySQL?May 10, 2025 am 12:01 AM

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose

How to Grant Permissions to New MySQL UsersHow to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideHow to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools