SQL SELECT Command

Select command is used to collect date from the table. We will discuss more on this by adding more commands to this select command. If you have not created the tables then please go to sql introduction page to use the sql commands to create the tables and fill the table with some records ( data ).

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`
idnameclassmark
1John DeoFour75
2Max RuinThree85
3ArnoldThree55
4Krish StarFour60
5John MikeFour60
6Alex JohnFour55
7My John RobFifth78
8AsruidFive85
9Tes QrySix78
10Big JohnFour55

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`
nameclass
John DeoFour
Max RuinThree
ArnoldThree
Krish StarFour
John MikeFour
Alex JohnFour
My John RobFifth
AsruidFive
Tes QrySix
Big JohnFour
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.
idnameclassmark
19TinnyNine18
17TumyuSix54
10Big JohnFour55
22ReggidSeven55
29Tess PlayedSeven55
6Alex JohnFour55
3ArnoldThree55
5John MikeFour60
4Krish StarFour60
20JacklyNine65
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
idnameclassmark
33Kenn ReinSix96
12ReckySix94
32Binn RottSeven90
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.

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>";
?>




Number of User Comments : 6


Google+

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 ?
subhendu

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.

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

How you handle Search Engine Optimization ( SEO ) for your website
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer