Convert 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!