How does MySQL database query data in a table?Query Statement/Command/Syntax

How to query data from a table in a MySQL database ? Query statement/command/syntax

MySQL Query data

MySQL databases use SQL SELECT statements to query data.

You can query data in the database through the mysql> command prompt window, or through a PHP script.

grammar

The following is the general SELECT syntax for querying data in a MySQL database:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

• In the query statement, you can use one or more tables, separate the tables with commas (,), and use the WHERE statement to set query conditions.
• The SELECT command can read one or more records.
• You can use an asterisk (*) to replace other fields, the SELECT statement will return all field data of the table
• You can use the WHERE statement to include any condition.
• You can specify the data offset at which the SELECT statement starts the query with OFFSET.By default the offset is 0.
• You can use the LIMIT property to set the number of records returned.

Get data via command prompt

In the following example, we will use the SQL SELECT command to obtain the data of the MySQL data table chenweiliang_tbl:

Examples

The following example will return all records of the data table chenweiliang_tbl:

select * from chenweiliang_tbl;

Use PHP script to get data

Use the PHP function mysqli_query() and the SQL SELECT command to retrieve data.

This function is used to execute SQL commands, and then the PHP function mysqli_fetch_array() is used to access or output all the data queried.

The mysqli_fetch_array() function retrieves a row from the result set as an associative array, a numeric array, or both, and returns an array generated from the row retrieved from the result set. If there are no more rows, it returns false.

The following example reads all records from the data table chenweiliang_tbl.

Examples

Try the following example to display all records of the data table chenweiliang_tbl.

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

In the above example, each row of records read is assigned to the variable $row, and then each value is printed out.

Note: Remember to enclose the variable in curly braces if you need to use it in a string.

In the example above, the second parameter of the PHP mysqli_fetch_array() function is MYSQL_ASSOC . Setting this parameter returns an associative array, and you can use the field name as the index of the array.

PHP provides another function, `mysqli_fetch_assoc()` , which retrieves one row from the result set as an associative array. It returns an associative array based on the row retrieved from the result set; if no more rows are available, it returns false.

Examples

Try the following example, which uses the mysqli_fetch_assoc() function to output all records of the chenweiliang_tbl table:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_assoc 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
 echo "<tr><td> {$row['chenweiliang_id']}</td> ".
 "<td>{$row['chenweiliang_title']} </td> ".
 "<td>{$row['chenweiliang_author']} </td> ".
 "<td>{$row['submission_date']} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

You can also use the constant MYSQL_NUM as the second parameter of the PHP mysqli_fetch_array() function, which returns an array of numbers.

Examples

The following example uses the MYSQL_NUM parameter to display all records in the table chenweiliang_tbl:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_NUM))
{
 echo "<tr><td> {$row[0]}</td> ".
 "<td>{$row[1]} </td> ".
 "<td>{$row[2]} </td> ".
 "<td>{$row[3]} </td> ".
 "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

memory release

It's a good practice to free cursor memory after we've executed a SELECT statement.

The memory can be released through the PHP function mysqli_free_result().

The following example demonstrates the use of this function.

Examples

Try the following examples:

Free memory with mysqli_free_result:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT chenweiliang_id, chenweiliang_title, 
 chenweiliang_author, submission_date
 FROM chenweiliang_tbl';
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 mysqli_fetch_array 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_NUM))
{
 echo "<tr><td> {$row[0]}</td> ".
 "<td>{$row[1]} </td> ".
 "<td>{$row[2]} </td> ".
 "<td>{$row[3]} </td> ".
 "</tr>";
}
echo '</table>';
// 释放内存
mysqli_free_result($retval);
mysqli_close($conn);
?>