MySQL SELECT commands to get records from table

Select command is used to collect date from the table. We will discuss more on this by adding more commands to this select command. Understand Structured Query Language ( SQL ) and read the basics of database table here.

SELECT query with LIMIT and order by with WHERE condition checking using BETWEEN range

20 SELECT queries with WHERE BETWEEN AND OR IN LIKE commands to get data from SQLite database

Now your table with some data is ready. We will apply select command to our table ( name student ) and fetch all the records

SELECT * FROM `student`

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55
7	My John Rob	Fifth	78
8	Asruid	Five	85
9	Tes Qry	Six	78
10	Big John	Four	55

That's all to get all the records from the table student. We have not placed any restriction here and asked for all the fields with all the records. Now if we want to restrict our results and get only name field from the table.

SELECT name FROM `student`

This will return only name field from the table. We can ask for more fields and all field names we have to separate by comma. We can include as many field names we want from a table.

SELECT name, class FROM `student`

name	class
John Deo	Four
Max Ruin	Three
Arnold	Three
Krish Star	Four
John Mike	Four
Alex John	Four
My John Rob	Fifth
Asruid	Five
Tes Qry	Six
Big John	Four

Now we will go for bit more and restrict the number of records we are getting. We are interested say in only 3 records. Our command should return 3 records only. We will use SQL limit command. This will take two parameters. One is the starting point and other is number of records required. Say we are interested in 3 records starting from beginning. Our command will be

SELECT * FROM `student` LIMIT 0,3

This command with LIMIT command will return us 3 records starting from 0 ( or first ) record. This is very important when we use this command with ORDER BY command. Now let us try to list all the students who have come in first 3 ranks. We are required to list here 3 records who have mark more than the others. The top 3 students we want to display in order of first, second and third. The order we can display are in by default in ascending order but we require the listing should return in descending order so we will get the highest ranked student at the top. Before that let us start with a simple example of ORDER BY command.

SELECT * FROM `student` ORDER BY mark

This will display the records of students in the order of lowest mark to highest mark.

id	name	class	mark
19	Tinny	Nine	18
17	Tumyu	Six	54
10	Big John	Four	55
22	Reggid	Seven	55
29	Tess Played	Seven	55
6	Alex John	Four	55
3	Arnold	Three	55
5	John Mike	Four	60
4	Krish Star	Four	60
20	Jackly	Nine	65

We will change it to display in reverse order so it will display highest mark at the top and lowest mark at the bottom.

SELECT  * FROM `student` ORDER BY `mark` DESC

With the addition of command DESC we can change the order of display to keep the highest mark at the top of the list and lowest mark at the bottom of the list. Now let us add the LIMIT command to display only the top 3 records. We already have the list in the order of highest mark to lowest mark so by just limiting the number of records to 3 will give our required top three student records.

SELECT *  FROM `student` ORDER BY `mark` DESC LIMIT 0,3

id	name	class	mark
33	Kenn Rein	Six	96
12	Recky	Six	94
32	Binn Rott	Seven	90

This is the SQL query which will display top three students based on the mark they scored. In the next section we will use sql WHERE clause to restrict or filter the records.

Combining two columns and displaying them as one

We can use CONCAT function to combine two columns and displaying them as one column. For example we can combine first name and last name and display them as Name.

Sample code in PHP

We have to first connect to database using PHP PDO. Then we can display the records by looping. Here is the sample code to display 4 records. However any other query can be used and matching records can be displayed.

<?Php
require "config.php";// Database connection

$count="SELECT * FROM student LIMIT 0,4";

echo "<table>";
echo "<tr><th>id</th><th>name</th><th>class</th>
	<th>mark</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td>
	<td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";
?>

What is SQL Query with WHERE Condition Collecting data by linking more than one table

Subscribe to our YouTube Channel here

raju	11-04-2013
How to get ( select ) data from more than one table ?

ranj	01-01-2014
how to get 200 characters/letters from a long description and to be followed by "..."

midhu	09-03-2015
how to display data in database as table by some limit(first 10,first 20,all) using php

smo	09-03-2015
You can display by using above code or to display first 10 you can use LIMIT query. By adding Order by to the query you can display in the order of highest to lowest or alphabetically or in any other combination.

Daniel	08-07-2015
smo:::How to get ( select ) data from more than one table ?

smo1234	09-07-2015
To select data from different tables, you have to link them. Here is the tutorial on how to link more than one table..

Vivek Kumar Tyagi	12-09-2015
in last example .. what is $dbo??

smo1234	12-09-2015
It is declared inside config.php file where all database connection details are kept. The link is there also.

	01-03-2022
how the the student record will increase by adding 50% in the schoolarship any student whose cgpa >=3.00

	08-01-2023
You have to use update table query, add one where condition to filter cgpa UPDATE table_name set schoolarship=1.5*schoolarship WHERE cgpa >=3.00

	28-02-2024
Which of the following SQL clauses is used to filter records and include only those that fulfill a specified condition?

SQL SELECT Command

Combining two columns and displaying them as one

Sample code in PHP

Subscribe