MySQL query data
MySQL query data
MySQL database uses the SQL SELECT statement to query data.
You can query data in the database through the mysql> command prompt window, or query data through PHP scripts.
Syntax
The following is the general SELECT syntax for querying data in the MySQL database:
SELECT column_name,column_name FROM table_name[WHERE Clause][LIMIT N][ OFFSET M]
In the query statement, you can Use one or more tables, separate them 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 in the table.
You can use WHERE statement to contain any conditions.
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 to query. By default the offset is 0.
Get data through the command prompt
In the following example we will get MySQL through the SQL SELECT command Data of data table user:
Example
The following example will return all records of data table user:
Read the data table:
select * from user;
Use PHP script to get data
Use PHP function mysqli_query() and SQL SELECT commands to obtain data.
This function is used to execute SQL commands, and then use or output all queried data through the PHP function mysqli_fetch_array().
mysqli_fetch_array() function fetches a row from the result set as an associative array, a numeric array, or both. Returns an array generated based on 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 user.
Example
Try the following example to display all records of the data table user.
header("Content-Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host Address
$dbuser = 'root'; // mysql username
$dbpass = 'root'; // mysql username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled characters
mysqli_query ($conn , "set names utf8");
$sql = 'SELECT user_id, user_title,
user_author, submission_date
FROM user';
mysqli_select_db( $conn, 'DEMO' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Unable to read data: ' . mysqli_error($conn ));
}
echo '<h2>PHP Chinese website 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['user_id']}</td> ".
" "<td> ;{$row['user_title']} </td> ".
" "<td>{$row['user_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 if you need to use a variable in a string, please put the variable in curly braces.
In the above example, the second parameter of the PHP mysqli_fetch_array() function is MYSQLI_ASSOC. Set this parameter to query the result to return an associative array. You can use the field name as the index of the array.
PHP provides another function mysqli_fetch_assoc(), which fetches a row from the result set as an associative array. Returns an associative array based on the rows taken from the result set, or false if there are no more rows.
Example
Try the following example, which uses the mysqli_fetch_assoc() function to output all records of the data table user:
<?php
header("Content-Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host Address
$dbuser = 'root'; // mysql username
$dbpass = 'root'; // mysql username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled characters
mysqli_query ($conn , "set names utf8");
$sql = 'SELECT user_id, user_title,
user_author, submission_date
FROM user';
mysqli_select_db( $conn, 'DEMO' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Unable to read data: ' . mysqli_error($conn ));
}
echo '<h2>PHP Chinese website 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['user_id']}</td> ".
" "<td>{ $row['user_title']} </td> ".
" "<td>{$row['user_author']} </td> ".
" "<td>{$row ['submission_date']} </td> ".
" "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
You can also use the constant MYSQLI_NUM as the second parameter of the PHP mysqli_fetch_array() function to return a numeric array.
Example
The following example uses the MYSQLI_NUM parameter to display all records of the data table user:
header("Content-Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host address
$dbuser = 'root'; / mysql username
$dbpass = 'root'; // mysql username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
//Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8");
$sql = 'SELECT user_id, user_title,
user_author, submission_date
FROM user';
mysqli_select_db( $conn, 'demo' );
$retval = mysqli_query ( $conn, $sql );
if(! $retval )
{
die('Unable to read data: ' . mysqli_error($conn));
}
echo ' <h2>PHP Chinese website mysqli_fetch_array test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title< ;/td><td>Author</td><td>Submit Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_NUM))
{
echo "<tr><td> {$row[0]}</td> ".
"<td>{$row[1]} </td> " .
;/tr>";
}
echo '</table>';
mysqli_close($conn);
?>
##
The output results of the above three examples are the same.
Memory release
After we execute the SELECT statement, it is a good habit to release the cursor memory.
You can release memory through the PHP function mysqli_free_result().
The following example demonstrates the use of this function.
Examples
Try the following examples:
<?php
header("Content-Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host address
$dbuser = 'root'; ; dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
//Set encoding to prevent Chinese garbled characters
mysqli_query($conn , "set names utf8");
$sql = 'SELECT user_id, user_title,
user_author, submission_date
FROM user';
mysqli_select_db( $conn, 'demo' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Unable to read data: ' . mysqli_error ($conn));
}
echo '<h2>PHP Chinese website 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>';
// Release memory
mysqli_free_result($ retval);
mysqli_close($conn);
?>
Related course recommendations:
