MySQL query data

MySQL database uses 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.

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

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

You can use one or more tables in the query statement , use commas (,) to separate tables, 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, and the SELECT statement will return all field data in the table.

You can use WHERE statement to include any condition.

You can use OFFSET to specify the data offset at which the SELECT statement starts to query. By default the offset is 0.

You can use the LIMIT attribute to set the number of records returned.

Get data through the command prompt

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

[root@localhost runoob]# mysql -u root -pEnter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 5.5.50-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> use RUNOOBReading table information for completion of table and column names
You can turn off this feature to get a startup quicker with -A

Database changed
MariaDB [RUNOOB]> select * from runoob_tbl
-> ;+-----------+---------------+-- -------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+------ -----+---------------+--------------+------------ -----+
| 1 | Learn PHP | John Poul | 2016-11-26 |
| 2 | Learn MySQL | Abdul S | 2016-11-26 |
| 3 | JAVA Tutorial | Sanjay | 2007-05 -06 |
| mysql | cakin24 ------------+------------------+4 rows in set (0.00 sec)

MariaDB [RUNOOB]>

Use PHP scripts to obtain data

Method 1:

First, use the PHP function mysql_query() and the SQL SELECT command to obtain data. The mysql_query function is used to execute SQL commands.

Then, use the PHP function mysql_fetch_array() to process the queried data.

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

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

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

$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if (! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT runoob_id, runoob_title,
              runoob_author, submission_date
        FROM runoob_tbl';

mysql_select_db('RUNOOB' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array( $retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['runoob_id']}
".
"Title: {$row['runoob_title']}
".
" Author: {$row['runoob_author']}
".
" "Submission Date : {$row['submission_date']}
".
" "--------- ---------------------
";
}
echo "Fetched data successfullyn";
mysql_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, put the variable in curly braces.

In the above example, the second parameter of the PHP mysql_fetch_array() function is MYSQL_ASSOC. When set to this parameter, the query results will return an associative array. You can use the field name as the index of the array.

Method 2:

PHP provides another function mysql_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.

The following example uses the mysql_fetch_assoc() function to output all records of the data table runoob_tbl:

$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword' ;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT runoob_id , runoob_title,
              runoob_author, submission_date
      FROM runoob_tbl';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
echo "Tutorial ID :{$row['runoob_id']}
".
" "Title: {$row['runoob_title']}
".
" "Author: {$row['runoob_author']}
".
" "Submission Date : {$row['submission_date']} ".
" "--------------------------------
";
}
echo "Fetched data successfullyn";
mysql_close($conn);
?>

Method 3:

You can also use the constant MYSQL_NUM as the second parameter of the PHP mysql_fetch_array() function to return a numeric array.

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

$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT runoob_id, runoob_title,
runoob_author, submission_date
FROM runoob_tbl';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]}
".
"Title: {$row[ 1]}
".
" "Author: {$row[2]}
".
" "Submission Date: {$row[3]}
".
" "---- ----------------------------
";
}
echo "Fetched data successfullyn";
mysql_close($conn) ;
?>

The output results of the above three examples are the same. The output results are as follows:

Memory release

After we execute the SELECT statement, it is a good habit to release the cursor memory. Memory release can be achieved through the PHP function mysql_free_result().

The following example demonstrates the use of this function. This example only adds the statement mysql_free_result($retval); based on the previous example.

$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if (! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT runoob_id, runoob_title,
              runoob_author, submission_date
          FROM runoob_tbl';

mysql_select_db('RUNOOB' );$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($ retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]}
".
"Title: {$row[1]}
".
"Author: {$row [2]}
".
" "Submission Date: {$row[3]}
".
" "-------------------------- ------------
";
}mysql_free_result($retval);echo "Fetched data successfullyn";
mysql_close($conn);
?>