Detailed instructions on how to use the SELECT command to perform query operations in MySQL, selectmysql

Detailed instructions on how to use the SELECT command to perform query operations in MySQL, selectmysql

The SQL SELECT command is used to obtain data from the MySQL database. You can use this command at a mysql> prompt or a script, such as PHP.
Syntax:

The following is the syntax of the general SQL SELECT command to extract data from the MySQL table:

SELECT field1, field2,...fieldN table_name1, table_name2...[WHERE Clause][OFFSET M ][LIMIT N]

• You can use one or more tables separated by commas (,), including various WHERE clause conditions. However, the SELECT command of the WHERE clause is an optional part.
• One or more fields can be read from a single SELECT command.
• You can specify the fields of the asterisk. In this case, SELECT returns all fields
• You can use the WHERE clause to specify any conditions.
• You can specify the offset used by SELECT to return the offset of the record. By default, the offset is 0.
• You can LIMIT the number of returned LIMIT attributes.

Obtain data from the command prompt:

The SQL SELECT command is used to obtain data from the MySQL table tutorials_tbl.
Example:

The following example returns the tutorials_tbl table of all records:

root@host# mysql -u root -p password;Enter password:*******mysql> use TUTORIALS;Database changedmysql> SELECT * from tutorials_tbl +-------------+----------------+-----------------+-----------------+| tutorial_id | tutorial_title | tutorial_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 a PHP script to obtain data:

You can use the same SQL SELECT command to enter the PHP function mysql_query (). This function is used to execute SQL commands. Another PHP function mysql_fetch_array () can be used to obtain all selected data. The row returned by this function acts as an associated array/number array or both. If no more rows exist, the function returns FALSE.

The following is a simple example to obtain the record tutorials_tbl table.
Example:

The following example shows all records in the tutorials_tbl 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 tutorial_id, tutorial_title,         tutorial_author, submission_date    FROM tutorials_tbl';mysql_select_db('TUTORIALS');$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['tutorial_id']} <br> ".     "Title: {$row['tutorial_title']} <br> ".     "Author: {$row['tutorial_author']} <br> ".     "Submission Date : {$row['submission_date']} <br> ".     "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>

The content of these rows is allocated to the variable $ row, the value in the row, and then printed.

Note: Remember to insert the value to a string array directly in curly brackets.

In the preceding example, MYSQL_ASSOC is used as the second parameter of the PHP function mysql_fetch_array () so that it returns the behavior of an associated array. An associated array can use their names instead of using indexes to access fields.

PHP provides another function called mysql_fetch_assoc (), which also returns this row as an associated array.
Instance:

The following example shows all records from the tutorial_tbl table, using the mysql_fetch_assoc () function.

<?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}$sql = 'SELECT tutorial_id, tutorial_title,         tutorial_author, submission_date    FROM tutorials_tbl';mysql_select_db('TUTORIALS');$retval = mysql_query( $sql, $conn );if(! $retval ){ die('Could not get data: ' . mysql_error());}while($row = mysql_fetch_assoc($retval)){  echo "Tutorial ID :{$row['tutorial_id']} <br> ".     "Title: {$row['tutorial_title']} <br> ".     "Author: {$row['tutorial_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 the second parameter of the PHP function mysql_fetch_array (). This causes the function to return an array of numeric indexes.
Instance:

The following example shows all records in the tutorials_tbl table using the MYSQL_NUM parameter.

<?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}$sql = 'SELECT tutorial_id, tutorial_title,         tutorial_author, submission_date    FROM tutorials_tbl';mysql_select_db('TUTORIALS');$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);?>

All the above three examples will produce the same results.
Release memory:

It is a good practice to release the cursor memory in each SELECT statement. You can use the PHP function mysql_free_result (). The following example shows how it 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 tutorial_id, tutorial_title,         tutorial_author, submission_date    FROM tutorials_tbl';mysql_select_db('TUTORIALS');$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);?>

You can write complex SQL statements while obtaining data. The program will remain the same as above.