Create a Database Website With Apache24 PHP and PostgreSQL

PHP is a widely used server-side scripting language. Its vast capabilities and ease of use make PHP ideal for front-end web development, and while it allows us to create advanced structures, its basic usage is also easy to learn, making it a good starting point for beginners too.

However the data recorded, processed, sent or displayed by the applications we create needs to be stored somewhere, our web pages have no state without this data. We can store our data in various ways or even dispose of it after use, but the most standard way is to store it in a database designed solely to store data securely and present it when needed as quickly as possible.

PostgreSQL, also known as Postgres, is a free and open source relational database management system that emphasizes SQL extensibility and compliance. It was originally called POSTGRES, a reference to its origins as a successor to the Ingres database developed at the University of California.

In this article, we will learn how to create a simple web page to record and display user data. We will use PostgreSQL DBMS as backend and develop our PHP application which will run on Apache web server. Thus, we can access our web application from any common browser to view or add to our user database. The nature of web applications is that many users/operators can work on them simultaneously, all they need is a browser and network access to our application.

So that the PostgreSQL database can be connected to PHP and can be modified via the website page, your computer must have the specifications as below installed.


1. System Specifications
  • OS: FreeBSD 13.2
  • Hostname/Domain: ns1@unixexplore.com
  • IP Address: 192.168.5.2
  • phpPgAdmin 7.14.4-mod
  • Apache24
  • PHP82
  • modul dan extension PHP
  • PHP-FPM
  • PostgreSQL 15.3
Before we continue to part 2, it is recommended that you read the previous article: "Creating a postgresql database connection with PHP and Apache on FreeBSD". Because if PostgresQL is not connected to PHP, don't expect your website application to run perfectly.


2. Creating a New Table

The first step in creating this simple web application is to create a new table in the PostgresQL database. Here's how to make it.

root@ns1:~ # su - postgres
$ psql postgres
psql (15.4, server 15.3)
Type "help" for help.

postgres=# CREATE TABLE public.user
(
   id serial, 
   name character varying(250), 
   email character varying(250), 
   password character varying(250), 
   mobno bigint, 
   CONSTRAINT id PRIMARY KEY (id)
) 
WITH (
OIDS=FALSE
);
CREATE TABLE
postgres=# 
The description of the script above is to open a database with the name "postgres" and create a new table with the name "user". The contents of the "user" table consist of name, email, password and cellphone number. Below are the contents of the "user" table.

postgres=# SELECT * FROM "public"."user";
 id | name | email | password | mobno 
----+------+-------+----------+-------
(0 rows)
postgres=# 
In this example case, to input or enter data in the "user" table we will do it via the Google Chrome, Yandex or other web browser. So that the Google Chrome web browser can function to input data in the "user" table, we will create a php script with the names "register.php" and "login.php". We will place these 2 files in the "/usr/local/www/apache24/data" folder.

To complete the learning in this article, it is recommended that you read the article "How to Install and Configure PostgreSQL on FreeBSD 13.2". The following is the script that you have to type in the files "/usr/local/www/apache24/data/register.php" and "/usr/local/www/apache24/data/login.php"

root@ns1:~ # ee /usr/local/www/apache24/data/register.php
<?php
$host = "192.168.5.2";
$port = "5432";
$dbname = "postgres";
$user = "postgres";
$password = "router"; 
$connection_string = "host={$host} port={$port} dbname={$dbname} user={$user} password={$password} ";
$dbconn = pg_connect($connection_string);
if(isset($_POST['submit'])&&!empty($_POST['submit'])){
    
      $sql = "insert into public.user(name,email,password,mobno)values('".$_POST['name']."','".$_POST['email']."','".md5($_POST['pwd'])."','".$_POST['mobno']."')";
    $ret = pg_query($dbconn, $sql);
    if($ret){
        
            echo "Data saved Successfully";
    }else{
        
            echo "Soething Went Wrong";
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>PHP PostgreSQL Registration & Login Example </title>
  <meta name="keywords" content="PHP,PostgreSQL,Insert,Login">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
  <h2>Register Here </h2>
  <form method="post">
  
    <div class="form-group">
      <label for="name">Name:</label>
      <input type="text" class="form-control" id="name" placeholder="Enter name" name="name" requuired>
    </div>
    
    <div class="form-group">
      <label for="email">Email:</label>
      <input type="email" class="form-control" id="email" placeholder="Enter email" name="email">
    </div>
    
    <div class="form-group">
      <label for="pwd">Mobile No:</label>
      <input type="number" class="form-control" maxlength="10" id="mobileno" placeholder="Enter Mobile Number" name="mobno">
    </div>
    
    <div class="form-group">
      <label for="pwd">Password:</label>
      <input type="password" class="form-control" id="pwd" placeholder="Enter password" name="pwd">
    </div>
     
    <input type="submit" name="submit" class="btn btn-primary" value="Submit">
  </form>
</div>
</body>
</html>
root@ns1:~ # ee /usr/local/www/apache24/data/login.php
<?php
$host = "192.168.5.2";
$port = "5432";
$dbname = "postgres";
$user = "postgres";
$password = "router"; 
$connection_string = "host={$host} port={$port} dbname={$dbname} user={$user} password={$password} ";
$dbconn = pg_connect($connection_string);
if(isset($_POST['submit'])&&!empty($_POST['submit'])){
    
    $hashpassword = md5($_POST['pwd']);
    $sql ="select *from public.user where email = '".pg_escape_string($_POST['email'])."' and password ='".$hashpassword."'";
    $data = pg_query($dbconn,$sql); 
    $login_check = pg_num_rows($data);
    if($login_check > 0){ 
        
        echo "Login Successfully";    
    }else{
        
        echo "Invalid Details";
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>PHP PostgreSQL Registration & Login Example </title>
  <meta name="keywords" content="PHP,PostgreSQL,Insert,Login">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
  <h2>Login Here </h2>
  <form method="post">
  
     
    <div class="form-group">
      <label for="email">Email:</label>
      <input type="email" class="form-control" id="email" placeholder="Enter email" name="email">
    </div>
    
     
    <div class="form-group">
      <label for="pwd">Password:</label>
      <input type="password" class="form-control" id="pwd" placeholder="Enter password" name="pwd">
    </div>
     
    <input type="submit" name="submit" class="btn btn-primary" value="Submit">
  </form>
</div>
</body>
</html>
Now we open the Google Chrome, Firefox or Yandex web browser to fill in or input the "user" table. In the address bar menu of the Firefox web browser, type "http://192.168.5.2/register.php" and fill in the data in the web browser the.

Once you have finished entering the data as above, click the "Submit" button

Pay attention to the left side of the Firefox web browser, there is the text "Data saved successfully", meaning the data has been saved successfully. Do the steps above for up to 3 or 5 names. You can see the results as follows.

postgres=# SELECT * FROM "public"."user";
 id |     name      |        email        |             password             |    mobno    
----+---------------+---------------------+----------------------------------+-------------
  1 | iwan setiawan | datainchi@gmail.com | f82d7f6c2ab22a228da7c99dde71869c | 81289065249
  2 | M. Jaka       | inchi@gmail.com     | f82d7f6c2ab22a228da7c99dde71869c | 81289065241
  3 | Kanaka Robih  | data@gmail.com      | f82d7f6c2ab22a228da7c99dde71869c | 81289056247
(3 rows)

postgres=# 
The password displayed above is not the actual password. The original password is not displayed. Because for these 3 names, we created the passwords "router1", "router2" and "router3". Now we test to log in to these 3 names. In Firefox or Google Chrome type "192.168.5.2/login.php".

In this display, we try to log in with "datainchi@gmail.com" or the user name "iwan setiawan" with the password "router1", if the login data matches the data you entered in the input data file "register.php", it will appear "Login Successfully" text

This article is just a few examples of using a PostgreSQL database with PHP. You can read other articles about creating web applications based on PostgreSQL and PHP, because PostgreSQL is the best database that is no less popular than MySQL server.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post