PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

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

In This Article