MySQL Query Data
MySQL databases use the SQL SELECT statement to query data.
You can query data in the database through the mysql> command prompt window, or through PHP scripts.
Syntax
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]
- You can use one or multiple tables in the query statement, separated by commas (,), and use the WHERE clause to set query conditions.
- The SELECT command can read one or multiple records.
- You can use an asterisk (*) to replace other fields; the SELECT statement will return all fields 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 specify the data offset for the SELECT statement using OFFSET. The default offset is 0.
Retrieving Data via Command Prompt
The following example demonstrates how to retrieve data from the MySQL table tutorialpro_tbl using the SQL SELECT command:
Example
The following example will return all records from the table tutorialpro_tbl:
SELECT * FROM tutorialpro_tbl;
Output result:
Retrieving Data Using PHP Script
Use the PHP function mysqli_query() along with the SQL SELECT command to retrieve data.
This function is used to execute the SQL command, and then the PHP function mysqli_fetch_array() is used to retrieve or output all the queried data.
The mysqli_fetch_array() function fetches a result row as an associative array, a numeric array, or both. It returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
The following example retrieves all records from the table tutorialpro_tbl.
Example
Try the following example to display all records from the table tutorialpro_tbl.
<?php
$dbhost = 'localhost'; // mysql server hostname
$dbuser = 'root'; // mysql username
$dbpass = '123456'; // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorialpro_id, tutorialpro_title,
tutorialpro_author, submission_date
FROM tutorialpro_tbl';
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Could not get data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_fetch_array Test</h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
echo "<tr><td> {$row['tutorialpro_id']}</td> ".
"<td>{$row['tutorialpro_title']} </td> ".
"<td>{$row['tutorialpro_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
Output result is as follows:
In the above example, each row of the retrieved data is assigned to the variable $row, and then each value is printed.
Note: Remember that if you need to use variables in a string, place the variables in braces.
In the example above, the second parameter of the PHP mysqli_fetch_array() function is MYSQLI_ASSOC,
Setting this parameter returns the query result as an associative array, which you can index using the field names.
PHP provides another function mysqlifetchassoc(), which fetches a result row as an associative array. It returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
Example
Try the following example, which uses the mysqlifetchassoc() function to output all records from the table tutorialpro_tbl:
<?php
$dbhost = 'localhost:3306'; // mysql server hostname
$dbuser = 'root'; // mysql username
$dbpass = '123456'; // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorialpro_id, tutorialpro_title,
tutorialpro_author, submission_date
FROM tutorialpro_tbl';
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Could not get data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_fetch_assoc Test</h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
echo "<tr><td> {$row['tutorialpro_id']}</td> ".
"<td>{$row['tutorialpro_title']} </td> ".
"<td>{$row['tutorialpro_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
Output result is as follows:
You can also use the constant MYSQLI_NUM as the second parameter for the PHP mysqli_fetch_array() function, which returns a numeric array.
Example
The following example uses the MYSQLINUM parameter to display all records from the table tutorialprotbl:
<?php
$dbhost = 'localhost:3306'; // mysql server hostname
$dbuser = 'root'; // mysql username
$dbpass = '123456'; // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorialpro_id, tutorialpro_title,
tutorialpro_author, submission_date
FROM tutorialpro_tbl';
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Could not get data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_fetch_array Test</h2>';
echo '<table border="1"><tr><td>Tutorial 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>{$row[2]} </td> ".
"<td>{$row[3]} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
Output result is as follows:
All three examples above produce the same output.
Memory Release
It is a good practice to free cursor memory after executing the SELECT statement.
This can be achieved using the PHP function mysqli_free_result().
The following example demonstrates how to use this function.
Example
Try the following example:
<?php
$dbhost = 'localhost:3306'; // mysql server hostname
$dbuser = 'root'; // mysql username
$dbpass = '123456'; // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorialpro_id, tutorialpro_title,
tutorialpro_author, submission_date
FROM tutorialpro_tbl';
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Could not get data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_free_result Test</h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
echo "<tr><td> {$row['tutorialpro_id']}</td> ".
"<td>{$row['tutorialpro_title']} </td> ".
"<td>{$row['tutorialpro_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_free_result($retval);
mysqli_close($conn);
?>
Output result is as follows:
mysqli_select_db($conn, 'tutorialpro');
$retval = mysqli_query($conn, $sql);
if (!$retval) {
die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_fetch_array Test</h2>';
echo '<table border="1"><tr><td>Tutorial 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>{$row[2]} </td> " .
"<td>{$row[3]} </td> " .
"</tr>";
}
echo '</table>';
// Free memory
mysqli_free_result($retval);
mysqli_close($conn);
?>
Output result is as follows: ```