PHP Database Interaction Overview
DATABASE
•
PHP has support for over 20 databases, including the most
popular commercial and open source varieties. Relational
database systems such as MySQL, PostgreSQL, and Oracle
are the backbone of most modern dynamic web sites.
•
In these are stored shopping-cart information, purchase
histories, product reviews, user information, credit-card
numbers, and sometimes even web pages themselves
DATABASES
•
There are two ways to access databases from PHP.
•
By using database-specific extension
•
By using the database-independent PEAR DB library.
•
There are advantages and disadvantages to each approach.
BY USING DATABASE-SPECIFIC
EXTENSION
•
If you use a database-specific extension, your code is intimately
tied to the database you’re using( e.g MySQL).
•
The MySQL extension’s function names, parameters, error
handling, and so on are completely different from those of the
other database extensions.
•
If you want to move your database from MySQL to
PostgreSQL, it will involve significant changes to your code.
•
Code that uses the PEAR DB is also typically a little slower
than code that uses a database-specific extension.
USING PEAR DB
•
The PEAR DB, on the other hand, hides the database-
specific functions from you; moving between database
systems can be as simple as changing one line of your
program.
•
For simple applications, the PEAR DB is preferred to the
database-specific extensions, not just for portability but also
for ease of use.
BY USING DATABASE-SPECIFIC
EXTENSION
•
Steps :
•
connection to the database
•
select a database to work with
•
execute the SQL query
•
fetch the data from the database and display it
•
Close the database connection.
OPENING DATABASE
•
PHP provides
pg_connect()
function to open a database connection.
•
pg_connect(host,port,dbname,user,passwd);
•
host : [Optional] The host name running database server. If not specified, then
default value is
localhost
•
Port: TCP/IP port to connect to on the server (Default: $PGPORT or 5432)
•
Dbname: Database to connect to (Default: $PGDATABASE)
•
User : [Optional] - The username accessing the database. If not specified, then default
is the name of the user that owns the server process or postges
•
passwd : [Optional] - The password of the user accessing the database. If not
specified, then default is an empty password.
EXAMPLE
<?
php
$dbconn2 = pg_connect("host=localhost port=5432 dbname=mary")Or
$dbconn2 = pg_connect("host=localhost port=5432 dbname=postgres user=postgres") ;
if($dbconn2)
echo “database connected”;
else
echo “not connected”;
?>
CLOSE CONNECTION
•
The connection created to the database will be closed
automatically when script ends.
•
If we want to close the connection before that then is
pg_close()
•
pg_close(connection name);
•
E.g. pg_close($dbconn2);
EXECUTION QUERY
pg_query(SQL, connection) using this
function query or command is sent to
postgres connection.
•
When you perform a SELECT query
on the database it will return a
postgres Resource that holds
everything from your postgres table.
•
resource pg_query ([ resource
$connection ], string $query )
•
This routine executes the query on
the specified database connection.
•
$sql =<<<EOF
•
SELECT * from COMPANY;
•
EOF;
•
$ret = pg_query($db, $sql);
•
if(!$ret)
•
{•
echo pg_last_error($db);
•
exit;
•
}
FETCHING DATA FROM ARRAY
array pg_fetch_row ( resource $result [, int $row ] )
This routine fetches one row of data from the result associated with the specified
result resource.
Result PostgreSQL query result resource, returned
by
pg_query()
,
pg_query_params()
or
pg_execute()
(among others).
rowRow number in result to fetch. Rows are numbered from 0 upwards. If omitted
or NULL, the next row is fetched.
•
<?php
•
$db = pg_connect( "$host $port $dbname
$credentials" );
•
if(!$db)
•
{ echo "Error : Unable to open database\n"; }•
else
•
{ echo "Opened database successfully\n"; }•
$sql =<<<EOF
•
SELECT * from COMPANY;
•
EOF;
•
$ret = pg_query($db, $sql);
•
if(!$ret)
•
{•
echo pg_last_error($db);
•
exit;
•
}
•
while($row = pg_fetch_row($ret))
•
{•
echo "ID = ". $row[0] . "\n";
•
echo "NAME = ". $row[1] ."\n";
•
echo "ADDRESS = ". $row[2] ."\n";
•
echo "SALARY = ".$row[4] ."\n\n";
•
}
•
pg_close($db); ?>
pg_fetch_array ( resource $result [, int $row [, int $result_type = PGSQL_BOTH ]] )
pg_fetch_array() returns an array that corresponds to the fetched row (record).
result : PostgreSQL query result resource, returned by
pg_query()
, or
pg_execute()
rowRow : number in result to fetch. Rows are numbered from 0 upwards. If omitted
or NULL, the next row is fetched.
result_type : An optional parameter that controls how the returned
array
is indexed.
result_type is a constant and can take the following values:
PGSQL_ASSOC, PGSQL_NUM and PGSQL_BOTH.
Using PGSQL_NUM, pg_fetch_array() will return an array with numerical indices,
usingPGSQL_ASSOC it will return only associative indices
while PGSQL_BOTH, the default, will return both numerical and associative indices.
PG FETCH ARRAY
•
<?php
$conn = pg_pconnect("dbname=publisher");if (!$conn) {echo "An error occurred.\n";
exit;
}
$result = pg_query($conn, "SELECT author, email FROM authors");
if (!$result) {echo "An error occurred.\n";
exit;
}
$arr = pg_fetch_array($result, 0, PGSQL_NUM);
echo $arr[0] . " <- Row 1 Author\n";
echo $arr[1] . " <- Row 1 E-mail\n";
$arr = pg_fetch_array($result, NULL, PGSQL_ASSOC);
echo $arr["author"] . " <- Row 2 Author\n";
echo $arr["email"] . " <- Row 2 E-mail\n";
$arr = pg_fetch_array($result);
echo $arr["author"] . " <- Row 3 Author\n";
echo $arr[1] . " <- Row 3 E-mail\n";
?>
PG_FETCH_OBJECT ()
•
pg_fetch_object ( resource $result [, int $row ])
•
pg_fetch_object() returns an object with properties that
correspond to the fetched row's field names.
•
Parameters :
•
Result : PostgreSQL query result resource, returned
by
pg_query()
, or
pg_execute()
.
•
Row : Row number in result to fetch. Rows are numbered
from 0 upwards. If omitted or NULL, the next row is fetched.
•
<?php
$database = "store";
$db_conn = pg_connect("host=localhost port=5432 dbname=$database");if (!$db_conn) {echo "Failed connecting to postgres database $database\n";
exit;
}
$qu = pg_query($db_conn, "SELECT * FROM books ORDER BY author");
while ($data = pg_fetch_object($qu))
•
{ echo $data->author . " (";echo $data->year . "): ";
echo $data->title . "<br />";
}
pg_free_result($qu);
pg_close($db_conn);
PG_FETCH_RESULT
•
pg_fetch_result ( resource $result , int $row ,
mixed
$field )
•
pg_fetch_result()
•
returns the value of a particular row and field (column) in a PostgreSQL result
resource.
•
Parameters
•
Result : PostgreSQL query result resource, returned
by
pg_query()
, or
pg_execute()
.
•
Row:Row number in result to fetch. Rows are numbered from 0 upwards. If omitted,
next row is fetched.
•
Field: A
string
representing the name of the field (column) to fetch, otherwise
an
int
representing the field number to fetch. Fields are numbered from 0 upwards.
EXAMPLE
•
<?php
$db = pg_connect("dbname=users user=me") || die();$res = pg_query($db, "SELECT * from emp");
$val = pg_fetch_result($res, 1, 0);
echo “Emp No of second row is : “. $val. "\n";
?>
PG_NUM_ROWS()
•
pg_num_rows ( resource $result )
•
pg_num_rows() will return the number of rows in a
PostgreSQL result resource.
•
Parameter:
•
$result : PostgreSQL query result resource, returned
by
pg_query()
,
pg_query_params()
or
pg_execute()
EXAMPLE
•
<?php
$result = pg_query($conn, "SELECT * from emp;");
$rows = pg_num_rows($result);
echo $rows . " row(s) returned.\n";
?>
•
The above example will output:
•
10 row(s) returned.
pg_num_fields — Returns the number of fields in a result.
pg_num_fields ( resource $result )
result : PostgreSQL query result resource, returned by
pg_query()
<?php
$result = pg_query($conn, "SELECT 1, 2");
$num = pg_num_fields($result);
echo $num . " field(s) returned.\n";
?>
The above example will output:
2 field(s) returned.
PG NUM FIELDS
PG_RESULT_ERROR()
•
pg_result_error — Get error message associated with result.
•
pg_result_error ( resource $result ) ;
•
Result :PostgreSQL query result resource, returned by
pg_query()
,
•
Returns a string. Returns empty string if there is no error. If there is an error associated
with the result parameter, returns false
•
<?php
$dbconn = pg_connect("dbname=publisher") or die("Could not connect"); if (!pg_connection_busy($dbconn)) {pg_send_query($dbconn, "select * from doesnotexist;");
}
$res1 = pg_get_result($dbconn);
echo pg_result_error($res1);
?>
•
.
PG_LAST_ERROR
•
pg_last_error ([ resource $connection ] )
•
pg_last_error() returns the last error message for a given connection.
•
Error messages may be overwritten by internal PostgreSQL (libpq) function calls.
It may not return an appropriate error message if multiple errors occur inside a
PostgreSQL module function.
•
<?php
$dbconn = pg_connect("dbname=publisher") or die("Could not connect");// Query that fails
$res = pg_query($dbconn, "select * from doesnotexist");
echo pg_last_error($dbconn);
?>
•
A string containing the last error message on the given connection, or false on
error
PG_FREE_RESULT
•
pg_free_result(resource $result): bool
•
pg_free_result() frees the memory and data associated
with the specified PostgreSQL query result resource.
•
This function need only be called if memory consumption
during script execution is a problem. Otherwise, all result
memory will be automatically freed when the script ends.
•
Returns true on success or false on failure.
PG_FREE_RESULT EXAMPLE
•
<?php
$db = pg_connect("dbname=users user=me") || die();$res = pg_query($db, "SELECT 1 UNION ALL SELECT 2");
$val = pg_fetch_result($res, 1, 0);
echo "First field in the second row is: ", $val, "\n";
pg_free_result($res);
?>
•
The above example will output:
•
First field in the second row is: 2
PG_INSERT
•
pg_insert(
resource $connection,
string $table_name,
array $assoc_array,
int $options = PGSQL_DML_EXEC
):
mixed
•
pg_insert() inserts the values of assoc_array into the table
specified by table_name. If options is specified,
pg_convert()
is
applied to assoc_array with the specified options.
•
Parameters
•
Connection
PostgreSQL database connection resource.
•
table_name
•
Name of the table into which to insert rows. The table table_name must at least have as many columns
as assoc_array has elements.
•
assoc_array
An array whose keys are field names in the table table_name, and whose
values are the values of those fields that are to be inserted.
•
Options
•
Any number
of PGSQL_CONV_OPTS, PGSQL_DML_NO_CONV, PGSQL_DML_ESCAPE, PGSQL_DML_EXEC, PGSQ
L_DML_ASYNC or PGSQL_DML_STRING combined. If PGSQL_DML_STRING is part of the options then
query string is returned. When PGSQL_DML_NO_CONV or PGSQL_DML_ESCAPE is set, it does not
call
pg_convert()
internally.
•
Return Values
Returns the connection resource on success, or false on failure. Returns string if PGSQL_DML_STRING is
passed via options.
PG_UPDATE
•
pg_update(
resource $connection,
string $table_name,
array $data,
array $condition,
int $options = PGSQL_DML_EXEC
):
mixed
•
pg_update() updates records that matches condition with data.
If options is specified,
pg_convert()
is applied to data with specified
options.
•
pg_update() updates records specified by assoc_array which
has field=>value.
PG_DELETE
•
pg_delete(
resource $connection,
string $table_name,
array $assoc_array,
int $options = PGSQL_DML_EXEC
):
mixed
•
pg_delete() deletes records from a table specified by the keys
and values in assoc_array. If options is specified,
pg_convert()
is
applied to assoc_array with the specified options.
PG_AFFECTED_ROWS
•
pg_affected_rows(resource $result): int
•
pg_affected_rows() returns the number of tuples (instances/records/rows) affected
by INSERT, UPDATE, and DELETE queries.
•
<?php
$result = pg_query($conn, "INSERT INTO authors VALUES ('Orwell', 2002, 'Animal Farm')");$cmdtuples = pg_affected_rows($result);
echo $cmdtuples . " tuples are affected.\n";
?>
•
The above example will output:
•
1 tuples are affected.
PEAR DB
•
PEAR - PHP Extension and Application Repository.
•
PEAR is a community-driven project governed by its
developers. The code in PEAR is partitioned in "packages". Each
package is a separate project with its own development team,
version number, release cycle, documentation and a defined
relation to other packages (including dependencies).
•
Packages are distributed as gzipped tar files with a description
file inside, and installed on your local system using the
PEAR
installer
.
MISSION AND PURPOSE
•
PEAR's mission is to provide reusable
components, lead innovation in PHP, provide
best practices for PHP development and
educate developers.
•
The purpose of PEAR is to provide:
•
A structured library of open-source code for PHP
users
•
A system for code distribution and package
maintenance
•
A standard style for code written in PHP
PEAR DB
•
DB is a database abstraction layer providing:
* an OO-style query API
* portability features that make programs written for one DBMS work
with other DBMS's
* a DSN (data source name) format for specifying database servers
* prepare/execute (bind) emulation for databases that don't support it
natively
* a result object for each query response
* portable error codes
* sequence emulation
* sequential and non-sequential row fetching as well as bulk fetching
* formats fetched rows as associative arrays, ordered arrays or objects
* row limit support
* transactions support
* table information interface
•
DB.php is required to include in every program.
PEAR DB BASICS
•
A
data source name (DSN) is a string that specifies where the database is
located,
what kind of database it is, the username and password to use when
connecting to the database, and more.
•
The components of a DSN are assembled into a URL-like string:
•
type(dbsyntax)://username:password@protocol+hostspec/database
•
The only mandatory field is
type, which specifies the PHP database backend to
use.
•
mysql:///webdb
•
mysql://localhost/webdb
•
mysql://bondview@localhost/webdb
•
mysql://bondview@tcp+localhost/webdb
•
mysql://bondview:007@localhost/webdb
CONNECTION
•
Once you have a DSN, create a connection to the database
using the connect( ) method. This returns a database object
you’ll use for tasks such as issuing queries and quoting
parameters:
•
$db = DB::connect(
DSN [, options ]);
•
The
options value can either be Boolean, indicating whether or
not the connection is to
•
be persistent, or an array of options settings
•
persistent : Connection persists between accesses
•
optimize : What to optimize for 'performance' and
'portability'. The default is 'performance'
•
debug : Display debugging information
ERROR CHECKING
•
PEAR DB methods return DB_ERROR if an error occurs. You can check for
this with DB::isError( ):
$db = DB::connect($datasource);
if (DB::isError($db))
{die($db->getMessage( ));
}
•
The DB::isError( ) method returns true if an error occurred while working
with the database object. If there was an error, the usual behavior is to stop
the program and display the error message reported by the getMessage( )
method. You can call getMessage( ) on any PEAR DB object.
ISSUING QUERY
•
The query( ) method on a database object sends SQL to the database:
•
$result = $db->query(
sql);
•
A SQL statement that doesn’t query the database (e.g., INSERT, UPDATE,
DELETE) returns the DB_OK constant to indicate success. SQL that
performs a query (e.g.,
•
SELECT) returns an object that you can use to access the results.
•
You can check for success with DB::isError( ):
•
$q = $db->query($sql);
•
if (DB::iserror($q)) {•
die($q->getMessage( ));
FETCHING RESULTS FROM A QUERY
•
PEAR DB provides two methods for fetching data from a query result
object.
•
One returns an array corresponding to the next row, and the other stores
the row array into a variable passed as a parameter.
•
The fetchRow( ) method on a query result returns an array of the next row
of results:
•
$row = $result->fetchRow([
mode ]);
•
This returns either an array of data, NULL if there is no more data, or
DB_ERROR if an error occurred.
•
Mode can be DB_FETCHMODE_ORDERED,DB_FETCHMODE_ASSOC,
•
DB_FETCHMODE_OBJECT
EXAMPLE
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
while ($row =$result->fetchRow())
{if (DB::isError($row))
{ die($row->getMessage( )); }else
echo $row[0] . ” ” $row[1]. “<br>”;
}
FETCHINTO()
•
DB_result::fetchInto()
•
DB_result::fetchInto() – Fetches a row of a result set into a
variable.
•
integer
fetchInto
( array &$arr , integer $fetchMode =
DB_FETCHMODE_DEFAULT , integer $rowNum = null )
•
mixed $arr : reference to a variable to contain the row
•
integer $fetchMode : the fetch mode to use. The default
is
DB_FETCHMODE_DEFAULT
, which tells this method to use DB's
current fetch mode. Potential values include:
•
DB_FETCHMODE_ORDERED
•
DB_FETCHMODE_ASSOC
•
DB_FETCHMODE_OBJECT
•
integer $rowNumthe row number to fetch. Note that 0 returns the first
row, 1 returns the second row, etc.
•
integer -
DB_OK
if a row is processed,
NULL
when the end of the result
set is reached or a
DB_Error
object on failure
•
// Once you have a valid DB object named $db...
$res = $db->query('SELECT * FROM emp');while ($res->fetchInto($row))
{echo $row[0] . "\n";
}
FINISHING RESULTS
•
A query result object typically holds all the rows returned by the query. This
may consume a lot of memory.
•
To return the memory consumed by the result of a query to the operating
system, use the free( ) method:
•
$result->free( );
•
This is not strictly necessary, as free( ) is automatically called on all queries
when the PHP script ends.
DISCONNECTING DATABASE
•
To force PHP to disconnect from the database, use the
disconnect( ) method on the database object:
•
$db->disconnect( );
•
This is not strictly necessary, however, as all database
connections are disconnected when the PHP script ends.
ADVANCED DATABASE
TECHNIQUES(PLACEHOLDERS)
•
By inserting values into a template, the PEAR DB can build a query by
inserting values into a template. Pass the query( ) function SQL with ? in
place of specific values, and add a second parameter consisting of the array
of values to insert into the SQL:
•
$result = $db->query(
SQL, values);
•
For example, this code inserts three entries into the movies table:
$movies = array(array('Dr No', 1962), array('Goldfinger', 1965),array('Thunderball', 1965));foreach ($movies as $movie)
{$db->query('INSERT INTO movies (title,year) VALUES (?,?)', $movie);}
•
There are three characters that you can use as placeholder values in an
SQL query:
•
? A string or number, which will be quoted if necessary (recommended)
•
| A string or number, which will never be quoted
•
& A filename, the contents of which will be included in the statement (e.g.,
for storing an image file in a BLOB field)
PREPARE/EXECUTE
•
When issuing the same query repeatedly, it can be more
efficient to compile the query once and then execute it
multiple times, using the prepare( ), execute( ), and
executeMultiple( ) methods.
•
The first step is to call prepare( ) on the query:
•
$compiled = $db->prepare(
SQL);
•
This returns a compiled query object
EXECUTE METHOD
•
The execute( ) method fills in any placeholders
•
in the query and sends it to the RDBMS:
•
$response = $db->execute(
compiled, values);
•
The
values array contains the values for the placeholders in the
query. The return
value is either a query response object, or
DB_ERROR if an error occurred.
EXAMPLE
For example, we could insert multiple values into the movies table like this:
$movies = array(array('Dr No', 1962),array('Goldfinger', 1965),array('Thunderball', 1965));$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)');foreach ($movies as $movie) {$db->execute($compiled, $movie);
}
EXECUTEMULTIPLE( ) METHOD
•
The executeMultiple( ) method takes a two-dimensional array of
values to insert:
•
$responses = $db->executeMultiple(
compiled, values);
•
The
values array must be numerically indexed from 0 and have values
that are arrays
of values to insert. The compiled query is executed
once for every entry in
values,
and the query responses are collected
in $responses.
•
A better way to write the movie-insertions code is:
•
$movies = array(array('Dr No', 1962),•
array('Goldfinger', 1965),•
array('Thunderball', 1965));•
$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES(?,?)');
•
$db->executeMultiple($compiled, $movies
);
SHORTCUTS
•
PEAR DB provides a number of methods that perform a
query and fetch the results in one step: getOne( ), getRow(
), getCol( ),
•
getAssoc( ), and getAll( ). All of these
•
methods permit placeholders.
GETONE()
•
The getOne( ) method fetches the first column of the first row of data returned by
an SQL query:
•
$value = $db->getOne(
SQL [, values ]);
•
For example:
•
$when = $db->getOne("SELECT avg(year) FROM movies");•
if (DB::isError($when)) {•
die($when->getMessage( ));
•
}
•
echo "The average James Bond movie was made in $when";
•
The average James Bond movie was made in 1977
GETROW( )
•
The getRow( ) method returns the first row of data returned by an
SQL query:
•
$row = $db->getRow(
SQL [, values ]]);
•
This is useful if you know only one row will be returned. For example:
•
list($title, $actor) = $db->getRow(
•
"SELECT movies.title,actors.name FROM movies,actors
•
WHERE movies.year=1977 AND movies.actor=actors.id");
•
echo "($title, starring $actor)";
•
(The Spy Who Loved Me, starring Roger Moore)
THE GETCOL( ) METHOD
•
The getCol( ) method returns a single column from the data
returned by an SQL query:
•
$col = $db->getCol(
SQL [, column [, values ]]);
•
The
column parameter can be either a number (0, the default, is
the first column), or
the column name.
•
For example, this fetches the names of all the Bond movies in
the database, ordered by the year they were released:
•
$titles = $db->getCol("SELECT title FROM movies ORDER BYyear ASC");
•
foreach ($titles as $title)
•
{•
echo "$title\n";
•
}
GETALL( ) METHOD
•
The getAll( ) method returns an array of all the rows returned by the query:
•
$all = $db->getAll(
SQL [, values [, fetchmode ]]);
•
For example, the following code builds a select box containing the names of the
movies. The ID of the selected movie is submitted as the parameter value.
•
$results = $db->getAll("SELECT id,title FROM movies ORDER BY year ASC");•
echo "<select name='movie'>\n";
•
foreach ($results as $result) {•
echo "<option value={$result[0]}>{$result[1]}</option>\n";•
}
•
echo "</select>";
•
All the get*( ) methods return DB_ERROR when an error occurs.
DETAILS ABOUT A QUERY RESPONSE
•
Four PEAR DB methods provide you with information on a query result
object:
•
numRows( ), numCols( ), affectedRows( ), and tableInfo( ).
•
The numRows( ) and numCols( ) methods tell you the number of rows and
columns returned from a SELECT query:
•
$howmany = $response->numRows( );
•
$howmany = $response->numCols( );
CONTINUED
•
The affectedRows( ) method tells you the number of rows
affected by an INSERT,
•
DELETE, or UPDATE operation:
•
$howmany = $response->affectedRows( );
•
The tableInfo( ) method returns detailed information on
the type and flags of fields returned from a SELECT
operation:
•
$info = $response->tableInfo( );
METADATA
•
The getListOf( ) method lets you query the database for information on
available
•
databases, users, views, and functions:
•
$data = $db->getListOf(
what);
•
The
what parameter is a string identifying the database feature to list. Most
databases
support "databases"; some support "users", "views", and
"functions".
•
For example, this stores a list of available databases in $dbs:
•
$dbs = $db->getListOf("databases");PHP provides support for over 20 databases, including popular options like MySQL, PostgreSQL, and Oracle. The backbone of modern dynamic websites, relational database systems store various data such as shopping cart details, purchase histories, user information, and more. PHP allows access to databases through database-specific extensions or the database-independent PEAR.DB library, each with its own advantages and disadvantages. Using a database-specific extension ties code closely to a particular database, while PEAR.DB offers portability and ease of use for simpler applications.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
DATABASE PHP has support for over 20 databases, including the most popular commercial and open source varieties. Relational database systems such as MySQL, PostgreSQL, and Oracle are the backbone of most modern dynamic web sites. In these are stored shopping-cart information, purchase histories, product reviews, user information, credit-card numbers, and sometimes even web pages themselves
DATABASES There are two ways to access databases from PHP. By using database-specific extension By using the database-independent PEAR DB library. There are advantages and disadvantages to each approach.
BY USING DATABASE-SPECIFIC EXTENSION If you use a database-specific extension, your code is intimately tied to the database you re using( e.g MySQL). The MySQL extension s function names, parameters, error handling, and so on are completely different from those of the other database extensions. If you want to move your database from MySQL to PostgreSQL, it will involve significant changes to your code. Code that uses the PEAR DB is also typically a little slower than code that uses a database-specific extension.
USING PEAR DB The PEAR DB, on the other hand, hides the database- specific functions from you; moving between database systems can be as simple as changing one line of your program. For simple applications, the PEAR DB is preferred to the database-specific extensions, not just for portability but also for ease of use.
BY USING DATABASE-SPECIFIC EXTENSION Steps : connection to the database select a database to work with execute the SQL query fetch the data from the database and display it Close the database connection.
OPENING DATABASE PHP provides pg_connect() function to open a database connection. pg_connect(host,port,dbname,user,passwd); host : [Optional] The host name running database server. If not specified, then default value is localhost Port: TCP/IP port to connect to on the server (Default:$PGPORT or 5432) Dbname: Database to connect to (Default:$PGDATABASE) User : [Optional] -The username accessing the database. If not specified, then default is the name of the user that owns the server process or postges passwd : [Optional] -The password of the user accessing the database. If not specified, then default is an empty password.
EXAMPLE <?php $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary") Or $dbconn2 = pg_connect("host=localhost port=5432 dbname=postgres user=postgres ") ; if($dbconn2) echo database connected ; else echo not connected ; ?>
CLOSE CONNECTION The connection created to the database will be closed automatically when script ends. If we want to close the connection before that then is pg_close() pg_close(connection name); E.g. pg_close($dbconn2);
EXECUTION QUERY pg_query(SQL, connection) using this function query or command is sent to postgres connection. $sql =<<<EOF SELECT * from COMPANY; EOF; When you perform a SELECT query on the database it will return a postgres Resource that holds everything from your postgres table. $ret = pg_query($db, $sql); if(!$ret) { resource pg_query ([ resource $connection ], string $query ) echo pg_last_error($db); This routine executes the query on the specified database connection. exit; }
FETCHING DATA FROM ARRAY array pg_fetch_row ( resource $result [, int $row ] ) This routine fetches one row of data from the result associated with the specified result resource. Result PostgreSQL query result resource, returned by pg_query(),pg_query_params() or pg_execute() (among others). rowRow number in result to fetch. Rows are numbered from 0 upwards. If omitted or NULL, the next row is fetched.
<?php { $db = pg_connect( "$host $port $dbname $credentials" ); echo pg_last_error($db); exit; if(!$db) } { echo "Error : Unable to open database\n"; } while($row = pg_fetch_row($ret)) else { { echo "Opened database successfully\n"; } echo "ID = ". $row[0] . "\n"; $sql =<<<EOF echo "NAME = ". $row[1] ."\n"; SELECT * from COMPANY; echo "ADDRESS = ". $row[2] ."\n"; EOF; echo "SALARY = ".$row[4] ."\n\n"; $ret = pg_query($db, $sql); } if(!$ret) pg_close($db); ?>
PG FETCH ARRAY pg_fetch_array ( resource $result [,int $row [,int $result_type = PGSQL_BOTH ]] ) pg_fetch_array() returns an array that corresponds to the fetched row (record). result : PostgreSQL query result resource, returned by pg_query(), or pg_execute() rowRow : number in result to fetch. Rows are numbered from 0 upwards. If omitted or NULL, the next row is fetched. result_type : An optional parameter that controls how the returned array is indexed. result_type is a constant and can take the following values: PGSQL_ASSOC,PGSQL_NUM and PGSQL_BOTH. Using PGSQL_NUM,pg_fetch_array() will return an array with numerical indices, usingPGSQL_ASSOC it will return only associative indices while PGSQL_BOTH, the default, will return both numerical and associative indices.
<?php $conn = pg_pconnect("dbname=publisher"); if (!$conn) { echo "An error occurred.\n"; exit; } $result = pg_query($conn,"SELECT author,email FROM authors"); if (!$result) { echo "An error occurred.\n"; exit; } $arr = pg_fetch_array($result,0,PGSQL_NUM); echo $arr[0] ." <- Row 1Author\n"; echo $arr[1] ." <- Row 1 E-mail\n"; $arr = pg_fetch_array($result,NULL,PGSQL_ASSOC); echo $arr["author"] ." <- Row 2Author\n"; echo $arr["email"] ." <- Row 2 E-mail\n"; $arr = pg_fetch_array($result); echo $arr["author"] ." <- Row 3Author\n"; echo $arr[1] ." <- Row 3 E-mail\n"; ?>
PG_FETCH_OBJECT () pg_fetch_object ( resource $result [,int $row ]) pg_fetch_object() returns an object with properties that correspond to the fetched row's field names. Parameters : Result : PostgreSQL query result resource, returned by pg_query(), or pg_execute() . Row : Row number in result to fetch. Rows are numbered from 0 upwards. If omitted or NULL, the next row is fetched.
<?php $database = "store"; $db_conn = pg_connect("host=localhost port=5432 dbname=$database"); if (!$db_conn) { echo "Failed connecting to postgres database $database\n"; exit; } $qu = pg_query($db_conn,"SELECT * FROM books ORDER BY author"); while ($data = pg_fetch_object($qu)) { echo $data->author ." ("; echo $data->year ."):"; echo $data->title ."<br />"; } pg_free_result($qu); pg_close($db_conn);
PG_FETCH_RESULT pg_fetch_result ( resource $result ,int $row ,mixed $field ) pg_fetch_result() returns the value of a particular row and field (column) in a PostgreSQL result resource. Parameters Result : PostgreSQL query result resource, returned by pg_query(), or pg_execute() . Row:Row number in result to fetch. Rows are numbered from 0 upwards. If omitted, next row is fetched. Field: A string representing the name of the field (column) to fetch, otherwise an intrepresenting the field number to fetch. Fields are numbered from 0 upwards.
EXAMPLE <?php $db = pg_connect("dbname=users user=me") || die(); $res = pg_query($db,"SELECT * from emp"); $val = pg_fetch_result($res,1,0); echo Emp No of second row is : .$val."\n"; ?>
PG_NUM_ROWS() pg_num_rows ( resource $result ) pg_num_rows() will return the number of rows in a PostgreSQL result resource. Parameter: $result : PostgreSQL query result resource, returned by pg_query(),pg_query_params() or pg_execute()
EXAMPLE <?php $result = pg_query($conn,"SELECT * from emp;"); $rows = pg_num_rows($result); echo $rows ." row(s) returned.\n"; ?> The above example will output: 10 row(s) returned.
PG NUM FIELDS pg_num_fields Returns the number of fields in a result. pg_num_fields ( resource $result ) result : PostgreSQL query result resource, returned by pg_query() <?php $result = pg_query($conn,"SELECT 1,2"); $num = pg_num_fields($result); echo $num ." field(s) returned.\n"; ?> The above example will output: 2 field(s) returned.
PG_RESULT_ERROR() pg_result_error Get error message associated with result. pg_result_error ( resource $result ) ; Result :PostgreSQL query result resource, returned by pg_query(), Returns a string. Returns empty string if there is no error. If there is an error associated with the result parameter, returns false <?php $dbconn = pg_connect("dbname=publisher") or die("Could not connect"); if (!pg_connection_busy($dbconn)) { pg_send_query($dbconn,"select * from doesnotexist;"); } $res1 = pg_get_result($dbconn); echo pg_result_error($res1); ?> .
PG_LAST_ERROR pg_last_error ([ resource $connection ] ) pg_last_error() returns the last error message for a given connection. Error messages may be overwritten by internal PostgreSQL (libpq) function calls. It may not return an appropriate error message if multiple errors occur inside a PostgreSQL module function. <?php $dbconn = pg_connect("dbname=publisher") or die("Could not connect"); // Query that fails $res = pg_query($dbconn,"select * from doesnotexist"); echo pg_last_error($dbconn); ?> A string containing the last error message on the given connection, or false on error
PG_FREE_RESULT pg_free_result(resource $result):bool pg_free_result() frees the memory and data associated with the specified PostgreSQL query result resource. This function need only be called if memory consumption during script execution is a problem. Otherwise, all result memory will be automatically freed when the script ends. Returns true on success or false on failure.
PG_FREE_RESULT EXAMPLE <?php $db = pg_connect("dbname=users user=me") || die(); $res = pg_query($db,"SELECT 1 UNIONALL SELECT 2"); $val = pg_fetch_result($res,1,0); echo "First field in the second row is:",$val,"\n"; pg_free_result($res); ?> The above example will output: First field in the second row is: 2
PG_INSERT pg_insert( resource $connection, string $table_name, array $assoc_array, int $options = PGSQL_DML_EXEC ):mixed pg_insert() inserts the values of assoc_array into the table specified by table_name. If options is specified,pg_convert() is applied to assoc_array with the specified options.
Parameters Connection PostgreSQL database connection resource. table_name Name of the table into which to insert rows. The table table_name must at least have as many columns as assoc_array has elements. assoc_array An array whose keys are field names in the table table_name, and whose values are the values of those fields that are to be inserted. Options Any number of PGSQL_CONV_OPTS,PGSQL_DML_NO_CONV,PGSQL_DML_ESCAPE,PGSQL_DML_EXEC,PGSQ L_DML_ASYNC or PGSQL_DML_STRING combined. If PGSQL_DML_STRING is part of the options then query string is returned. When PGSQL_DML_NO_CONV or PGSQL_DML_ESCAPE is set, it does not call pg_convert() internally. Return Values Returns the connection resource on success, or false on failure. Returns string if PGSQL_DML_STRING is passed via options.
PG_UPDATE pg_update( resource $connection, string $table_name, array $data, array $condition, int $options = PGSQL_DML_EXEC ):mixed pg_update() updates records that matches condition with data. If options is specified,pg_convert() is applied to data with specified options. pg_update() updates records specified by assoc_array which has field=>value.
PG_DELETE pg_delete( resource $connection, string $table_name, array $assoc_array, int $options = PGSQL_DML_EXEC ):mixed pg_delete() deletes records from a table specified by the keys and values in assoc_array. If options is specified,pg_convert() is applied to assoc_array with the specified options.
PG_AFFECTED_ROWS pg_affected_rows(resource $result):int pg_affected_rows() returns the number of tuples (instances/records/rows) affected by INSERT,UPDATE, and DELETE queries. <?php $result = pg_query($conn,"INSERT INTO authorsVALUES ('Orwell',2002,'Animal Farm')"); $cmdtuples = pg_affected_rows($result); echo $cmdtuples ." tuples are affected.\n"; ?> The above example will output: 1 tuples are affected.
PEAR DB PEAR - PHP Extension and Application Repository. PEAR is a community-driven project governed by its developers.The code in PEAR is partitioned in "packages". Each package is a separate project with its own development team, version number, release cycle, documentation and a defined relation to other packages (including dependencies). Packages are distributed as gzipped tar files with a description file inside, and installed on your local system using the PEAR installer.
MISSION AND PURPOSE PEAR's mission is to provide reusable components, lead innovation in PHP, provide best practices for PHP development and educate developers. The purpose of PEAR is to provide: A structured library of open-source code for PHP users A system for code distribution and package maintenance A standard style for code written in PHP
PEAR DB DB is a database abstraction layer providing: * an OO-style query API * portability features that make programs written for one DBMS work with other DBMS's * a DSN (data source name) format for specifying database servers * prepare/execute (bind) emulation for databases that don't support it natively * a result object for each query response * portable error codes * sequence emulation * sequential and non-sequential row fetching as well as bulk fetching * formats fetched rows as associative arrays, ordered arrays or objects * row limit support * transactions support * table information interface
PEAR DB BASICS A data source name (DSN) is a string that specifies where the database is located, what kind of database it is, the username and password to use when connecting to the database, and more. The components of a DSN are assembled into a URL-like string: type(dbsyntax)://username:password@protocol+hostspec/database The only mandatory field is type, which specifies the PHP database backend to use. mysql:///webdb mysql://localhost/webdb mysql://bondview@localhost/webdb mysql://bondview@tcp+localhost/webdb mysql://bondview:007@localhost/webdb
CONNECTION Once you have a DSN, create a connection to the database using the connect( ) method. This returns a database object you ll use for tasks such as issuing queries and quoting parameters: $db = DB::connect(DSN [, options ]); The options value can either be Boolean, indicating whether or not the connection is to be persistent, or an array of options settings
persistent : Connection persists between accesses optimize : What to optimize for 'performance' and 'portability'. The default is 'performance' debug : Display debugging information
ERROR CHECKING PEAR DB methods return DB_ERROR if an error occurs. You can check for this with DB::isError( ): $db = DB::connect($datasource); if (DB::isError($db)) { die($db->getMessage( )); } The DB::isError( ) method returns true if an error occurred while working with the database object. If there was an error, the usual behavior is to stop the program and display the error message reported by the getMessage( ) method. You can call getMessage( ) on any PEAR DB object.
ISSUING QUERY The query( ) method on a database object sends SQL to the database: $result = $db->query(sql); A SQL statement that doesn t query the database (e.g., INSERT, UPDATE, DELETE) returns the DB_OK constant to indicate success. SQL that performs a query (e.g., SELECT) returns an object that you can use to access the results. You can check for success with DB::isError( ): $q = $db->query($sql); if (DB::iserror($q)) { die($q->getMessage( ));
FETCHING RESULTS FROM A QUERY PEAR DB provides two methods for fetching data from a query result object. One returns an array corresponding to the next row, and the other stores the row array into a variable passed as a parameter. The fetchRow( ) method on a query result returns an array of the next row of results: $row = $result->fetchRow([ mode ]); This returns either an array of data, NULL if there is no more data, or DB_ERROR if an error occurred. Mode can be DB_FETCHMODE_ORDERED,DB_FETCHMODE_ASSOC, DB_FETCHMODE_OBJECT
EXAMPLE $row = $result->fetchRow(DB_FETCHMODE_ASSOC); while ($row =$result->fetchRow()) { if (DB::isError($row)) { die($row->getMessage( )); } else echo $row[0] . $row[1]. <br> ; }
FETCHINTO() DB_result::fetchInto() DB_result::fetchInto() Fetches a row of a result set into a variable. integer fetchInto ( array &$arr , integer $fetchMode = DB_FETCHMODE_DEFAULT , integer $rowNum = null )
mixed $arr : reference to a variable to contain the row integer $fetchMode : the fetch mode to use. The default is DB_FETCHMODE_DEFAULT, which tells this method to use DB's current fetch mode. Potential values include: DB_FETCHMODE_ORDERED DB_FETCHMODE_ASSOC DB_FETCHMODE_OBJECT integer $rowNumthe row number to fetch. Note that 0 returns the first row, 1 returns the second row, etc. integer - DB_OK if a row is processed,NULL when the end of the result set is reached or a DB_Error object on failure
// Once you have a valid DB object named $db... $res = $db->query('SELECT * FROM emp'); while ($res->fetchInto($row)) { echo $row[0] ."\n"; }
FINISHING RESULTS A query result object typically holds all the rows returned by the query. This may consume a lot of memory. To return the memory consumed by the result of a query to the operating system, use the free( ) method: $result->free( ); This is not strictly necessary, as free( ) is automatically called on all queries when the PHP script ends.
DISCONNECTING DATABASE To force PHP to disconnect from the database, use the disconnect( ) method on the database object: $db->disconnect( ); This is not strictly necessary, however, as all database connections are disconnected when the PHP script ends.
ADVANCED DATABASE TECHNIQUES(PLACEHOLDERS) By inserting values into a template, the PEAR DB can build a query by inserting values into a template. Pass the query( ) function SQL with ? in place of specific values, and add a second parameter consisting of the array of values to insert into the SQL: $result = $db->query(SQL, values); For example, this code inserts three entries into the movies table: $movies = array(array('Dr No', 1962), array('Goldfinger', 1965), array('Thunderball', 1965)); foreach ($movies as $movie) { $db->query('INSERT INTO movies (title,year) VALUES (?,?)', $movie); } There are three characters that you can use as placeholder values in an SQL query: ? A string or number, which will be quoted if necessary (recommended)
PREPARE/EXECUTE When issuing the same query repeatedly, it can be more efficient to compile the query once and then execute it multiple times, using the prepare( ), execute( ), and executeMultiple( ) methods. The first step is to call prepare( ) on the query: $compiled = $db->prepare(SQL); This returns a compiled query object
EXECUTE METHOD The execute( ) method fills in any placeholders in the query and sends it to the RDBMS: $response = $db->execute(compiled, values); The values array contains the values for the placeholders in the query. The return value is either a query response object, or DB_ERROR if an error occurred.
EXAMPLE For example, we could insert multiple values into the movies table like this: $movies = array(array('Dr No', 1962), array('Goldfinger', 1965), array('Thunderball', 1965)); $compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)'); foreach ($movies as $movie) { $db->execute($compiled, $movie); }
EXECUTEMULTIPLE( ) METHOD The executeMultiple( ) method takes a two-dimensional array of values to insert: $responses = $db->executeMultiple(compiled, values); The values array must be numerically indexed from 0 and have values that are arrays of values to insert. The compiled query is executed once for every entry in values,and the query responses are collected in $responses. A better way to write the movie-insertions code is: $movies = array(array('Dr No', 1962), array('Goldfinger', 1965), array('Thunderball', 1965)); $compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)'); $db->executeMultiple($compiled, $movies);
SHORTCUTS PEAR DB provides a number of methods that perform a query and fetch the results in one step: getOne( ), getRow( ), getCol( ), getAssoc( ), and getAll( ). All of these methods permit placeholders.
