[Lazy Vulpes]

I've decided to try out postgres, but I can't seem to even get a connection going. I'm pretty sure I got the installation right, and I have pgAdmin 3 up and running.

This is my code:

<?php
$dbconn = pg_connect("host=*** dbname=*** user=*** password=***")
or die('Could not connect: ' . pg_last_error());
echo("test");
?>

Nothing shows up. Please help..

[Atli]

Do you have error reporting enabled? An empty screen usually indicates that a page has a fatal error but PHP is configured to hide such errors. Try adding this to the top of the page.

ini_set('display_errors', true);
error_reporting(E_ALL);

Also, I suggest you use PDO instead of the PostgreSQL extension. That way you don't have to keep learning new database-specific APIs every time you switch databases. You can just use PDO for all of them. (The most common once, at least.)

Here's an example of how PDO can be used:

<?php
if (!isset($_POST['email'], $_POST['name'], $_POST['whatever'])) {
	die("Email, name and whatever expected as POST data.");
}

try {
	// Connect to PostgreSQL, and have it throw exceptions when
	// errors occur.
	$dbLink = new PDO("pgsql:host=localhost;dbname=testdb", "User", "PWD");
	$dbLink->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
	die("Failed to connect to PostgreSQL : " . $e->getMessage());
}

try {
	// Insert a row of data based on user supplied POST values.
	// Because I use a prepared statement, the threat of 
	// SQL Injection is avoided completely!
	$sql = "INSERT INTO stuff (email, name, whatever) 
			VALUES(:email, :name, :whatever)";
	$stmt = $dbLink->prepare($sql);
	$stmt->bindValue(':email', $_POST['email']);
	$stmt->bindValue(':name', $_POST['name']);
	$stmt->bindValue(':whatever', $_POST['whatever']);
	$stmt->execute();
	if ($stmt->rowCount() === 1) {
		echo "Record inserted!";
	}
	else {
		echo "Failed to insert record!";
	}
}
catch (PDOException $e) {
	die("Failed to insert record: " . $e->getMessage());
}

The brilliant part about this is, if you ever switch to another database that supports these types of standard SQL queries (like MySQL, MSSQL, Oracle, SQLite), all you have to change in order to do that is the connection string passed to the PDO constructor. Just changing line #9 to this will make the code use MySQL instead:

$dbLink = new PDO("mysql:host=localhost;dbname=testdb", "User", "PWD");

[Lazy Vulpes]

I didn't have error reporting enabled. Thanks for that, was very annoyed that I never did get any error messages. The error was as following:

Fatal error: Call to undefined function pg_connect()

Installing the pdo_postgres driver went like a breeze with the ubuntu sofware center. (I love that thing!)

I really know nothing about PDO and it seems to have the effect of having my eyes bleed, but I will give it a try.

Thanks Atli, I appreciate your help! ( I haz data now )

This post has been edited by Lazy Vulpes: 04 July 2012 - 12:54 PM

[e_i_pi]

+1 Atli. I'd suggest putting this line in after you instantiate the PDO object as well:

$dblink->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);

You're probably thinking "Hey, this is PostgreSQL, not Oracle!". Don't worry about the constant name, it works with every DB driver. What this line does is ensures that a value of NULL in the DB comes back as null in PHP. When I started using PostgreSQL PDOs, I had troubles with nulls auto-converting to empty strings, took me ages to figure out what the issue was.

FYI, here's the manual page on the various setAttribute settings.

This post has been edited by e_i_pi: 04 July 2012 - 02:04 PM