How would I control PostgreSQL via a web page?

Question 1

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.

Question 2

This looks a bit open-ended. What do you already have in place — PHP scripts, client-side code etc?

Question 3

Nothing. It's a greenfield situation. Well, that's not entirely true. There's a bunch of SQL waiting to be applied from a old school DBMS.

Question 4

You can do this through ajax, json, jscript and php

Question 5

I gathered that. The part that I'm missing seeing is how the web server communicates back and forth with PostgreSQL.

Question 6

Which programming language are you using?

Question 7

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

Question 8

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.

Question 9

Yep, I think that's the core of what I want, thanks.

Question 10

If you find it useful, give a score or a flag. For others who seek the same thing find it quicker.

Question 11

I did hit the up arrow but I don't have the reputation to make that show. How do I score an answer?

strash strashstrash 1,3213 gold badges15 silver badges29 bronze badges · Accepted Answer · 2017-04-10 06:09:07Z

1

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

Share

Improve this answer

answered Apr 10, 2017 at 6:09

strash's user avatar

strash strashstrash

1,3213 gold badges15 silver badges29 bronze badges

Add a comment |

CollectivesTM on Stack Overflow

How would I control PostgreSQL via a web page?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Hot Network Questions

CollectivesTM on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Related