MySQL – Select Query

The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

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

• You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
• You can fetch one or more fields in a single SELECT command.
• You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
• You can specify any condition using the WHERE clause.
• You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
• You can limit the number of returns using the LIMIT attribute.
  

  Fetching Data from a Command Prompt

  This will use SQL SELECT command to fetch data from the MySQL table tutorials_tbl.

  Example

  The following example will return all the records from the tutorials_tbl table −

  root@host# mysql -u root -p password;
  Enter password:
  mysql> use mydb
  Database changed
  mysql> SELECT * FROM employees;
  +----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
  | id | company           | first_name | last_name | job_title             | mobile_phone  | email_address               |
  +----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
  |  1 | Northwind Traders | Nancy      | kotas     | developer             | (123)555-0100 | nancy@northwindtraders.com  |
  |  2 | Northwind Traders | Cencini    | Andrew    | Vice President, Sales | (123)555-0200 | andrew@northwindtraders.com |
  |  3 | Northwind Traders | Mariya     | Jan       | Sales Representative  | 444 333 8888  | mariya@northwindtraders.com |
  +----+-------------------+------------+-----------+-----------------------+---------------+-----------------------------+
  3 rows in set (0.00 sec)
  mysql>

  Fetching Data Using a PHP Script

  You can use the same SQL SELECT command into a PHP function mysqli_query(). This function is used to execute the SQL command and then later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns the row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
  The following program is a simple example which will show how to fetch / display records from the tutorials_tbl table.

  Example

  The following code block will display all the records from the employees table.

  num_rows > 0) {
  while($row = mysqli_fetch_array($result)) {
  echo "Sr. no:{$row['id']}  <br> ".
  "Company name:{$row['company']}  <br> ".
  "First Name: {$row['first_name']} <br> ".
  "Last Name: {$row['last_name']} <br> ".
  "Job Title: {$row['job_title']} <br> ".
  "Mobile Number: {$row['mobile_phone']} <br> ".
  "Email : {$row['email_address']} <br> ".
  "--------------------------------<br>";
  } }
  else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  ?>

  The content of the rows is assigned to the variable $row and the values in that row are then printed.
  NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.
  In the above example, the constant mysqli_query() is used as the second argument to the PHP function mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.
  PHP provides another function called mysql_fetch_assoc(), which also returns the row as an associative array.

  Example

  The following example to display all the records from the employees table using mysql_fetch_assoc() function.

  num_rows > 0) {
  while($row = mysqli_fetch_assoc($result)) {
  echo "Sr. no:{$row['id']}  <br> ".
  "Company name:{$row['company']}  <br> ".
  "First Name: {$row['first_name']} <br> ".
  "Last Name: {$row['last_name']} <br> ".
  "Job Title: {$row['job_title']} <br> ".
  "Mobile Number: {$row['mobile_phone']} <br> ".
  "Email : {$row['email_address']} <br> ".
  "--------------------------------<br>";
  } }
  else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  ?>

  While fetching data, you can write as complex a code as you like, but the procedure will remain the same as mentioned above.