CODEWITHSUNDEEP
ajaxpostgresql
Peter Dixon
Peter Dixon

Reputation: 21

How would I control PostgreSQL via a web page?

I know I would need to use AJAX to update the web page with the results. My idea is to run queries and send back results to the form. Usually this will simply be the success or failure of the query but sometimes text would need to be returned. The platform is Linux.

Upvotes: 1

Views: 1457

Answers (2)

Jose Marques
Jose Marques

Reputation: 748

This example is based on an example made on the site https://www.w3schools.com

getuser.php

<!DOCTYPE html>
<html>
<head>
<style>
table {
    width: 100%;
    border-collapse: collapse;
}

table, td, th {
    border: 1px solid black;
    padding: 5px;
}

th {text-align: left;}
</style>
</head>
<body>

<?php
$q = intval($_GET['q']);

$dbconn = pg_connect('localhost','peter','abc123','my_db');
if (!$con) {
    die('Could not connect');
}



$query = "SELECT * FROM user WHERE id = $1";
$result = pg_prepare($dbconn, "my_query", $query);

$data = array($q);
$result = pg_execute($dbconn, "my_query", $data);


echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = pg_fetch_row($result)) {
    echo "<tr>";
    echo "<td>" . $row[0] . "</td>";
    echo "<td>" . $row[1] . "</td>";
    echo "<td>" . $row[2] . "</td>";
    echo "<td>" . $row[3] . "</td>";
    echo "<td>" . $row[4] . "</td>";
    echo "</tr>";
}
echo "</table>";
pg_close($dbconn);
?>
</body>
</html>

The Html

<html>
<head>
<script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else {
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("txtHint").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET","getuser.php?q="+str,true);
        xmlhttp.send();
    }
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
  <option value="">Select a person:</option>
  <option value="1">Peter Griffin</option>
  <option value="2">Lois Griffin</option>
  <option value="3">Joseph Swanson</option>
  <option value="4">Glenn Quagmire</option>
  </select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

</body>
</html>

This is a simple example, for you to understand the basics. If you want more help start programming, and if you have any difficulties ask the community.

In the example above, when a user selects a person in the dropdown list above, a function called "showUser()" is executed.

The function is triggered by the onchange event.

Code explanation:

First, check if person is selected. If no person is selected (str == ""), clear the content of txtHint and exit the function. If a person is selected, do the following:

1) Create an XMLHttpRequest object

2) Create the function to be executed when the server response is ready

3) Send the request off to a file on the server

Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The page on the server called by the JavaScript above is a PHP file called "getuser.php".

The source code in "getuser.php" runs a preaper execute on a PostgreSQL database, and returns the result in an HTML table.

Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:

1) PHP opens a connection to a PostgreSQL server

2) The correct person is found

3) An HTML table is created, filled with data, and sent back to the "txtHint" placeholder.

Upvotes: 0

strash
strash

Reputation: 1321

You can use spring boot + hibernate to build a nice app. Works well with postgresql. A good approach is to implement @restcontroller

Upvotes: 1

Related Questions