CODEWITHSUNDEEP
phppostgresqlherokuconnectionremote-access
bluemccarty
bluemccarty

Reputation: 3

How to remotely connect to HEROKU PostgreSQL database using

I have a PHP application that needs to remotely connect to a HEROKU app's postgresql database and run database queries (preferably using pg_query, though PDO is ok too). I am stuck trying to 1.) obtain live db credentials using the Heroku DATABASE_URL (as they recommend), and 2) generate any kind of db connection even when hard-coded credentials are supplied. I haven't found the proper sequence of steps to make a connection. (App is running on GoDaddy hosting - mentioned for clarity.)

I am trying to connect to the database, run a select query and assign the values from the resultset to PHP variables. I can connect to the database using PGAdmin3 so remote access isn't the issue.

What I have so far (Edited- commented lines are from original post):

//$DATABASE_URL='$({heroku CLI string, pasted from Heroku db credentials page})';
//$db = parse_url(getenv($DATABASE_URL));

$db_url = getenv("DATABASE_URL") ?: "postgres://user:pass@host:port/dbname";
$db = pg_connect($db_url);

if($db) {echo "connected";} else {echo "not connected";}

$selectSql = "SELECT id, name FROM companies ORDER BY id";   
$result =  pg_query($db, $selectSql);

while ($row = pg_fetch_row($result)) {
    $id = $row["id"];
    $name = $row["name"];
    echo "<br>id: ".$id;
    echo "<br>name: ".$name;
}

What is missing to connect to the database and run queries?

Upvotes: 0

Views: 2374

Answers (2)

davejagoda
davejagoda

Reputation: 2528

I see at least 3 problems:

1) Delete or comment out this:

$DATABASE_URL='$({heroku CLI string, pasted from Heroku db credentials page})';

2) Change this line to get the environment variable (so it looks like this). You have to set the environment variable before starting PHP. You could also hardcode the value, but not recommended:

$db = parse_url(getenv('DATABASE_URL'));

3) It doesn't look like you are actually connecting to the database, you just start your query. You have to connect first. See heroku docs for PDO or PHP docs for pg_query

Update 2018-03-23

Here is a standalone program that will make sure it is connecting:

#!/usr/bin/env php
<?php
$db_url = getenv("DATABASE_URL") ?: "postgres://user:pass@host:port/dbname";
echo "$db_url\n";

$db = pg_connect($db_url);
if($db) {echo "connected";} else {echo "not connected";}

$selectSql = "SELECT 1";
$result =  pg_query($db, $selectSql);

while ($row = pg_fetch_row($result)) {
    var_dump($row);
}
?>

Upvotes: 3

Kevin Raoofi
Kevin Raoofi

Reputation: 1033

You need to call pg_connect with the correct connection string. I don't think it's the same as the URL given via Heroku; refer to the documentation for that one.

You're also using getenv improperly.

$db_url = getenv("DATABASE_URL") ?: "some default value here";
$db = pg_connect($db_url);

Upvotes: 1

Related Questions