In the previous tutorial, we have learned How to create a database MySQL. Now in this tutorial, we will MySQL statement with syntax and Examples.
What is a MySQL SELECT Query?
MySQL SELECT Query is used to fetching the data from the database. The MySQL SELECT Query is also used in a scripting language like PHP, Java, Ruby, etc. or you can execute it via the CMD command prompt.
How to fetch data from MySQL database
The MySQL SELECT Query is used to fetch data from one or more tables in the MySQL database:
You can fetch a specific column by using the below select statement.
SELECT STATEMENT SYNTAX
SELECT columnname FROM tablename
OR you can print all the columns by using the (*) character in the select statement.
MySQL SELECT statement will retrieve all records from a testrecords table without using any where clause. The below SELECT statement will fetch all records from testrecords table.
SELECT STATEMENT SYNTAX
SELECT * FROM testrecords
Output: +-------------+----------------+-------------------+---------------+ | id | fullName | email | mobile | +-------------+----------------+-------------------+---------------+ | 1 | HTMLCSS | htmlcss@gmail.com | 99999999900 | | 2 | HTMLCSS1 | htmlcss1@gmail.com| 99999999900 | | 3 | HTMLCSS2 | htmlcss2@gmail.com| 99999999900 | | 4 | HTMLCSS3 | htmlcss3@gmail.com| 99999999900 | +-------------+----------------+-------------------+---------------+ 4 rows in set (0.02 sec) mysql>
Fetching data from MySQL database using SELECT statement (PHPMYADMIN).
SELECT * FROM `testrecords`Output:
| id | fullName | mobile | |
| 1 | HTMLCSS | htmlcss@gmail.com | 9000000006 |
| 2 | HTMLCSS1 | htmlcss1@gmail.com | 9000000007 |
| 3 | HTMLCSS2 | htmlcss2@gmail.com | 9000000008 |
| 4 | HTMLCSS3 | htmlcss3@gmail.com | 9000000009 |
Retrieving Single Column from MYSQL database
In the below SELECT statement, we are retrieving a single column from the database
mysql> SELECT fullName from testrecords;
Table: testrecords +-------------+----------------+-------------------+---------------+ | id | fullName | email | mobile | +-------------+----------------+-------------------+---------------+ | 1 | HTMLCSS | htmlcss@gmail.com | 99999999900 | | 2 | HTMLCSS1 | htmlcss1@gmail.com| 99999999900 | | 3 | HTMLCSS2 | htmlcss2@gmail.com| 99999999900 | | 4 | HTMLCSS3 | htmlcss3@gmail.com| 99999999900 | +-------------+----------------+-------------------+---------------+
Output: +----------------+ | fullName | +----------------+ | HTMLCSS | | HTMLCSS1 | | HTMLCSS2 | | HTMLCSS3 | +----------------+ 4 rows in set (0.02 sec) mysql>
Retrieving Multiple Columns from MYSQL using Command prompt
mysql> SELECT id,fullName,email from testrecords;
Table: testrecords
+-------------+----------------+-------------------+---------------+
| id | fullName | email | mobile |
+-------------+----------------+-------------------+---------------+
| 1 | HTMLCSS | htmlcss@gmail.com | 99999999900 |
| 2 | HTMLCSS1 | htmlcss1@gmail.com| 99999999900 |
| 3 | HTMLCSS2 | htmlcss2@gmail.com| 99999999900 |
| 4 | HTMLCSS3 | htmlcss3@gmail.com| 99999999900 |
+-------------+----------------+-------------------+---------------+
Output: +-------------+----------------+-------------------+ | id | fullName | email | +-------------+----------------+-------------------+ | 1 | HTMLCSS | htmlcss@gmail.com | | 2 | HTMLCSS1 | htmlcss1@gmail.com| | 3 | HTMLCSS2 | htmlcss2@gmail.com| | 4 | HTMLCSS3 | htmlcss3@gmail.com| +-------------+----------------+-------------------+ 4 rows in set (0.02 sec) mysql>
Retrieving record using WHERE clause
mysql> SELECT id,fullName,email from testrecords where fullName=”HTMLCSS”;
Table: testrecords
+-------------+----------------+-------------------+---------------+
| id | fullName | email | mobile |
+-------------+----------------+-------------------+---------------+
| 1 | HTMLCSS | htmlcss@gmail.com | 99999999900 |
| 2 | HTMLCSS1 | htmlcss1@gmail.com| 99999999900 |
| 3 | HTMLCSS2 | htmlcss2@gmail.com| 99999999900 |
| 4 | HTMLCSS3 | htmlcss3@gmail.com| 99999999900 |
+-------------+----------------+-------------------+---------------+
Output: +-------------+----------------+-------------------+ | id | fullName | email | +-------------+----------------+-------------------+ | 1 | HTMLCSS | htmlcss@gmail.com | +-------------+----------------+-------------------+ 1 rows in set (0.01 sec) mysql>
Retrieving records using LIKE ‘%%’ clause
mysql> SELECT id,fullName,email from testrecords where fullName LIKE ‘%HTMLCSS%’;
Output:
+-------------+----------------+-------------------+
| id | fullName | email |
+-------------+----------------+-------------------+
| 1 | HTMLCSS | htmlcss@gmail.com |
| 2 | HTMLCSS1 | htmlcss1@gmail.com|
| 3 | HTMLCSS2 | htmlcss2@gmail.com|
| 4 | HTMLCSS3 | htmlcss3@gmail.com|
+-------------+----------------+-------------------+
4 rows in set (0.02 sec)
mysql>
Fetching Data using PHP mysqli
You can use the SELECT statement into a PHP function mysqli_query() to execute the select query in the Mysql database and the mysqli_fetch_assoc function will return an associative array that array can be used in while loop to print output of each row.
Example
The following example will display all the records from testrecords table using the PHP mysqli function.
<?php
$host = "localhost";
$username = "root";
$password = "";
$db_name = "testdemo";
// Create database connection
$connection = mysqli_connect( $host, $username, $password, $db_name );
// Check database connection. if DB connection not establish print error
if ( !$connection ) {
die( "Database not connected: " . mysqli_connect_error() );
}
$query = "SELECT id, fullName, email,mobile FROM testrecords";
$result = mysqli_query( $connection, $query );
if ( mysqli_num_rows( $result ) > 0 ) {
?>
<!--Print output of each row as per your requirement-->
<table border="1">
<thead>
<tr>
<th>Id</th>
<th>Full Name</th>
<th>Email Id</th>
<th>Mobile</th>
</tr>
</thead>
<tbody>
<?php while($rows = mysqli_fetch_assoc($result)) { ?>
<tr>
<td><?php echo $rows['id']; ?></td>
<td><?php echo $rows['fullName']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['mobile']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php
} else {
echo "Oops Something went wrong.";
}
mysqli_close( $connection );
?>Fetching data using PHP PDO with Prepared Statements
The following example will display all the records using PHP PDO prepared statement.
$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '';
$DB_NAME = 'testdemo';
try{
$DB_con = new PDO("mysql:host={$DB_HOST};dbname={$DB_NAME}",$DB_USER,$DB_PASS);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
echo $e->getMessage();
}
?>
<table border="1">
<thead>
<tr>
<th>Id</th>
<th>Full Name</th>
<th>Email Id</th>
<th>Mobile</th>
</tr>
</thead>
<tbody>
<?php
$result = $DB_con->prepare("SELECT id, fullName, email,mobile FROM testrecords");
$result->execute();
for($i=0; $rows = $result->fetch(); $i++){
?>
<tr>
<td><?php echo $rows['id']; ?></td>
<td><?php echo $rows['fullName']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['mobile']; ?></td>
</tr>
<?php } ?>
</tfoot>
</table>
Share this:
