MySQL databases use SQL SELECT statements to query data.

You can query data from the database in the mysql> command prompt window, or from PHP scripts.

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]
[LIMIT N][ OFFSET M]
Copy the code
  • You can use one or more tables in a query, separate the tables with commas (,), and set the query criteria with WHERE statements.
  • The SELECT command can read one or more records.
  • You can use an asterisk (*) in place of other fields, and the SELECT statement will return all of the table’s field data
  • You can use the WHERE statement to include any condition.
  • You can use the LIMIT attribute to set the number of records returned.
  • You can use OFFSET to specify the data OFFSET at which the SELECT statement starts the query. The default offset is 0.

Get the data at the command prompt

SELECT * from runoob_tbl; SELECT * from runoob_tbl;

The instance

The following instance will return all records of the data table ruNOob_tbL:

Read data table:

select * from runoob_tbl;

Output result:

 

Use PHP scripts to get the data

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

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

The mysqli_fetch_array() function fetches one row from the result set as an associative array, or an array of numbers, or both, or returns an array generated from the rows fetched from the result set, or false if there are no more rows.

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

The instance

Try the following example to display all the records for the data table runoob_TBL.

Fetch data with mysqli_fetch_array MYSQLI_ASSOC parameter:

<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> mysqli_fetch_array ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo “<tr><td> {$row[‘runoob_id’]}</td> “. “<td>{$row[‘runoob_title’]} </td> “. “<td>{$row[‘runoob_author’]} </td> “. “<td>{$row[‘submission_date’]} </td> “. “</tr>”; } echo ‘</table>’; mysqli_close($conn); ? >

The following output is displayed:

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

Note: Remember to put variables in curly braces if you need them in a string.

In the above example, the PHP mysqli_fetch_array() function takes the second argument to MYSQLI_ASSOC. Setting this parameter returns an associative array. You can use the field name 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.

The instance

Try the following example, which uses the mysqli_fetch_assoc() function to print all the records of the data table runoob_tbL:

Fetch data using mysqli_fetch_assoc:

<? php $dbhost = ‘localhost:3306’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> mysqli_fetch_assoc ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_assoc($retval)) { echo “<tr><td> {$row[‘runoob_id’]}</td> “. “<td>{$row[‘runoob_title’]} </td> “. “<td>{$row[‘runoob_author’]} </td> “. “<td>{$row[‘submission_date’]} </td> “. “</tr>”; } echo ‘</table>’; mysqli_close($conn); ? >

The following output is displayed:

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

The instance

The following example uses the MYSQLI_NUM parameter to display all records of the runoob_tbl table:

Fetch_array MYSQLI_NUM = MYSQLI_NUM;

<? php $dbhost = ‘localhost:3306’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> mysqli_fetch_array ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_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); ? >

The following output is displayed:

The above three examples all give the same output.

Memory release

It is a good practice to free the cursor memory after we execute the SELECT statement.

Memory can be freed using the PHP function mysqli_free_result().

The following example demonstrates how this function can be used.

The instance

Try the following example:

Free memory with mysqli_free_result:

<? php $dbhost = ‘localhost:3306’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl’; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> mysqli_fetch_array ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_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); ? >

The following output is displayed: