#Lab Session 4 - Building Database-Web Applications in PHP - Solutions

###By Edmund Sadgrove

### University of New England

---

## Reading
* PHP Documentation: [http://php.net/manual/en/book.pgsql.php](http://php.net/manual/en/book.pgsql.php)

---

* Remember that you need to set execute and read access for php files to run from your public_html e.g. chmod a+rx *.php

## Book Post

```html

<html>

<head>
    <title>Simple Database Access</title>
</head>

<body>
    <?php
    ini_set("display_errors",E_ALL); 
    ini_set("display_startup_errors","On"); 
    error_reporting(E_ALL); 
    
    
    
    $pw = "220069300";
    $user = "esadgro2_apps";
    $db = "esadgro2_w5";

    $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
    $dbconn = pg_connect($conn_string);
    //connect to a database named "test" on the host "sheep" with a username and password


    // Performing SQL query
    $query = 'SELECT book_id, title FROM books';
    $result = pg_query($dbconn, $query) or die('Query failed: ' . pg_last_error());

    ?>
        <h4>Employee Details for:</h4>
        <form method="post" action="book_display.php">
        <select name="book_id">

    <?php
    	while ($data = pg_fetch_object($result)) {
            echo "\t\t<option value='$data->book_id'>$data->title</option>\n";
        }
            

    ?>

    </select>
    <input type="submit" value="Get Book Details">
    </form>
</body>

</html>

```

## Book Display

```html

<html>

<head>
<title>Simple Database Access</title>
</head>
    <body>
    <h3>Employee Information</h3>

    <?php
        ini_set("display_errors",E_ALL); 
        ini_set("display_startup_errors","On"); 
        error_reporting(E_ALL); 


        $book=$_POST['book_id'];

        $pw = "220069300";
        $user = "esadgro2_apps";
        $db = "esadgro2_w5";

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT * FROM books, authors, editions 
        	WHERE books.author_id=authors.author_id AND books.book_id=editions.book_id AND books.book_id=$1";
        $result = pg_query_params($dbconn,$query,array($book)) or die('Query failed: ' . pg_last_error());

        if($data = pg_fetch_object($result)) {
            echo "<b>$data->title by $data->first_name  $data->last_name, edition: $data->edition</b>";

        }
    ?>

    </body>
</html>

```