Heroku PHP: Connecting to ClearDB via Herrera PDO

Question

I've read several questions about using ClearDB via Heroku, but none of them provided enough guidance to solve my issue.

So, my issue...

Currently I've got a PHP application deployed that is basically empty except the code which I'll place below. Basically, I'm trying to use the Herrera\PDOServiceProvider() to connect to ClearDB which I've read is possible. Also, it's basically the same as the Heroku PHP tutorial just using ClearDB vice the Postgre.

I'm getting an error somewhere because I'm getting a blank page. The tutorial shows that I should see a single word "hello" which is now gone.

<?php

require('../vendor/autoload.php');

use Herrera\Pdo\PdoServiceProvider;
use Silex\Application;

$app = new Application();
$app['debug'] = true;

// Register the monolog logging service
$app->register(new Silex\Provider\MonologServiceProvider(), array(
  'monolog.logfile' => 'php://stderr',
));

// Our web handlers

$app->get('/', function() use($app) {
  $app['monolog']->addDebug('logging output.');
  return 'Hello';
});

$dbopts = parse_url(getenv('DATABASE_URL'));
//print_r(array_values($dbopts));
$app->register(new PdoServiceProvider(),
    array(
        'pdo.dsn' => 'mysql:dbname='.ltrim($dbopts["path"],'/').';host='.$dbopts["host"],
        'pdo.port' => $dbopts["port"],
        'pdo.username' => $dbopts["user"],
        'pdo.password' => $dbopts["pass"]
    )
);

$host = $app['pdo.dsn.host'];
$dbname = $app['pdo.dsn.mysql:dbname'];
$user = $app['pdo.username'];
$pass = $app['pdo.password'];*/

$pdo = new PDO("mysql:host='.$host.'; dbname='.$dbname.';", $user, $pass);
$statement = $pdo->query("SELECT title FROM news");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['title']);

app->run();

I'm not sure exactly what's wrong, but I know it has something to do with the db connection because when I comment it out the word "Hello" shows back up on my remote site.

Any help is appreciated. Thank you.

Answer 1

Problem

You have used single quotes in your connections strings:

if you write the following:

"mysql:host='.$host.'; dbname='.$dbname.';"

PHP will parse as:

mysql:host='.localhost.'; dbname='.test.';

instead of

mysql:host=localhost; dbname=test;

Solution:

$pdo = new PDO("mysql:host=$host; dbname=$dbname;", $user, $pass);

or

$pdo = new PDO("mysql:host=".$host."; dbname=".$dbname.";", $user, $pass);

matter of facts, you don't even need the last semi-colon:

$pdo = new PDO("mysql:host=".$host."; dbname=".$dbname, $user, $pass);

---

• it is a good idea to turn on error mode and to catch exceptions
• You need a loop if you have more then one rows

Code:

try {
    $pdo = new PDO("mysql:host=".$host."; dbname=".$dbname, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    foreach($pdo->query('SELECT title FROM news') as $row) {
        echo htmlentities($row['title']);
    }
    $pdo = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}