pgsql via PDO

[Sluke]

Hi

 

I've been trying to connect and query pgsql through PDO but can't seem to get very far.

 

I have a table consisting of the following:

id, title, owner, post, editdate, createdate, deleted

 

When I try to run a simple query I don't get any output whatsoever

 

<?php

try
{

$db = new PDO("pgsql:host=localhost;dbname=slukedb",blank,blank);

$sql = "SELECT title, owner, post, createdate FROM news_posts";
Foreach ($db->query($sql) as $row)
{
echo $row['title'] . ' - ' . $row['owner'] . ' - ' . $row['post'] . ' - ' . $row['createdate'] . '<br />';
}

$db = null;
} catch (PDOException $e)
{
echo $e->getMessage();
}
?>

 

This is given as an example of querying using PDO by almost all sites I've been to.

 

If I try the following I can get a bit further:

<?php
try {
$db = new PDO("pgsql:host=localhost;dbname=slukedb",blank,blank);

$sql = "SELECT title, owner, post, createdate FROM news_posts";

$stmt = $db->query($sql);

$result = $stmt->fetch(PDO::FETCH_ASSOC);

foreach ($result as $row) {
echo echo $row['title'] . ' - ' . $row['owner'] . ' - ' . $row['post'] . ' - ' . $row['createdate'] . '<br />';
}

$db = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
?>

 

But it'll only display the first row in the table.

 

Thank you for your help

[btherl]

The examples I see here are different:

 

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

 

This is using a prepared transaction, but fetching the result data should be the same.  $stmt->fetch() must be called to fetch each row individually.