MySQL queries the data

May 15, 2021 MySQL

MySQL queries the data

The MySQL database uses SQL SELECT statements to query the data.

You can query the data in the database through the mysql?command prompt window, or you can query the data through the PHP script.

Grammar

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

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

In a query statement, you can use one or more tables, which are split with a comma (,) and where statements are used to set query criteria.
The SELECT command can read one or more records.
You can use an asterisk instead of another field, and the SELECT statement returns all the field data for the table
You can use a WHERE statement to include any condition.
You can specify the data offset from the SELECT statement that you can start the query with OFFSET. The offset is 0 by default.
You can use the LIMIT property to set the number of records returned.

Get the data with the command prompt

The following example we'll use the SQL SELECT command to get the data for the MySQL w3cschool_tbl data:

Instance

The following instance returns all records w3cschool_tbl data table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT * from w3cschool_tbl; 
+-------------+----------------+-----------------+-----------------+
| w3cschool_id | w3cschool_title | w3cschool_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

Use PHP scripts to get data

Use the PHP function'mysql_query () and SQL SELECT commands to get the data.

The function is used to execute SQL commands and then uses or outputs data mysql_fetch_array queries through the PHP function, 2003/2.33

mysql_fetch_array() The function obtains a row from the result set as an associated array, or an array of numbers, or both returns an array generated from rows obtained from the result set, and false if there are no more rows.

The following example reads all records w3cschool_tbl data table and data sheet.

Instance

Try the following instance to display all the records w3cschool_tbl the data table.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl';

mysql_select_db('W3CSCHOOL');
$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['w3cschool_id']}  <br> ".
         "Title: {$row['w3cschool_title']} <br> ".
         "Author: {$row['w3cschool_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
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 that if you need to use variables in strings, place them in parentheses.

In the example above, the second argument of the PHP mysql_fetch_array() function is MYSQL_ASSOC, setting the result of the parameter query to return an associated array, which you can use as an index of the array using the field name.

PHP provides another function mysql_fetch_assoc (), which obtains a row from the result set as an associated array. R eturns the associated array generated from the rows obtained from the result set, and false if there are no more rows.

Instance

Try the following instance, which uses a mysql_fetch_assoc() function to output all records w3cschool_tbl data table:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
    echo "Tutorial ID :{$row['w3cschool_id']}  <br> ".
         "Title: {$row['w3cschool_title']} <br> ".
         "Author: {$row['w3cschool_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

You can also use the MYSQL_NUM to return an array mysql_fetch_array numbers as the second argument to the PHP() function.

Instance

The following examples MYSQL_NUM records from the data table w3cschool_tbl the data table:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl';

mysql_select_db('W3CSCHOOL');
$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]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>

The output of all three instances is the same.

Memory free

It is a good habit to free cursor memory after we have executed the SELECT statement. M emory freeing can be mysql_free_result by using the PHP function( ( ) .

The following example demonstrates how the function is used.

Instance

Try the following example:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl';

mysql_select_db('W3CSCHOOL');
$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]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>

MySQL queries the data

Table of contents