634Part IVConnectionsListing 34-3(continued) submit.

); } ?>

Return to the main page.

First, we added a link to our now-finished insert form. Second, we are now passing the actionparameter via our submit button, purely for the sake of orderliness. We also encountered aproblem with our characters display. When we submitted a character with a space in itsname (okay, we admit, we were trying to submit Wonder Woman), it was unclear where onecharacter ends and the next one begins. Using a little regex and string concatenation, we venow caused this to display as a comma-separated list. Now we need a form for editing records. Listing 34-4 is what we ve come up with. Listing 34-4:edit.php

Cartoons and Characters Database

Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

633Chapter 34PostgreSQLinto an array. Next we call trim()to get rid of the superfluous space character left behind byexplode(). This is done because Windows systems submit an additional rwherever a linebreak occurs. If we explode on this as a separator, explodewill fail when the form is submit- ted from Linux, for example, because the character is not there. Finally, we iterate through the resulting array, putting each character into the characterstable with its own insert query, and we return a message of success or failure. When we went back to test our results, we found a few problems with index.phpand so wepresent a revised version in Listing 34-3. We could have just changed the original listing, butthis so nicely illustrates the debugging process that we ve included it this way to point outthe improvements. Listing 34-3:index.php (improved)

Cartoons and Characters Database

Your submission was successfully inserted. You can submit another, if you wish

); } else { print(

We were unable to insert the records as submitted. You can try again, if you wish

); } } else { print(

Welcome to the cartoons and characters database. Enter the ); print( name of your favorite cartoon below, and chooseContinued39
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

632Part IVConnectionsListing 34-2(continued) print(

Your submission was successfully inserted. You can submit another, if you wish

); } else { print(

We were unable to insert the records assubmitted. You can try again, if you wish

); } } else { print(

Welcome to the cartoons and characters database. Enter the ); print( name of your favorite cartoon below, and choosesubmit.

); } ?>

Return to the main page.

This script is doing a lot, so let s review it. Note first the separation of the PHP and HTML ele- ments. All of the conditional code appears at the top, and the conditional display require- ments are set up such that we don t have to weave in and out of PHP to get the job done. We re going to display a form even if a submission has just been made so that the user cansubmit entries one right after the other without an intermediate step. The first conditional, atthe top of the page, checks to see if the page is being called from the form. If not, we print asimple instruction set. If the page is the result of a form submission, the fun begins. We start with a connection to our database. This item is not tested as we just connected onour index page, so we will, perhaps perilously, assume a valid connection. The next step is togenerate the SQL for the cartoon table from the POST data and insert a new record into thedatabase. The next bit is designed to get the insert id of the inserted cartoon. Actually, PHPoffers pg_last_oid()for this purpose, but as of this writing, it did not yield the desiredresult and is currently deemed unreliable. Once we have the correct id, we do some manipulation on the contents of the textarea thatcontains our characters. Refer to Chapter 21 on arrays for more on explode(). Basically, we re splitting the field each place we find a line break and popping the resulting elements39
Check Tomcat Web Hosting services for best quality webspace to host your web application.

631Chapter 34PostgreSQL} else { print(

Connection to the cartoons database hasfailed

); } ?> Notice how different our connect parameters are from what we d use with MySQL. Theyaren t even comma separated! This function is even insensitive to the order in which they are supplied you simply have to use the appropriate parameter name and supply the corresponding value. Additional recognized parameters for this function are options, tty, and port. The rest of this script reads much like a similar script would for MySQL. A query is defined, we get the results of that as an object, issue a second query to get the character data, anduse the ubiquitous print statement to put it all into a nice html table. This is fine as far as it goes, but we don t yet have a way to insert, edit, and delete records. InListing 34-2, we create a new form for the purpose of inserting records. Listing 34-2:insert.php

Cartoons and Characters Database

If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

630Part IVConnectionsThe Cartoons DatabaseWe d like to be able to add some cartoons and characters to our database using a handy Web- entry system. We ve deliberately oversimplified our example to get through the key conceptsquickly, so it should be fairly easy to put together a system that allows us to achieve thistask. Listing 34-1 shows a welcoming page to our Cartoons database. Listing 34-1:index.php

Cartoons and Characters Database

Welcome to the cartoons and characters database. Existingentries are provided below. Use the provided functions to getmore details and to edit, add or delete entries.

0) { print(

); while ($sRow = pg_fetch_object($sResult)) { print(

); $tSql = select * from characters where id = $sRow->id ; $tResult = pg_query($tSql); print(

); } print(

); } else { print(

There are not currently any records in thecartoon database.

); }
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

629Chapter 34PostgreSQLon characters to cartoonfan; GRANT SELECT, INSERT, UPDATE, DELETEon cartoons_id_seq to cartoonfan; Note that we must issue this command at the table level. For security reasons, the wildcardcharacter does not function in this context. The last command given is necessary for theserialfield type we selected in the cartoons table. PHP and PostgreSQLTable 34-1 itemizes the PHP functions for working with PostgreSQL databases. There aremany more functions than we can possibly elaborate on here. Many of them will make senseonly after you have gained more familiarity with Postgres. Also, many of the function namesfor Postgres changed with PHP version 4.2. Because this is a PHP5 book, we re going to con- centrate on the new names rather than the old. Table 34-1: Common PostgreSQL Functions in PHPFunctionBehaviorpg_connect()andTakes a single connection string as an argument, enumerating pg_pconnect()connection parameters such as host, database, port, user and password. pg_pconnect()is the persistent version. Returns a connection resource. See the listings below for usage examples. pg_query()This is the standard pass-through mechanism for sending basic SQL to theserver. In earlier versions, it was called pg_exec(), but this name hasbeen deprecated. Although optional, pg_query()likes to see aconnection resource, followed by a comma before the actual SQL. pg_fetch_row()Each of these functions takes at least a query-result resource as an pg_fetch_assoc()argument and returns varying results depending on the function chosen pg_fetch_array()and how it is called. Each of these except pg_fetch_all()used to pg_fetch_object()require a counter argument if you wished to iterate through the returned pg_fetch_result()rows. This argument is still available but is not necessary. These functions pg_fetch_all()differ primarily in the results they return, which in the same order as theyare listed are: 1) a numerically indexed array starting at an offset of 0; 2) an associative array with field names as indices; 3) returns both a numericand an associative array; 4) returns the rows and values in objectnotation; 5) returns a specific row and column offset; and 6) returns amultidimensional array of the entire result set. pg_affected_rows()Returns the number of tuples (rows) affected by an INSERT, UPDATE, orDELETEquery. pg_free_result()Frees the memory used by a query result. pg_num_fields()Returns the number of fields in a query result. Use with SELECTstatements. pg_num_rows()Returns the number of rows in a query result. Use with SELECTstatements. pg_close()Closes the PostgreSQL Connection. Takes a connection resource as anargument.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

628Part IVConnectionsThe following command allows us to check our result: d cartoonsTable public.cartoons Column | Type | Modifiers———+———————–+———– id | integer | cartoon | character varying(30) | Just so we can say we did something relational, we ll create a second table to hold the namesof some of the characters in these cartoons. CREATE TABLE characters(id int4, character varchar(15)); Postgres offers an astonishing 47 data types, so obviously our example barely even scratchesthe surface. To be clear, some of these types are really other types with increasing data speci- ficity built in. (You may already be familiar with this concept, commonly called an input maskin other database tools.) Now we ll use some SQL to insert a record into the cartoons table. Note that because theserial type is just an integer with an auto-increment flag attached, we do not need to specifyanything for ID: INSERT INTO cartoons (cartoon) values( Scooby Doo ); The absence of an error message suggests our efforts have met with success, but it s a goodidea to check this with a SELECTstatement anyway, first to be sure and second because, after anumber of records have been entered, we may mentally lose track of where the auto-incrementvalue stands. We ll need the number to define a relationship with our characters table. SELECT * from cartoons; id | cartoon—-+———— 1 | Scooby Doo(1 row) Of course, we d be in pretty bad shape if we managed to mess that one up. Let s also put in acharacter or two into the characters table. INSERT INTO characters(id, character) VALUES(1, Shaggy ); INSERT INTO characters(id, character) VALUES(1, Daphne ); So we ve built a database, created a couple of tables, and added a couple of records, just tomake sure things are going well. Before we make a Web application out of this, however, weneed to create a minimally privileged user one who can access the tables in our sampledatabase, write to them, and read from them, but not modify them or investigate any otheraspect of the system. In MySQL, users that did not already exist were implicitly created by the GRANTcommand. While Postgres also uses the GRANTsyntax, we must explicitly create a user first: CREATE USER cartoonfan PASSWORD secretword ; After creating the user, we must give cartoonfansome privileges: GRANT SELECT, INSERT, UPDATE, DELETE on cartoons to cartoonfan; GRANT SELECT, INSERT, UPDATE, DELETE39
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

627Chapter 34PostgreSQLBecause a database is a mandatory argument to the interactive client and we don t want towork directly on one of our template databases, we ll start by creating a single database thatwe ll use for the rest of this chapter: createdb sampleThat s all there is to creating a new database, but of course, this is just a blank slate at thispoint a minimalist canvas based on the template database and on which we will paint astructure to our exacting specifications. Now we can use the interactive client to work with our database: psql sampleThis will open a copy of sampleand drop us at a prompt along with some instructions. Theprompt inside the interactive client is different from the shell prompt, which can help youdistinguish where you are exactly. This is especially useful on a Linux system where multipleshells open at one time is an everyday occurrence. The prompt will take the form=#, such that our prompt will look like: sample=# If you issue the ?command from this prompt, you ll get a very long list of everything youcan do from this prompt, exclusive of SQL specifics, which of course are also supported here. We can t offer exhaustive coverage here, but a few key commands are worth exploring. .The hcommand lists available help for all of the supported SQL constructs such asSELECT, DELETE, GRANT, and so on. .The dcommand, along with one of its accepted parameters, will display informationabout your database or specific objects in it. Of specific interest are dt, which will listall tables in the current database, and d
, which will show the structureof the specified table. .Hturns on HTML output, which is handy for exporting data quickly to the Web. In con- junction with the Tcommand you can customize the html output somewhat, and youcan use oto send it all to a file. Incidentally, you can call all of these options on the command line when starting psql. Simply substitute a hyphen for the slash and psqlwill execute the commands in sequenceand then exit. For even greater utility, you can group these commands in a text file and readthem in from the command line. Down to Real WorkLet s build a simple structure inside our sample database. This example is necessarily abbre- viated, but it is designed to give you a quick but useful familiarity with the Postgres and itsSQL syntax. If you have already exited from our previous example, get back in to the sampledatabase: psql sampleLet s start by defining a simple table to hold the names of some cartoons we really like: CREATE TABLE cartoons(id serial, cartoon varchar(30));
You want to have a cheap webhost for your apache application, then check apache web hosting services.

624Part IVConnectionslovers in the audience, it may seem odd to sell these features as disadvantages. Don t flameus yet. We re just taking a moment to expound on the Keep It Simple Stupid (KISS) philoso- phy. You don t need a sledgehammer to drive in a picture nail, and you don t need an objectrelational database to store addresses and phone numbers. Some folks will debate this point, but simply put, PostgreSQL is not as fast as MySQL underlow-load circumstances. At some point, PostgreSQL s more robust design will offer perfor- mance advantages in really large datasets, but on average, MySQL is just that little bit perkierfor lots of reads. Again, if you have simpler data storage and retrieval needs, there s no needfor you to go swimming around in the PostgreSQL waters. Finally, PostgreSQL is more complicated. Permissions management, for one, is not as clear- cut as it is with MySQL. PostgreSQL also offers some features that may cause the novice seyes to glaze over Schemas and Stored Procedures, while useful, aren t strictly necessaryand you may find them to be clutter. Some people work much better when there are nononessentials on their desks; the same principle may hold true here. All these things said, on balance PostgreSQL is a great tool a superlative tool even formany jobs. Its userbase may be smaller than MySQL s, but its devotees tend to be very loyal. We can t cover it very comprehensively and still stay within the focus of this book; but ifyou ve gotten this far, weighed all the benefits and disadvantages, and you choosePostgreSQL, the rest of this chapter should set you off in the right direction. Why Object-Relational Anyway? Object relational databases (ORDBMS) are a relatively new class of product compared to therelational database model that was developed in the early 1970s. In addition to implementingthe relational model discussed in Chapter 13, ORDBMS borrow from pure object databases, which excel at handling media objects, spatial, and series style data. An ORDBMS implementsobject properties on the components of a relational database in order to have the benefit ofboth worlds. This, in turn, facilitates interaction with the object features of PHP. BecausePHP s object model is significantly more powerful in version 5, there s never been a bettertime to use these two tools together. This means that choosing PostgreSQL potentially offers the developer greater extensibility. It s fairly easy to define and add custom data types, operators, functions, and indexing meth- ods. This has far reaching implications for fine-tuning performance; especially when workingwith particularly complicated data structures. At the data structure level, PostgreSQL tables and objects can benefit from either static ordynamic inheritance. That is to say, a child object created from its parent can be made toadopt characteristics identical to those of the parent either just once when it is created, orperpetually (as changes are made to the parent, they are passed on to the children). Installing PostgreSQLPostgreSQL is known to run on Linux, most Unix variants, and with the help of the Cygwinframework (http://cygwin.com/), can even made to be run on Windows although if per- formance is your priority, this arrangement is suboptimal. In keeping with the focus of thisbook, we re going to follow the steps for a typical Linux installation. The procedure for the39
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

PostgreSQLYou might find that MySQL or even simple text files meet all yourdata storage and retrieval requirements. Nothing about a simpleflat data structure in a small quantity demands a relational databasemodel. However, as we mentioned earlier in this book, you dohavechoices when it comes to databases. In the next chapter, we ll look ata commercial offering, Oracle. In this chapter, we ll look at what ispossibly the granddaddy of the free/open source database alterna- tives, PostgreSQL (pronounced post-gress-q-l ). Why Choose PostgreSQL? This is the part where the open-source purists start waving theirhands in the air and yelling with uncontrolled excitement! And theexcitement is easy to understand PostgreSQL is a true open-sourcedatabase, made available under the simple and portable BSD license. You can read the almost vanishingly short text of the license atwww.postgres.org/licence.html. Are you back yet? See, we told you it was short. So reason numberone is not so much the license itself as the freedom from an 85 pageEULA laced with sneaky provisions that nobody alive really under- stands, and that, in many cases, you can t even see until you get thebox open. By then it s too late all the money s gone. Which bringsus to reason number two: PostgreSQL is free. We don t mean free onMondays, Wednesdays, Fridays and the vernal equinox, nor do wemean free to the right people, nor even the more conventional andarguably understandable free for non-commercial use. PostgreSQLis completely and totally free (unless the developers change theirminds). The term free applies to more than just the cost. Like the GNUGeneral Public License, you can alter, repackage, and redistributePostgreSQL as a standalone product or with your own applications. Arguably better than the GPL for businesses, using and distributingPostgreSQL will not infect all your code with the copyleft. Finally, PostgreSQL supports some nifty special features and ele- ments of the ANSI SQL92 and SQL99 standards that simply aren tavailable or fully developed in other databases, as well as the abilityto work with object and hierarchical data. Of course, there are some disadvantages as well. First, considerPostgreSQL s ability to work with object and hierarchical data. Wait aminute; didn t we just sell that one as a feature? To the PostgreSQL3434CHAPTER …In This ChapterWhy PostgreSQLInstallation and setupAdministrationPostgreSQL …
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.