WEB PROGRAMMING WITH PHP

MODULE 5
1. What is PostgreSQL?

PostgreSQL is an advanced, enterprise-class, and open-source relational database system.

PostgreSQL supports both SQL (relational) and JSON (non-relational) querying.

2. What is header() function in PHP?

header() function in PHP is used to send raw HTTP header and it must be called before any output is
sent to the requester, either by normal HTML tags, blank lines in a file, or from PHP.
The main purpose of header() function is to redirect user from the current/certain page to another
URL.

3. What is WAMP?

WAMP is an acronym that stands for Windows, Apache, MySQL, and PHP. It’s a software stack
which means installing WAMP installs Apache, MySQL, and PHP on your operating system

4. What are the important uses of header()?

• Redirect page: -It is used to redirect a from one web page to another web page in PHP.
• Set Content-Type in header response:If we want to change the Content-Type, we can
achieve that with the header() function.
• Set HTTP Status in the header response.
• Sent the response to a browser with no cache.
5. What are the features of PostgreSQL?

The essential features of PostgreSQL are as follows:

o Free to download: It is open-source, and we can easily download it from the official website
of PostgreSQL.
o Compatible on several operating systems: PostgreSQL runs on all major operating systems
such as Microsoft Windows, Linux, MacOS X, UNIX etc.
o Compatible with various programming languages: It supports multiple programming
interfaces such asC/C++, JAVA, Python, Perl, Ruby etc.
o Compatible with Data Integrity: It supports data integrity which includes the following:
Primary Keys, UNIQUE, NOT NULL, Foreign Keys, Explicit Locks, Advisory Locks
o Highly extensible: It supports procedural Languages such as Perl, PL/PGSQL, and Python,
etc. It also supportsStored procedures and functions.
o Secure: It is safe because it follows several security aspects.
o Highly Reliable: It is highly reliable and also provides disaster recovery.
6. Give detailed account on different data types in PostgreSQL
PostgreSQL supports the following data types:
1) Boolean - A Boolean data type can hold one of three possible values: true, false or null.
2) Character - PostgreSQL provides three character data types: CHAR(n), VARCHAR(n),
and TEXT
a) CHAR (n) is the fixed-length character. If you insert a string that is shorter than the length
of the column, PostgreSQL pads spaces.
b) VARCHAR (n) is the variable-length character string.
c) TEXT is the variable-length character string.
3) Numeric: PostgreSQL provides two distinct types of numbers: integers, floating- point
numbers.
a) Integer : There is three kinds of integers in PostgreSQL:
i) Small integer ( SMALLINT) is 2-byte signed integer.
ii) Integer ( INT) is a 4-byte integer.
iii) Serial is the same as integer except that PostgreSQL will automatically generate and
populate values into the SERIAL column.
b) Floating-point number: There are three main types of floating-point numbers:
i) float(n) is a floating-point number up to a maximum of 8 bytes.
ii) Real is a 4-byte floating-point number.
iii) numeric or numeric(p,s) is a real number with p digits with s number after the decimal
point.
4) Temporal data types: The temporal data types allow you to store date and /or time data.
a) DATEstores the dates only.
b) TIMEstores the time of day values.
c) TIMESTAMPstores both date and time values.
5) Special data types
Besides the primitive data types, PostgreSQL also provides several special data types related
to geometric and network.
a) box– a rectangular box.
b) line – a set of points.
c) point– a geometric pair of numbers.
d) lseg– a line segment.
e) polygon– a closed geometric.
f) inet– an IP4 address.
g) macaddr– a MAC address.
7. Explain how you will execute a PostgreSQL statement in PHP
Follow these four standard steps to execute an SQL query on a PostgreSQL database with PHP:
1. Create a connection to the RDBMS server by calling the pg_connect() function and passing it a
string containing information on the host name, port number, database name , user name, and
password. If a connection to the server is possible, a new connection resource object will be returned;
if not, the pg_connect() function will return false.

2. Create the SQL query string and execute it with the pg_query() method. For successful queries, a
result object is returned; for unsuccessful ones, the function returns false. If a query is unsuccessful,
you can retrieve the reason for failure via a call to pg_last_error().
3. For SELECT queries, you may process the result object further to extract data from it. The easiest
way to process the result object is with a while() loop, which calls the pg_fetch_array() to fetch the
next record in the result set as a numerically indexed array; this continues until no records are left to
process. If you prefer for each record to be returned as a string-indexed array,
pass pg_fetch_array() the PGSQL_ASSOC flag as an additional argument.

4. End the database session by destroying the result object with pg_free_result() and closing the
database connection with pg_close().

8. Explain the commands needed to establish a connection between a PHP script and PostgreSQL

To connect to PostgreSQL using native functions, follow these steps:

Database connections are opened using the pg_connect() function. This function takes a connect
string as its only argument and returns a database connection handle.
Syntax:
pg_connect(“host=hostname port=portnodbname=DatabaseName user=UserName
password=Password”);
Host: Name of the server to connect(localhost or 127.0.0.1)
Port: TCP/IP port to connect to on the server default value is 5432
Dbname: Database to connect
User: User name to use when connecting
Password: Password for the specified user
e.g
$conn=pg_connect(“host=127.0.0.1 port=5432 dbname=library”);
If the connection attempt fails, the pg_connect() function will return false
PHP provides a number of simple functions for retrieving information on the current database
connection based on the connection handle provided.
• pg_dbname() Returns the name of the current database
• pg_host() Returns the hostname associated with the current connection
• pg_port() Returns the port number of the current connection

The pg_query() function is responsible for sending the query to the selected database, returning a
result resource on success, and FALSE otherwise. For example, you would retrieve a result set
consisting of products

Syntax:
pg_query($connection,string query)
e.g
$result=pg_query($con,”Select * from product);
When executing SELECT statements, you might typically use pg_fetch_row(), pg_fetch_array(),
pg_fetch_object(), or pg_num_rows(); whereas for INSERT, UPDATE, or DELETE statements, you
might typically use pg_affected_rows().

9. What are the different functions used to Retrieving data with PHP in PostgreSQL?

pg_fetch_row() : - Get a row as an enumerated array

e.g
<?php
$conn = pg_pconnect("host=127.0.0.1 port=5432 dbname=publisher user=postegres");
$result = pg_query($conn, "SELECT author, price FROM Book");
while ($row = pg_fetch_row($result)) {
echo "Author: $row[0] Price: $row[1]";
echo "<br>";
}
?>
pg_fetch_assoc():-Function fetches a row as an associative array.
Examples
<?php
$conn = pg_pconnect("host=127.0.0.1 port=5432 dbname=publisher user=postegres");
$result = pg_query($conn, "SELECT author, price FROM Book");
while ($row = pg_fetch_row($result)) {
echo "Author: $row[‘author’] Price: $row[‘price’]";
echo "<br>";
}
pg_fetch_object():-Fetch a row as an object

Examples

<?php
$conn = pg_pconnect("host=127.0.0.1 port=5432 dbname=publisher user=postegres");
$result = pg_query($conn, "SELECT author, price FROM Book");
while ($row = pg_fetch_row($result)) {
echo "Author: “. $row->author;
echo “Price: “. $row->price;
echo "<br>";
}
?>
10. What is AJAX? Explain its implementation in PHP
AJAX is an acronym for Asynchronous JavaScript and XML.AJAX offers a technique to make
background server calls via JavaScript and retrieve additional data as needed, updating portions of the
page without causing full page reloads. Ajax is the method of exchanging data with a server, and
updating parts of a web page – without reloading the entire page.

AJAXis a method to request a server side script to execute via JavaScript. This request is generated in
a JavaScript function and sent to a PHP file. In server side, PHP file it receives and processes the
request passed through an AJAX call and send a response. The client-server communication will be
done in an asynchronous way. After getting a response from the PHP file, we can update a particular
component of a web page and avoid page refresh.

Ajax uses a combination of programming languages such as HTML/CSS, JavaScript, XML/JSON,

and a server-side scripting language (PHP, ASP.NET, etc.). It works by sending data from the
browser to the server, which processes it and sends back a response. This response is used by the
browser to update the web page without reloading it.
• A user action triggers an event in a browser (like a button click).
• The Ajax call activates, which sends a request to the server, using XML/JSON.
• The server-side script processes this request. It can also access the database if it needs to.
• The server then sends a response back to the browser.
• A second JavaScript function, called a callback function, receives the response and updates
the web page
11. How AJAX works?
AJAX communicates with the server using XMLHttpRequest object.

• User sends a request from the UI and a javascript call goes to XMLHttpRequest object.
• HTTP Request is sent to the server by XMLHttpRequest object.
• Server interacts with the database using JSP, PHP, Servlet, ASP.net etc.
• Data is retrieved.
• Server sends XML data or JSON data to the XMLHttpRequest callback function.
• HTML and CSS data is displayed on the browser.

12. Explain different AJAX Components

AJAX is not a technology but group of inter-related technologies. AJAX Technologiesincludes:
• HTML/XHTML and CSS
• DOM
• XML or JSON(JavaScript Object Notation)
• XMLHttpRequest Object
• JavaScript
• HTML/XHTML and CSS
These technologies are used for displaying content and style. It is mainly used forpresentation.
DOM
It is used for dynamic display and interaction with data.
XML or JSON(Javascript Object Notation)
For carrying data to and from server. JSON is like XML but short and faster than XML.
XMLHttpRequest Object
For asynchronous communication between client and server.
JavaScript
It is used to bring above technologies together. Independently, it is used mainly for clientside
validation.