Connecting Slim Framework and MySQL

Slim Framework is a very light PHP framework that’s great for building web applications and REST APIs. The Slim Hello World example, found right on the homepage, will get you up and running in a matter of minutes. Once you’ve done that, the next step is to create a template and use it to display data from a database. Read on to find out how.

Database

We’ll create a very simple database to get our data from. It will have a single table, “friends,” which contains three fields: id, name, and job. Here’s the SQL for that table:

CREATE TABLE `friends` (
   `id` int(10) unsigned not null auto_increment,
   `name` varchar(255),
   `job` varchar(255),
   PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4;

INSERT INTO `friends` (`id`, `name`, `job`) VALUES 
('1', 'Sam', 'Gardener'),
('2', 'Molly', 'Chef'),
('3', 'Evan', 'Web Developer');

Database connection

Slim doesn’t come with a database connection utility of its own. You can use any database connection toolkit you like. If you have a favorite, use it!. We’ll be using the MySQLi object, which is the modern way to connect to a MySQL database in native PHP. Put the following code in index.php or stash it in another file and include that file in index.php. In my case I’ve put it in a file at lib/mysql.php.

function connect_db() {
	$server = 'localhost'; // this may be an ip address instead
	$user = 'user';
	$pass = 'pass';
	$database = 'slim_db';
	$connection = new mysqli($server, $user, $pass, $database);

	return $connection;
}

Getting the data

Index.php is the main controller for your application. If you set up the Hello World example you’ve already used it to route requests, accept variables through the URL, and return data. Now we’ll use it to fetch MySQL data and render it in a template. In this case, we’ll set up a route for ‘/’ so that when you visit the root of your application you get a list of your friends from the database. Here’s the entire index.php file:

<?php
require 'Slim/Slim.php';
\Slim\Slim::registerAutoloader();
$app = new \Slim\Slim(); $app->get('/', function () use ($app) {
	require_once 'lib/mysql.php';
	$db = connect_db();
	$result = $db->query( 'SELECT id, name, job FROM friends;' );
	while ( $row = $result->fetch_array(MYSQLI_ASSOC) ) {
		$data[] = $row;
	}

	$app->render('friends.php', array(
			'page_title' => "Your Friends",
			'data' => $data
		)
	);
});

$app->run();

After setting up Slim this code creates a route for ‘/’ which you’ve already seen. From there, we include the mysql connection function we created earlier and connect to the database. Next we get a record set ($result) and loop through all records, storing them as $data. Finally, we pass that data (plus any other data we need) to a template using Slim’s render() method.

Template

Templates allow you to show your data off to the world. There are many template languages that you can add to Slim using custom Views, but for now we’ll use a simple PHP file as our template. By default, templates will go in a directory called templates/ in the root of your application.

You can pass data when you render a template. In this example we’ll pass a page title and the MySQL data that we converted to an associative array previously. Here’s what our template file, named friends.php, looks like:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title><?php echo $this->data['page_title']; ?></title>
</head>
<body>
<?php
foreach ($this->data['data'] as $friend) {
	echo $friend['id'].' - '.$friend['name'].' - '.$friend['job'].'</br />';
}
?>
</body>
</html>

That’s it!

Once you’ve got all of these pieces in place, you’ll have a functioning Slim application. Just visit the root of your application and you should see a list of friends from the database!

Download the full test application with commented code