Simple Table sorting in jquery with mysql data

Notice: Undefined variable: feedback_options in /home/cinemaup/public_html/wp-content/plugins/wp-article-feedback/plugin.php on line 135

Nowadays, We showed our data in Frid view web content for the client. so for every content we need to use Sorting for Client and also our benefits, But Sorting, searching, pagination is not a easy job in HTML tables with Php and mysql data. So many grid view framework out there, Here we Learn about table sorting in jquery.
DataTable.js is the most popular nowadays. Using jquery and mysql structure we are going to create very simple and easy table sorting functions.

Column sorting is a feature where user can sort the results either in ascending or descending order.

Step1 : Setup your database and table in MySQL
Before you run this script make sure you have created a database and table in MySQL. Below is the sample sql command to create a database, table, and insert some dummy rows.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

CREATE TABLE IFNOTEXISTS`exam`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`student`varchar(55)DEFAULTNULL,

`subject`varchar(255)DEFAULTNULL,

`mark`int(11)DEFAULTNULL,

PRIMARY KEY(`id`)

)ENGINE=InnoDB DEFAULTCHARSET=latin1 AUTO_INCREMENT=17;

--

--Dumping data fortable`exam`

--

INSERT INTO`exam`(`id`,`student`,`subject`,`mark`)VALUES

(1,'John','English',75),

(2,'John','Maths',85),

(3,'John','Physics',73),

(4,'John','Chemistry',80),

(5,'robert','English',60),

(6,'robert','Maths',65),

(7,'robert','Physics',69),

(8,'robert','Chemistry',70),

(9,'Adam','English',75),

(10,'Adam','Maths',70),

(11,'Adam','Physics',85),

(12,'Adam','Chemistry',77),

(13,'jack','English',95),

(14,'jack','Maths',90),

(15,'jack','Physics',91),

(16,'jack','Chemistry',75);

For connect database Check this

Step 2: Fetch records from table and display
Now the next step is simple, fetch all records from MySQL table and display.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

<body>

<table id='table_sort'class='tablesorter'>

<thead>

<tr>

<th>id</th>

<th>student</th>

<th>subject</th>

<th>mark</th>

</tr>

</thead>

<tbody>

<?php

//connect to database

include"../config.php";

//query the database

$sql="select * from exam";

$rs=mysql_query($sql);

while($row=mysql_fetch_array($rs)){// fetch the data from database

extract($row);// use function to get exact row asc or desc

?>

<tr>

<td><?phpecho$id;?></td>

<td><?phpecho$subject;?></td>

<td><?phpecho$subject;?></td>

<td><?phpecho$mark;?></td>

</tr>

<?php

}

?>

</tbody>

</table>

Step 3:Include jquery functions for sorting

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

<!--includejQuerylibraryandtablesorterplugin-->

<script type='text/javascript'src='js/jquery-latest.js'>

</script>

<script type='text/javascript'src='js/jquery.tablesorter.min.js'>

</script>

<script type='text/javascript'>

$(document).ready(function(){

$("#table_sort").tablesorter({

//for example we want to disable the

//password column (5th column) from sorting

//we will specify '4' since it was indexed

//(count starts at '0')

//and set its property to 'false'

headers:{

4:{

sorter:false

}

}

});

});

</script>

Step 4 : Add css style for design

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

.tablesorter{

margin:0px;

padding:0px;

width:100%;

box-shadow:10px10px5px#888888;

-moz-border-radius-bottomleft:0px;

-webkit-border-bottom-left-radius:0px;

border-bottom-left-radius:0px;

-moz-border-radius-bottomright:0px;

-webkit-border-bottom-right-radius:0px;

border-bottom-right-radius:0px;

-moz-border-radius-topright:0px;

-webkit-border-top-right-radius:0px;

border-top-right-radius:0px;

-moz-border-radius-topleft:0px;

-webkit-border-top-left-radius:0px;

border-top-left-radius:0px;

}

.tablesorter table{

width:100%;

margin:0px;

padding:0px;

}

.tablesorter tr:nth-child(odd){

background-color:#D9ECFE;

}

.tablesorter tr:nth-child(even){background-color:#ffffff; }

.tablesorter td{

vertical-align:middle;

text-align:left;

padding:7px;

font-size:16px;

font-family:arial;

font-weight:normal;

color:#000000;

}

table.tablesorter thead tr.header{

background-image:url(images/bg.gif);

background-repeat:no-repeat;

background-position:center right;

cursor:pointer;

}

table.tablesorter thead tr.headerSortDown{

background-image:url(images/desc.gif);

}

table.tablesorter thead tr.headerSortUp{

background-image:url(images/asc.gif);

}

construct the table with id ‘table_srot’ and use this id for sorting. thead tag is required for using tablesorter. tbody tag is also required for using tablesorter. using mysql fetching with array you can get the data and using “exact” function you can access exect row asc or dec.
That’s it enjoy the code.

Was this article helpful?

Thanks!

Thanks for getting in touch with us.

I'm Rajasekar - Web developer, Freelancer, Blogger and Owner of DeveloperDesks. From India lives in Bahrain. I love to do coding, Creating websites and trying different with code and designs. You Can Hire Me

Follow Us

Click Easy To Create Own Website

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

Thank you for subscribing.

Something went wrong.

we respect your privacy and take protecting it seriously

About

Developer Desks is Teach Coding and updates latest technology news in our blog. We have Highly professional web designer and Developer. We do Website developing work as a freelancer. Founded in 2015, This blog focus on the latest trends, tutorials, opinion articles as well as tips and tricks to empower our readers to become better web developers.