Use PostgreSQL and PHP on Windows

Connect to PostgreSQL from PHP

Open your php.ini file (most likely located in c:\winnt), find the section called Dynamic Extensions and un-comment the line that contains "extension=php_pgsql.dll". Save and close php.ini and restart Apache. If the Web server complains and says it's unable to load the dynamic library, you probably have entered the wrong path for extension_dir in php.ini (section Paths and Directories). Another possible -– though unlikely -- cause is that the file php_pgsql.dll isn't present in the extensions directory.

Now you should be able to run the following little PHP script:

<?  
$conn = pg_connect("host=localhost    
   port=5432    
   dbname=template1    
   user=postgres    
   password=********");  
$sql = "SELECT current_date AS today;";  
$result = pg_query($conn, $sql);  
$row = pg_fetch_object($result, 0);  
echo "Today is: " .$row->today;  
?>

The script isn't too exciting, but at least it shows you how to connect to the database and execute a query.

Note that the code above isn't supposed to be an example of best practices in PHP. Of course you should add error handling, use pg_close() to close the connection explicitly when you are done etc. The database template1 should be used as a template (what else?) for the new database(s) you create for your application(s), and you should create new users and give them appropriate privileges (i.e. don't connect as postgres).

For more information on how to create databases, users, tables etc., see the PostgreSQL documentation.

For more reading on how to use PHP's PostgreSQL functions, see the articles Migrate your site from MySQL to PostgreSQL Part 1 and Part 2, and of course the PHP documentation.

Useful Tools

Command line SQL can be fun if you are a real geek, but to manage the database(s), I used Aqua Data Studio v 3.0 from AquaFold, Inc. Well, it isn't MSSQL Enterprise Manager, or even MySQL Control Center, but it gives you a better view of the objects than you'd get through the psql interactive terminal. One nice feature of Aqua Data Studio is that you can connect to, and view in the same tree structure, other types of databases; MySQL, MSSQL, Oracle, and more.

If you just want to manage your PostgreSQL database, pgAdmin is the tool for you. I guess "tens of thousands of developers worldwide" can't be totally wrong. Remember, this isn't a software review, hence the few words. Another option is to use MS Access (after all, you're running Windows, so there's a high probability you also have MS Office) as a front-end for PostgreSQL, but then you have to install the psqlODBC driver first.

Since you have Apache and PHP, you could use phpPgAdmin, a browser based tool that's not completely dissimilar to phpMyAdmin for MySQL.

Epilogue

Hopefully, you found this article interesting, you couldn't wait to try the installation procedure yourself, and now you are busy absorbing knowledge about PostgreSQL (see links above). If you are a MySQL user, I'm sure you will "discover" useful functionality missing in MySQL. If you are a MSSQL user, you will appreciate that PostgreSQL is available free of charge, yet full of powerful features.

Do note that there's no uninstaller included in the Cygwin setup. If you want to remove the things you've added after reading this tutorial, you'll have to manually delete...

• HKEY_CURRENT_USER\Software\Cygnus Solutions
• HKEY_LOCAL_MACHINE\SOFTWARE\Cygnus Solutions
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ipc-daemon2
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postmaster
• the postgres user account
• the Cygwin installation directory (don't forget; you have to become owner of the data directory before you can delete it)
• the local packages directory

Good luck!