Latest web development tutorials

MySQL query data

MySQL database using the SQL SELECT statement to query data.

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

grammar

Following is a common query data in a MySQL database SELECT syntax:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
  • You can query using one or more tables, use a comma between tables (,) segmentation, and use a WHERE clause to set the search criteria.
  • SELECT command reads one or more records.
  • You can use an asterisk (*) instead of the other fields, SELECT statement returns all the fields in the data table
  • You can use the WHERE clause to include any conditions.
  • You can specify the data offset SELECT query statement begins by OFFSET. By default, the offset is 0.
  • LIMIT the number of records you can use to set the property returned.

Get data from the command prompt

The following examples we'll get MySQL data tables w3big_tbl data through SQL SELECT command:

Examples

The following example returns all records in the data table w3big_tbl:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT * from w3big_tbl 
+-------------+----------------+-----------------+-----------------+
| w3big_id | w3big_title | w3big_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 retrieve data

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

This function is used to execute SQL commands, and then through the PHP function mysql_fetch_array () to use or output data for all queries.

mysql_fetch_array () function Fetch row as an associative array, a numeric array, or both, according to an array of return obtained from the result set rows generated if there are no more rows returned from false results.

The following examples are to read all records from the data in the table w3big_tbl.

Examples

Try the following examples to show all records of data tables w3big_tbl.

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

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

The above example, the records read each line is assigned to the variable $ row, and then print out each value.

Note: Remember that if you need to use variables in the string, set the variable placed in curly braces.

In the example above, PHP mysql_fetch_array () function of the second parameter is MYSQL_ASSOC, set the parameter query results returned associative array, you can use the field name as the array index.

PHP provides another function mysql_fetch_assoc (), the function Fetch a result row as an associative array. Returns an associative array obtained from the result set rows generated if there are no more rows, it returns false.

Examples

Try the following instance uses mysql_fetch_assoc () function to output all the records of data tables w3big_tbl:

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

mysql_select_db('w3big');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
    echo "Tutorial ID :{$row['w3big_id']}  <br> ".
         "Title: {$row['w3big_title']} <br> ".
         "Author: {$row['w3big_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

You can also use the constant MYSQL_NUM as PHP mysql_fetch_array () The second argument of the function returns an array of numbers.

Examples

The following example uses MYSQL_NUM parameter displays all the records in the data table w3big_tbl:

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

mysql_select_db('w3big');
$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 above three examples of output results are the same.


Memory release

After we finished the implementation of the SELECT statement, released cursor memory is a good habit. . By PHP function mysql_free_result () to achieve the release of memory.

The following example demonstrates how to use the function.

Examples

Try the following examples:

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

mysql_select_db('w3big');
$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);
?>