search
HomeBackend DevelopmentPHP ProblemConvert php query data table to array

When using MySQL as the database in PHP, it is often necessary to convert the queried data table data into array format. This method is very common because arrays are one of the commonly used data types in PHP, and when writing query code, you often need to use PHP arrays for data processing and conversion.

Below, we will introduce a simple method to convert query results in MySQL into PHP array format.

  1. Use mysql_fetch_array

The most basic way for PHP to query the mysql data table and convert it to an array is to use the mysql_fetch_array function. The function of this function is to convert the MySQL query results from row-by-row data records into PHP array type data. The syntax of this function is as follows:

mysql_fetch_array(resource $result [, int $result_type =  MYSQL_BOTH ])

Among them, the first parameter is the MySQL query result set, and the second parameter is an optional parameter, indicating the array type that needs to be returned. MYSQL_BOTH means returning both associative arrays and numeric arrays. In addition, it can also be set to MYSQL_ASSOC and MYSQL_NUM to return only associative arrays or numeric arrays respectively.

The sample code is as follows:

// 连接数据库
$conn = mysql_connect('localhost','root','');
mysql_query("set names 'utf8'");
mysql_select_db('test');

//查询数据表
$sql = "select * from student";
$result = mysql_query($sql);

//转换为数组
$arr = array();
while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
    $arr[] = $row;
}
//输出结果
print_r($arr);

In the above code, we first connected to the database, then queried all the data in the student data table, and finally used a while loop to traverse all the query results. Each result is added to the array, and finally the entire array is output.

2. Use PDO to query and convert arrays

Another method is to use PHP's PDO (PHP Data Object) extension library to query the database and obtain the result set. PDO provides a more flexible and secure way to operate the database. Querying the database through PDO can effectively prevent attacks such as SQL injection.

The example of PDO querying MySQL is as follows:

//连接数据库
$dsn = 'mysql:dbname=test;host=localhost';
$username = 'root';
$password = '';
try {
    $dbh = new PDO($dsn, $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

//查询数据表
$sql = "select * from student";
$sth = $dbh->query($sql);

//转换为数组
$arr = $sth->fetchAll(PDO::FETCH_ASSOC);
print_r($arr);

In the above example, we first established a connection to the MySQL database, and then used PDO (PHP data object) to query all the data in the student data table , and use the fetchAll function to obtain the data of all result sets, and finally output the array.

You can see that in this example, the code for querying the data table and converting it into an array is relatively simple, and the PDO method is more secure and reliable than the mysql_fetch_array method.

Summary:

Using the above two methods, the data table query results in MySQL can be converted into PHP array format. Method 1 is suitable for MySQL extended library functions. Its disadvantage is that it does not support the faster and more secure PDO operation method. The PDO method is more flexible and safer. It has the advantage of preventing SQL injection attacks and is a compromise solution. The choice of usage method should be weighed based on the actual scenario.

The above is the detailed content of Convert php query data table to array. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
ACID vs BASE Database: Differences and when to use each.ACID vs BASE Database: Differences and when to use each.Mar 26, 2025 pm 04:19 PM

The article compares ACID and BASE database models, detailing their characteristics and appropriate use cases. ACID prioritizes data integrity and consistency, suitable for financial and e-commerce applications, while BASE focuses on availability and

PHP Secure File Uploads: Preventing file-related vulnerabilities.PHP Secure File Uploads: Preventing file-related vulnerabilities.Mar 26, 2025 pm 04:18 PM

The article discusses securing PHP file uploads to prevent vulnerabilities like code injection. It focuses on file type validation, secure storage, and error handling to enhance application security.

PHP Input Validation: Best practices.PHP Input Validation: Best practices.Mar 26, 2025 pm 04:17 PM

Article discusses best practices for PHP input validation to enhance security, focusing on techniques like using built-in functions, whitelist approach, and server-side validation.

PHP API Rate Limiting: Implementation strategies.PHP API Rate Limiting: Implementation strategies.Mar 26, 2025 pm 04:16 PM

The article discusses strategies for implementing API rate limiting in PHP, including algorithms like Token Bucket and Leaky Bucket, and using libraries like symfony/rate-limiter. It also covers monitoring, dynamically adjusting rate limits, and hand

PHP Password Hashing: password_hash and password_verify.PHP Password Hashing: password_hash and password_verify.Mar 26, 2025 pm 04:15 PM

The article discusses the benefits of using password_hash and password_verify in PHP for securing passwords. The main argument is that these functions enhance password protection through automatic salt generation, strong hashing algorithms, and secur

OWASP Top 10 PHP: Describe and mitigate common vulnerabilities.OWASP Top 10 PHP: Describe and mitigate common vulnerabilities.Mar 26, 2025 pm 04:13 PM

The article discusses OWASP Top 10 vulnerabilities in PHP and mitigation strategies. Key issues include injection, broken authentication, and XSS, with recommended tools for monitoring and securing PHP applications.

PHP XSS Prevention: How to protect against XSS.PHP XSS Prevention: How to protect against XSS.Mar 26, 2025 pm 04:12 PM

The article discusses strategies to prevent XSS attacks in PHP, focusing on input sanitization, output encoding, and using security-enhancing libraries and frameworks.

PHP Interface vs Abstract Class: When to use each.PHP Interface vs Abstract Class: When to use each.Mar 26, 2025 pm 04:11 PM

The article discusses the use of interfaces and abstract classes in PHP, focusing on when to use each. Interfaces define a contract without implementation, suitable for unrelated classes and multiple inheritance. Abstract classes provide common funct

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.