Pràctica ASI-C6-ASGBD: PHP i PostgreSQL

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Objectius

Desenvolupament

Dos manuals de PHP + PostgreSQL:

Nosaltres seguirem el primer, que està en anglès

En el servidor es crea la carpeta /var/www/asgbd/php_postgresql. Ens podem connectar a aquesta carpeta per SSH, i podem

accedir-hi via web a la url:

i així podem fer proves en el servidor. De totes maneres, seria bo que l'alumne configurés el seu Apache local per fer la pràctica en local.

Nota. Els usuaris de Ubuntu/Debian han d'instal.lar el paquet php5pgsql

Nota. Per als usuaris de Windows que utilitzin EasyPHP, teniu un minitutorial per configurar el Apache+PHP per accedir al PostgreSQL:

connectats com a postgres...

CREATE USER addressbook WITH ENCRYPTED PASSWORD 'addressbook';
CREATE DATABASE "addressbook" WITH TEMPLATE = template0 OWNER = "addressbook" ENCODING = 'UTF8' TABLESPACE = pg_default;

i ara ja podem entrar

$ psql -h localhost -p 5432 -U addressbook addressbook

Contraseña para usuario addressbook: addressbook


Les dades que farem servir en aquesta pràctica són:

CREATE TABLE addressbook (id serial, name varchar(255), address text, tel varchar(50), email varchar(255));
INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Bugs Bunny', 'The Rabbit Hole, Looney Toons, USA', '123 4567','bugs@wascallywabbit.net');
INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Robin Hood', 'Sherwood Forest', 'None', 
'robin@steal.from.the.rich');
INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Sherlock Holmes', '221B Baker Street, London 16550, England', 
'911 1822', 'holmes@bakerstreetirregulars.domain');


L'usuari asi2aXX crearà la taula addressbook_XX

I ara ja podem començar a escriure codi PHP:

1.php (l'alumne farà el codi 1_XX.php):

<html>
<head><basefont face="Arial"></head>
<body>
<?php
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// get the number of rows in the resultset
$rows = pg_num_rows($result);
echo "There are currently $rows records in the database.";
// close database connection
pg_close($connection);
?>
</body>
</html>


I ara farem una cosa més interessant: una consulta de la llista d'adreces, i mostrar-les de forma entenedora. Codi

2.php (2_XX.php):

<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?php
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT name, address FROM addressbook ORDER BY name"; $result
= pg_query($connection, $query) or die("Error in query: $query. " .
pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++) 
{
$row = pg_fetch_row($result, $i);
?>
<li><font size="-1"><b><? echo $row[0]; ?></b></font>
<br>
<font size="-1"><? echo $row[1]; ?></font>
<p>
<?php
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?php
}
// close database connection
pg_close($connection);
?>
</body>
</html>


Una altra manera de fer-ho Codi 3.php (3_XX.php):

<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?php
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT name, address FROM addressbook ORDER BY name"; $result
= pg_query($connection, $query) or die("Error in query: $query. " .
pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++) 
{
$row = pg_fetch_array($result, $i, PGSQL_ASSOC);
?>
<li><font size="-1"><b><? echo $row['name'];
?></b></font>
<br>
<font size="-1"><? echo $row['address']; ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?php
}
// close database connection
pg_close($connection);
?>
</body>
</html>

Codi 4.php (4_XX.php)::

<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT name, address FROM addressbook ORDER BY name"; $result
= pg_query($connection, $query) or die("Error in query: $query. " .
pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++) 
{
$row = pg_fetch_object($result, $i);
?>
<li><font size="-1"><b><? echo $row->name; ?></b></font>
<br>
<font size="-1"><? echo $row->address; ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?php
}
// close database connection
pg_close($connection);
?>
</body>
</html>

Ara farem una transacció: és un conjunt d'instruccions contra la base de dades que, o s'executen totes amb èxit, o no se

n'executa cap. Codi 5.php (5_XX.php):

Per tal de què s'executi el codi, hem de comentar ROLLBACK i descomentar COMMIT.

<?php
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// begin a transaction block
$query = "BEGIN WORK";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// generate some queries
$query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'),
'Spiderman', 'The Web, Somewhere In Your Neighborhood', 'None',
'spidey@neigborhood.com')"; $result = pg_query($connection, $query) or
die("Error in query: $query. " . pg_last_error($connection));
$query = "INSERT INTO addressbook values (nextval('addressbook_id_seq'),
'Bruce Wayne', 'Gotham City', '64928 34585', 'bruce@batcave.org')";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// now roll them back
$query = "ROLLBACK";
// if you want to commit them, comment out the line above
// and uncomment the one below
// $query = "COMMIT";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// now check to see how many records are there in the table
// and print this
$query = "SELECT * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection)); $rows = pg_num_rows($result); echo
"There are currently $rows records in the database";
// close database connection
pg_close($connection);
?>

Atrapar els errors. Codi 6.php (6_XX.php):

<html>
<head><basefont face="Arial"></head>
<body>
<?php
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECTA * FROM addressbook";
$result = pg_query($connection, $query) or die("Error in query: $query.
" . pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
echo "There are currently $rows records in the database";
// close database connection
pg_close($connection);
?>
</body>
</html>

Formulari web per fer una inserció a la taula. Codi 7.php (7_XX.php):

<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?php
// form not yet submitted
// display form

$name=$_POST['name'];
$address=$_POST['address'];
$tel=$_POST['tel'];
$email=$_POST['email'];
$submit=$_POST['submit'];

if (!$submit)
{
?>
<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="POST">
Name:<br>
<input name="name" type="text" size="50">
<p>
Address:<br>
<textarea name="address" rows="6" cols="40"></textarea>
<p>
Tel:<br>
<input name="tel" type="text" size="10">
<p>
Email:<br>
<input name="email" type="text" size="30">
<p>
<input type="submit" name="submit" value="Add">
</form>
<?php
}
else
{
// form submitted
// prepare to insert data
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "addressbook";
$pass = "addressbook";
$db = "addressbook";
// open a connection to the database server
$connection = pg_connect("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// error checks on form submission go here
// generate and execute a query
$query = "INSERT INTO addressbook VALUES
(nextval('addressbook_id_seq'), '$name', '$address', '$tel', '$email')";
$result = pg_query($connection, $query) or die("Error in query:
$query. " . pg_last_error($connection));
echo "Data successfully added.";
// close database connection
pg_close($connection);
}
?>
</body>
</html>

Entrega

Durada

2 hores


creat per Joan Quintana Compte, març 2010

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines