PHP: Database Case Studies

Derek Bridge

Department of Computer Science,
University College Cork

PHP: Database Case Studies

Aims:

to appreciate two complete examples in which PHP is used to access a database
to learn when to use the HTTP get method and when to use post

Case study I: a web page comments facility

We will write some PHP that
- allows users to post comments on a web page
- allows users to view the comments posted about the page
This means that the web page (shag.html) becomes a PHP script (shag.php)
Note that the script uses the HTTP post method, not the get method

MySQL database for the web page comments facility

CREATE TABLE comments_table 
(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255),
    message TEXT,
    PRIMARY KEY (id)
);

The web page/PHP script

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Shag Ireland - News</title>
        <link rel="stylesheet" href="comments.css" />
    </head>

    <body>
        <header>
            <h1>Shag Ireland - News</h1>
        </header>
    
        <article>
            <!-- The rest of the 'normal' web page goes here -->

            <?php
                require_once( 'output_functions.php' );
    
                function output_form( )
                {
                    echo "<form action=\"{$_SERVER['PHP_SELF']}\" method=\"post\">";
                    echo "<fieldset>";
                    echo "<legend>Post a new comment</legend>";
                    output_textfield('username', 'Name: ', 'username', 25, 25, '', false);
                    echo "<div>";
                    echo "<label for=\"message\">Comment:</label>";
                    echo "<textarea name=\"message\" id=\"message\" rows=\"5\" cols=\"50\">";
                    echo "</textarea>";
                    echo "</div>";        
                    output_submit_button('Submit');
                    output_reset_button('Reset');
                    echo "</fieldset>";
                    echo "</form>";
                }
    
                function is_initial_request()
                {
                    return ! isset($_POST['submit']);
                }
    
                // Connect to database
                $dbconnection = mysqli_connect( $host, $user, $password, $dbname );
                if ( ! $dbconnection )
                {
                    die();
                }
 
                if ( ! is_initial_request() )
                {
                    $username = $_POST['username'];
                    $message = $_POST['message'];
                    // Insert into the database
                    $insert_sql = "INSERT INTO comments_table (username, message)
                                     VALUES ('{$username}', '{$message}');";
                    $dbinsert_result = mysqli_query( $dbconnection, $insert_sql );
                    if ( ! $dbinsert_result )
                    {
                        die();
                    }
                }
                echo "<section>";
                echo "<h1>Comments</h1>";
                output_form();
                $retrieve_sql = "SELECT * FROM comments_table ORDER BY id DESC"; 
                $dbretrieve_result = mysqli_query( $dbconnection, $retrieve_sql );
                if ( ! $dbretrieve_result )
                {
                    die();
                }
                if ( mysqli_num_rows( $dbretrieve_result ) != 0 ) 
                {
                    while ( $row = mysqli_fetch_assoc( $dbretrieve_result ) ) 
                    {
                        echo "<article>
                                <h1>{$row['username']}</h1>
                                <p>{$row['message']}</p>
                              </article>";
                    }
                }
                echo "</section>";
                mysqli_free_result( $dbretrieve_result );
                mysqli_close( $dbconnection );
            ?>
        </article>
		
    </body>
</html>

HTTP requests

After form data is encoded as name/value pairs, the browser creates an HTTP request
- the command (method) is given by the method attribute
- the URL is given by the action attribute
There are two methods the browser can use to pass form data to the server: get and post
- if method="get", the data is added to the end of the URL after a question mark, e.g.:
  
  GET guestbook.php?first=Hugh&surname=Jeegoh HTTP/1.1
- if method="post", the data is included in the HTTP request body, not the header
  
  POST guestbook.php HTTP/1.1
  
  first=Hugh&surname=Jeegoh

The get method versus the post method

When to use method="get",
- for a short form with few fields
- to allow the URL (with the form data) to be bookmarked, or used elsewhere
- where the output wouldn't differ if you issued the same request more than once
When to use method="post",
- for a longer, more complex form, where encoding the form data in the URL would result in a URL that is too long
- for security, because encryption and other encodings of the data is possible for method="post"
- for requests that make a change in the server (e.g. update a database)

How to use the post method

In your HTML forms,
- simply use method="post" instead of method="get"
In your PHP scripts,
- simply use superglobal $_POST instead of $_GET

Case study II: a web site comments facility

Next we will write some PHP that
- allows users to post comments on more than one page in a web site
- allows users to view the comments posted about each page in the web site
We will write a chunk of PHP (comments_script.php)
Every web page (wombats.html, badgers.html) will become a PHP script (wombats.php, badgers.php) and we will use require_once to include the chunk of PHP

MySQL database for the web page comments facility

CREATE TABLE comments_table 
(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255),
    url VARCHAR(255) NOT NULL,
    message TEXT,
    PRIMARY KEY (id)
);

A typical web page/PHP script, `wombats.php`

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Wombat Lovers Corner</title>
        <link rel="stylesheet" type="text/css" href="comments.css" />
    </head>

    <body>
        <header>
            <h1>Wombat Lovers Corner</h1>
        </header>

        <article>
		
	        <!-- The rest of the 'normal' web page goes here -->
    
            <?php
                require_once('comments_script.php');
            ?>
			
        </article>

    </body>
</html>

The chunk of PHP, `comments_script.php`

<?php
    require_once( 'output_functions.php' );
    
    function output_form( )
    {
        echo "<form action=\"{$_SERVER['PHP_SELF']}\" method=\"post\"";
        echo "<fieldset>";
        echo "<legend>Post a new comment</legend>";
        output_textfield('username', 'Name: ', 'username', 25, 25, '', false);
        echo "<div>";
        echo "<label for=\"message\">Comment:</label>";
        echo "<textarea name=\"message\" id=\"message\" rows=\"5\" cols=\"50\">";
        echo "</textarea>";
        echo "</div>";        
        output_submit_button('Submit');
        output_reset_button('Reset');
        echo "</fieldset>";
        echo "</form>";
    }
    
    function is_initial_request()
    {
        return ! isset($_POST['submit']);
    }
    
    // Connect to database
    $dbconnection = mysqli_connect( $host, $user, $password, $dbname );
    if ( ! $dbconnection )
    {
        die();
    }

    $url = $_SERVER['PHP_SELF'];
    if ( ! is_initial_request() )
    {
        $username = $_POST['username'];
        $message = $_POST['message'];
        // Insert into the database
        $insert_sql = "INSERT INTO comments_table (username, url, message)
                            VALUES ('{$username}', '{$url}', '{$message}');";
        $dbinsert_result = mysqli_query( $dbconnection, $insert_sql );
        if ( ! $dbinsert_result )
        {
            die();
        }
    }
    echo "<section>";
    echo "<h1>Comments</h1>";
    output_form();
    $retrieve_sql = "SELECT * FROM comments_table 
                        WHERE url = '{$url}'
                        ORDER BY id DESC"; 
    $dbretrieve_result = mysqli_query( $dbconnection, $retrieve_sql );
    if ( ! $dbretrieve_result )
    {
        die();
    }
    if ( mysqli_num_rows( $dbretrieve_result ) != 0 ) 
    {
        while ( $row = mysql_fetch_assoc( $dbretrieve_result ) ) 
        {
            echo "<article>
                    <h1>{$row['username']}</h1>
                    <p>{$row['message']}</p>
                </article>";
        }
    }
    echo "</section>";
    mysqli_free_result( $dbretrieve_result );
    mysqli_close( $dbconnection );
?>

PHP: Database Case Studies

Derek Bridge

Department of Computer Science, University College Cork

PHP: Database Case Studies

Case study I: a web page comments facility

MySQL database for the web page comments facility

The web page/PHP script

HTTP requests

The get method versus the post method

How to use the post method

Case study II: a web site comments facility

MySQL database for the web page comments facility

A typical web page/PHP script, wombats.php

The chunk of PHP, comments_script.php

Department of Computer Science,
University College Cork

A typical web page/PHP script, `wombats.php`

The chunk of PHP, `comments_script.php`