Joan: /* Desenvolupament */

2011-03-21T16:05:23Z

Desenvolupament

Pàgina nova

==Objectius==

==Desenvolupament==
*http://php.net/manual/en/book.pgsql.php

Dos manuals de PHP + PostgreSQL:

*http://www.devshed.com/c/a/PHP/PHP-and-PostgreSQL/
*http://www.wikilearning.com/tutorial/tutorial_de_php/6456-1

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:
*http://192.168.0.10/asgbd/php_postgresql/
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:
*[[DAI-C8-EC:_PHP_i_Postgres#Configuraci.C3.B3_de_EasyPHP_i_Postgres_.28usuaris_Windows.29]]

connectats com a postgres...

<pre>
CREATE USER addressbook WITH ENCRYPTED PASSWORD 'addressbook';
CREATE DATABASE "addressbook" WITH TEMPLATE = template0 OWNER = "addressbook" ENCODING = 'UTF8' TABLESPACE = pg_default;
</pre>
i ara ja podem entrar
<pre>
$ psql -h localhost -p 5432 -U addressbook addressbook
</pre>
Contraseña para usuario addressbook: addressbook

Les dades que farem servir en aquesta pràctica són:
<pre>
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');
</pre>

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):

<pre>
<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>
</pre>

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'''):
<pre>
<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>
</pre>

Una altra manera de fer-ho Codi '''3.php''' ('''3_XX.php'''):
<pre>
<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>
</pre>
Codi '''4.php''' ('''4_XX.php''')::
<pre>
<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>
</pre>

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'''.
<pre>
<?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);
?>
</pre>
Atrapar els errors. Codi '''6.php''' ('''6_XX.php'''):

<pre>
<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>
</pre>

Formulari web per fer una inserció a la taula. Codi '''7.php''' ('''7_XX.php'''):
<pre>
<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>
</pre>

==Entrega==

==Durada==
2 hores

{{Autor}}, març 2010
[[Categoria: IES Jaume Balmes]]
[[Categoria: Pràctiques ASI-C6-ASGBD]]

Pràctica ASI-C6-ASGBD: PHP i PostgreSQL - Historial de revisió

Joan: /* Desenvolupament */