Creating a sortable table using jQuery and Laravel

When handling large amounts of data, it can be helpful to display it in a table view. To manipulate
the data, such as for sorting or searching, we can use the data tables JavaScript library. This way,
we don’t need to keep making database calls every time we want to change the view.

Getting ready

For this recipe, we need a standard installation of Laravel and a properly configured
MySQL database.

How to do it…

Follow the given steps to complete this recipe:
1. In our database, create a new table and add some example data using the
following commands:
DROP TABLE IF EXISTS bookprices;
CREATE TABLE bookprices (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
price float(10,2) DEFAULT NULL,
book varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO bookprices VALUES (‘1’, ‘14.99’, ‘Alice in
Wonderland’);
INSERT INTO bookprices VALUES (‘2’, ‘24.50’,
‘Frankenstein’);
INSERT INTO bookprices VALUES (‘3’, ‘29.80’, ‘War and
Peace’);
INSERT INTO bookprices VALUES (‘4’, ‘11.08’, ‘Moby
Dick’);
INSERT INTO bookprices VALUES (‘5’, ‘19.72’, ‘The Wizard
of Oz’);
INSERT INTO bookprices VALUES (‘6’, ‘45.00’, ‘The
Odyssey’);
2. In the app/models directory, create a file named Bookprices.php with the
following code snippet:
<?php
class Bookprices extends Eloquent {
}
3. In the routes.php file, add our route as given in the following code:
Route::get(‘table’, function()
{
$bookprices = Bookprices::all();
return View::make(‘table’)->with(‘bookprices’,
$bookprices);
});
4. In the views directory, create a file named table.php with the following code:
<!doctype html>
<html lang=”en”>
<head>
<meta charset=”utf-8″>
<title></title>
<script src=”//ajax.googleapis.com/ajax/libs/jquery
/1.10.2/jquery.min.js”></script>
<script src=”//ajax.aspnetcdn.com/ajax/jquery.dataTables
/1.9.4/jquery.dataTables.min.js”></script>
<link rel=”stylesheet” type=”text/css” href=”
//ajax.aspnetcdn.com/ajax/jquery.dataTables/
1.9.4/css/jquery.dataTables.css”>
</head>

<body>
<h1>Book List</h1>
<table>
<thead>
<tr>

<th>Price</th>
<th>Name</th>
</tr>
</thead>
<tbody>
<?php foreach ($bookprices as $book): ?>
<tr>
<td><?php echo $book[‘price’] ?></td>
<td><?php echo $book[‘book’] ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<script>
$(function(){
$(“table”).dataTable();
});
</script>
</body>
</html>

How it works…

To start this recipe, we create a table to hold our book price data. We then insert the data into
the table. Next, we create an Eloquent model so we can interact with the data. We then pass
that data into our view.
In our view, we load in jQuery and the dataTables plugin. Then, we create a table to hold
our data and then loop through the data, putting each record into a new row. When we add
the dataTable plugin to our table, it will automatically add sorting to our table for each of
our columns.

Leave a Reply

Your email address will not be published. Required fields are marked *