11.2 Inserting and Retrieving Data

Connecting to the database is just not enough. In this section you will learn how to communicate with the database, how to create tables, and how to insert and retrieve data.

The first thing to do is to create a new table. Therefore you can write a short PHP script:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $sql = "CREATE TABLE person (id serial, name text, location text)";         $stat = pg_exec($dbh, $sql);         if      ($stat)         {                 echo "The table has successfully been created<br>\n";         }         else         {                 echo "creating the table failed<br>\n";         }         pg_close($dbh); ?> 

After connecting to the database, a string containing the SQL code is generated. In the next step the query is sent to the server. pg_exec returns a status code that can be checked. Two parameters have to be passed to pg_exec: The first parameter is the connection handle and the second parameter contains the SQL statement you want to execute. If the statement has been executed successfully, the following text will be displayed:

 connection to phpbook established ... The table has successfully been created 

To see if the table is really in the database, you can use psql and \d:

 phpbook=# \d person                              Table "person"  Attribute |  Type   |                     Modifier -----------+---------+---------------------------------------------------  id        | integer | not null default nextval('"person_id_seq"'::text)  name      | text    |  location  | text    | Index: person_id_key 

Now that you have created a table, you can start inserting records. The next example shows how one record can be added to the table:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $sql = "INSERT INTO person (name, location) VALUES ('Paul', 'Vienna')";         $stat = pg_exec($dbh, $sql);         if      ($stat)         {                 echo "The record has been inserted successfully<br>\n";         }         else         {                 echo "creating the table failed<br>\n";         }         pg_close($dbh); ?> 

As you can see, adding records to the table works the same way as adding tables or executing any other SQL statement. In this regard, PHP is flexible and easy to use.

The text displayed by PHP is not surprising:

 connection to phpbook established ... The record has been inserted successfully 

Now that the table contains some data, you can retrieve some records. Retrieving data is slightly more complex than inserting data or making other changes. To retrieve data, you must execute the query first. In the next step, the number of records in the result can be computed. Finally, the data can be retrieved using a simple loop as shown in the next listing:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $sql = "SELECT id, name, location FROM person";         $stat = pg_exec($dbh, $sql);         if      ($stat)         {                 $rows = pg_numrows($stat);                 echo "$rows lines returned by PostgreSQL<br>\n";         }         else         {                 echo "an error has occurred while processing ($sql)<br>\n";         }         pg_close($dbh); ?> 

For computing the number of lines returned by a query, you can use pg_numrows. In the scenario shown in the preceding listing, the number of lines is displayed on the screen:

 connection to phpbook established ... 1 lines returned by PostgreSQL 

In the next example you can see how the lines returned by a query can be retrieved and displayed:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "SELECT id, name, location FROM person");         $rows = pg_numrows($stat);         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_row($stat, $i);                 echo "data: $data[0], $data[1], $data[2]<br>\n";         }         pg_close($dbh); ?> 

After computing the number of lines, a loop goes through all records and retrieves the data in the result. To extract one line of data, the command pg_fetch_row has to be used. The command returns an array that can easily be processed and displayed on the screen using echo:

 connection to phpbook established ... data: 1, Paul, Vienna 

In this example, a fixed number of columns is used and displayed on the screen. In many cases, however, a dynamic number of columns has to be displayed. Therefore the number of columns has to be computed at runtime. The next example shows how this can be done and how data can be displayed:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "SELECT id, name, location FROM person");         $rows = pg_numrows($stat);         $cols = pg_numfields($stat);         echo "rows: $rows; columns: $cols<br>\n";         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_row($stat, $i);                 for     ($j = 0; $j < $cols; $j++)                 {                         echo "$data[$j] ";                 }                 echo "<br>\n";         }         pg_close($dbh); ?> 

pg_numfields is used to retrieve the number of columns in the result. Just like the result of pg_numrows, it is used by a loop. The output of the script is not surprising:

 connection to phpbook established ... rows: 1; columns: 3 1 Paul Vienna 

One line of data is displayed.

In the past few examples, data has been retrieved using pg_fetch_row. The next example shows how the same result can be achieved by using pg_fetch_array:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "SELECT id, name, location FROM person");         $rows = pg_numrows($stat);         echo "rows: $rows<br>\n";         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_array($stat, $i);                 echo $data["id"]." ".$data["name"]." ".$data["location"];                 echo "<br>\n";         }         pg_close($dbh); ?> 

This time the data can be accessed by using the name of the column. As you can easily imagine, this is far better than working with indexes because the columns in the result cannot be mixed up. The output of the script is similar to the one you have already seen:

 connection to phpbook established ... rows: 1 1 Paul Vienna 

An important point when working with pg_fetch_array is to see how you can work with aliases:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "SELECT cos(id) AS a FROM person");         $rows = pg_numrows($stat);         echo "rows: $rows<br>\n";         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_array($stat, $i);                 echo "data: ".$data["a"]."<br>\n";         }         pg_close($dbh); ?> 

An alias is used for the field called cos, which contains the cosine of the id. To extract the data from the result, you have to access the alias in order to find the correct result. As you can see in the next listing, it works perfectly:

 connection to phpbook established ... rows: 1 data: 0.54030230586814 

In many cases, you need to retrieve information about the result itself. You have already seen how to compute the number of rows returned by a query. In the next example you will see how to obtain some additional information:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br><br>";}         $stat = pg_exec($dbh, "SELECT id, name, location FROM person");         $myid = pg_fieldname($stat, 0);         echo "field number 1 is called $myid<br>\n";         $myname = pg_fieldnum($stat, "name") + 1;         echo "'name' is field number $myname<br>";         $myloc = pg_fieldsize($stat, 2);         echo "the third field is $myloc bytes long<br>";         pg_close($dbh); ?> 

To find the name of a column, you can use pg_fieldname. Just pass the name of the result and the index of the column to the function, and PHP and PostgreSQL will do the rest for you. To find the id of a certain column, PHP offers the pg_fieldnum function. This time the name of the result and the name of the column have to be passed to the function.

The last function discussed in this example is pg_fieldsize, which returns the length of a column. In the case of a variable length, the function will return -1 because the length cannot be computed precisely.

The next listing shows the output when the script is executed:

 connection to phpbook established ... field number 1 is called id 'name' is field number 2 the third field is -1 bytes long 

Complex operations with a lot of data involved might use a lot of memory. If many memory-consuming requests have to be processed simultaneously, your Web server might run out of memory and the performance of your system could decrease significantly because of swapping and inefficient behavior. To reduce the amount of memory used during complex operations, PHP provides a command called pg_freeresult. If the result of a query is not needed any more, this command will help you to free the memory allocated by PHP to store the result returned by PostgreSQL. Normally the memory allocated by PHP is freed at the end of the script automatically. pg_freeresult can be used to free it earlier in the process. Let's take a look at an example:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $result = pg_exec($dbh, "SELECT cos(id) AS a FROM person");         for     ($i = 0; $i < pg_numrows($result); $i++)         {                 $data = pg_fetch_array($result, $i);                 echo "data: ".$data["a"]."<br>\n";         }         $stat = pg_freeresult($result);         if      ($stat)         {                 echo "memory freed successfully<br>\n";         }         pg_close($dbh); ?> 

In this example the memory is freed manually and the script checks whether the command has been successful:

 connection to phpbook established ... data: 0.54030230586814 memory freed successfully 

No errors were displayed and the script terminated without any problems.

In many cases, the functions you have just seen are essential, and they are the basis of many sophisticated applications.