MySQL Query Data

MySQL databases use SQL SELECT statements to query data.

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

Syntax

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

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

You can use one or more tables in the query statement, separated by commas (,), and use the WHERE clause to set the 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 clause to include any conditions.
You can use the LIMIT attribute to set the number of records returned.
You can use OFFSET to specify the data offset for the beginning of the SELECT statement. The default offset is 0.

by command prompt

We will retrieve data from the MySQL data table w3codebox_tbl:

Online example

The following example will return the data of the data table w3Parameters display the table w

Read the data table:

select　*　from w3codebox_tbl;

Output result:

Use PHP scripts to get data

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

This function is used to execute SQL commands and then retrieve data through the PHP function mysqli_fetch_array(). To use or output all the data queried.

The mysqli_fetch_array() function retrieves a row from the result set as an associative array, or a numeric array, or both. Returns an array generated from the rows obtained from the result set, or false if there are no more rows.

The following example retrieves data from the data table w3Read all records from codebox_tbl.

Online example

Try the following example to display the data table w3All records of codebox_tbl.

Use the mysqli_fetch_array MYSQLI_ASSOC parameter to get data:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent garbled Chinese characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl';
　
mysqli_select_db($conn, 'w3codebox');}}
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Basic Tutorial mysqli_fetch_array Test</h2">';
echo '<table border="1><tr><td>教程 ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>";
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
　　　　while($row = mysqli_fetch_assoc($retval))3echo "<tr><td>　{$row['w/td> ".
　　　　　　　　　codebox_title']}　<3codebox_id'}</td> ".
　　　　　　　　　codebox_title']}　<3"<td>{$row['w/td> ".
　　　　　　　　　codebox_author'}　</td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output is as follows:

In the above examples, each line of the read record is assigned to the variable $row, and then each value is printed out.

Note:Remember if you need to use variables in a string, please place the variables inside curly braces.

In the above example, the second parameter of the PHP mysqli_fetch_array() function is MYSQLI_ASSOC, Setting this parameter returns an associative array for the query results, you can use field names as the index of the array.

PHP provides another function mysqli_fetch_assoc(), which retrieves a row from the result set as an associative array. Returns an associative array generated from the rows obtained from the result set, or false if there are no more rows.

Online example

Try the following example, which uses mysqli_fetch_assoc() 函数来输出数据表 w3The function to output the table w

All records of codebox_tbl

<?php
$dbhost = 'localhost:3306';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent garbled Chinese characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl';
　
mysqli_select_db($conn, 'w3codebox');}}
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2Use mysqli_fetch_assoc to get data:/h2">';
echo '<table border="1><tr><td>教程 ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>";
>Basic tutorial mysqli_fetch_assoc test<
{
　　　　while($row = mysqli_fetch_assoc($retval))3echo "<tr><td>　{$row['w/td> ".
　　　　　　　　　codebox_title']}　<3codebox_id'}</td> ".
　　　　　　　　　codebox_title']}　<3"<td>{$row['w/td> ".
　　　　　　　　　codebox_author'}　</td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

"<td>{$row['submission_date']}　<

The output result is as follows:

Online example

You can also use the constant MYSQLI_NUM as the second parameter of the PHP mysqli_fetch_array() function to return a numeric array. The following example uses MYSQLI_NUM3Parameters display the table w

All records of codebox_tbl

<?php
$dbhost = 'localhost:3306';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent garbled Chinese characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl';
　
mysqli_select_db($conn, 'w3codebox');}}
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Basic Tutorial mysqli_fetch_array Test</h2">';
echo '<table border="1><tr><td>教程 ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>";
while($row = mysqli_fetch_array($retval, MYSQLI_NUM))
{
　　　　echo "<tr><td> "{$row[0]}</td> ".
　　　　　　　　　"<td>{$row[1}</td></td> ".
　　　　　　　　　"<td>{$row[2}</td></td> ".
　　　　　　　　　"<td>{$row[3}</td></td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output is as follows:

Use mysqli_fetch_array with MYSQLI_NUM parameter to get data:

The output results of the above three examples are the same.

Memory release

It is a good habit to release cursor memory after executing the SELECT statement.

The following examples demonstrate how to use this function.

Online example

Try the following examples:

Use mysqli_free_result to release memory:

<?php
$dbhost = 'localhost:3306';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent garbled Chinese characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl';
　
mysqli_select_db($conn, 'w3codebox');}}
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Basic Tutorial mysqli_fetch_array Test</h2">';
echo '<table border="1><tr><td>教程 ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>";
while($row = mysqli_fetch_array($retval, MYSQLI_NUM))
{
　　　　echo "<tr><td> "{$row[0]}</td> ".
　　　　　　　　　"<td>{$row[1}</td></td> ".
　　　　　　　　　"<td>{$row[2}</td></td> ".
　　　　　　　　　"<td>{$row[3}</td></td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
//　Release Memory
mysqli_free_result($retval);
mysqli_close($conn);
?>

The output is as follows:

MySQL WHERE Clause MySQL Insert Data

MySql Tutorial

MySQL Query Data

Syntax

by command prompt

Online example

Use PHP scripts to get data

Online example

Use the mysqli_fetch_array MYSQLI_ASSOC parameter to get data:

Online example

All records of codebox_tbl

Online example

All records of codebox_tbl

The output results of the above three examples are the same.

Online example

Use mysqli_free_result to release memory: