Related Books

Prototype and Scriptaculous in Action [Ajax]

By Dave Crane, Bear Bibeault, Tom Locke

"This book should rightly be considered the bible of Prototype and Scriptaculous." --...

Creating Sortable Lists With PHP And Ajax

By Quentin Zervaas, 23 February 2006 Ajax JavaScript PHP Prototype Scriptaculous Download PDF (18 comments)

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

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

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

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

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

<?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">
            <?php foreach ($movies as $movie_id => $title) { ?>
                <li><?= $title ?></li>
            <?php } ?>
        </ul>
    </body>
</html>

Next: Adding Drag And Drop Functionality To Our List