Tutorial on using the Select command to perform query operations in Mysql _mysql

The SQL Select command is used to fetch data from the MySQL database. You can use this command at the mysql> prompt or using scripts such as PHP.
Syntax:

The following is the syntax of the generic 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 comma-delimited tables, including various use WHERE clause conditions. But the select command for the WHERE clause is an optional part.
• You can read one or more fields in a single select command.
• The field where the asterisk (*) can be specified. In this case, select returns all the fields
• You can specify any condition by using the WHERE clause.
• You can specify an offset to begin returning records with select. By default, the offset is 0
• You can limit the number of uses of the Limit property returned.

To get data from a command prompt:

This will use the SQL SELECT command to get the data from the MySQL table tutorials_tbl
Example:

The following example returns all the records TUTORIALS_TBL tables:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> 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>

To get data using a PHP script:

You can use the same SQL SELECT command to enter the PHP function mysql_query (). After this function is used to execute the SQL command, another PHP function, mysql_fetch_array (), can be used to get all the selected data. This function returns rows as an associative array/numeric array or both. If there are no more rows this function returns FALSE.

The following is a simple example of getting the record tutorials_tbl table.
Example:

Try the following example to show all the records of 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 contents of these rows are assigned to the variable $row, the values in the row, and then printed.

Note: Be sure to remember when inserting a value into a string array directly with curly braces.

In the example above it is constantly mysql_assoc as the second parameter of the PHP function mysql_fetch_array () so that it returns the behavior of an associative array. An associative array that can use their names instead of using an indexed access field.

PHP provides another name called the MYSQL_FETCH_ASSOC () function that also returns the row as an associative array.
Instance:

Try the following example to show all the 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 constant mysql_num as the PHP function for the second argument mysql_fetch_array (). This causes the function to return an array of numeric indices.
Instance:

Try the following example to display all the records in the table using the Mysql_num parameter tutorials_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 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 of the above three examples will produce the same results.
Free up Memory:

It is a good practice to release cursor memory in each SELECT statement. This can be done by using 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);
? >

While getting the data, you can write complex SQL as long as you like. The program will remain the same as above.