Creating Sortable Lists With PHP And Ajax
Outputting The Database Data
Now that we’ve made and populated our database, we’re going to write a PHP script to connect to this database and select all of this data. Because we are making both a MySQL version and a PostgreSQL version, some of this code will be implemented twice (once for each).
Hopefully you are using database abstraction in your web applications, but for the purpose of this article we’ll assume that you aren’t.
database.php for MySQL
Listing 4 database.php
function dbConnect() { $link = mysql_connect('localhost', 'username', 'password'); if (!$link) return false; return mysql_select_db('phpriot'); }
database.php for PostgreSQL
Listing 5 database.php
function dbConnect() { $str = sprintf('host=%s user=%s password=%s dbname=%s', 'localhost', 'username', 'password', 'phpriot'); $link = pg_connect($str); return (bool) $link; }
movies.php for MySQL
Listing 6 movies.php
function getMovies() { $query = 'select movie_id, title from movies order by ranking, lower(title)'; $result = mysql_query($query); $movies = array(); while ($row = mysql_fetch_object($result)) { $movies[$row->movie_id] = $row->title; } return $movies; }
movies.php for PostgreSQL
Listing 7 movies.php
function getMovies() { $query = 'select movie_id, title from movies order by ranking, lower(title)'; $result = pg_query($query); $movies = array(); while ($row = pg_fetch_object($result)) { $movies[$row->movie_id] = $row->title; } return $movies; }
index.php for MySQL and PostgreSQL
Here’s the main script that displays the list of movies. It is the same for both MySQL and PostgreSQL, as it will include the necessary code. At this point it is not styled and it is not yet possible to change the ordering. We’ll be adding each of those things in next.
Listing 8 index.php
require_once('database.php'); require_once('movies.php'); if (!dbConnect()) { echo 'Error connecting to database'; exit; } $movies = getMovies(); <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd"> <html> <head> <title>phpRiot Sortable Lists</title> </head> <body> <h1>phpRiot Sortable Lists</h1> <ul id="movies_list"> foreach ($movies as $movie_id => $title) { <li> $title </li> } </ul> </body> </html>

![Prototype and Scriptaculous in Action [Ajax]](http://ecx.images-amazon.com/images/I/51i%2BlUTDmbL._SL75_.jpg)