Trouble in fetching results from PostgreSQL table using PDO

Question

I have database named testDB and one table test_tbl in it with three fields ID, User, Pass in PostgreSQL database on Mac OS X.

I am using following php code to fetch results from the table.

<?php
try {
    $dbuser = 'postgres';
    $dbpass = 'test123';
    $dbhost = 'localhost';
    $dbname='testDB';

    $connec = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
} catch (PDOException $e) {
    echo "Error : " . $e->getMessage() . "<br/>";
    die();
}

$sql = 'SELECT ID, User, Pass FROM test_tbl ORDER BY ID';

foreach ($connec->query($sql) as $row) {
    print $row['ID'] . " ";
    print $row['User'] . "-->";
    print $row['Pass'] . "<br>";
}

It's raising following error:

Warning: Invalid argument supplied for foreach() in /Users/nikko/Sites/pg_conn2.php on line 12

Can you add PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION for your connection options ? — mim.
– mim., Commented May 3, 2018 at 15:19
You mean this way: $connec->setAttribute(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); — Nikko
– Nikko, Commented May 3, 2018 at 15:42
Fatal error: Uncaught PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column "id" does not exist LINE 1: SELECT ID, User, Pass FROM test_tbl ^ in /Users/Nikko/Sites/pg_conn2.php:12 Stack trace: #0 /Users/Nikko/Sites/pg_conn2.php(12): PDO->query('SELECT ID, User...') #1 {main} thrown in /Users/Nikko/Sites/pg_conn2.php on line 12 — Nikko
– Nikko, Commented May 3, 2018 at 15:54
Then your query is the problem, check your table columns and query. — mim.
– mim., Commented May 3, 2018 at 15:57

Nikko · Accepted Answer · 2018-05-04 12:31:07Z

1

I was able to solve the problem. There is problem with PostgreSQL if you use caps in database table fields names then this exception is thrown. I changed the fields to small caps and it starts working. – Nikko just now edit

answered May 4, 2018 at 12:31

Nikko

571 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Faure · Accepted Answer · 2018-05-04 14:26:38Z

1

Postgres is not case sensitive regarding fieldnames

SELECT anyfield, id FROM mytable;

SELECT AnyField, ID FROM mytable;

Both will work.

Maybe the problem is that PDO is quoting field names like in

SELECT "AnyField", "ID" FROM mytable;

You can read Postgres log on your server to see the actual statement.

Besides, you should catch $connec->query($sql) before using it as an array. Perhaps PDO is returning false as a result when querying a bad formed statement.

edited May 4, 2018 at 14:26

answered May 4, 2018 at 12:50

Daniel Faure

4007 silver badges16 bronze badges

3 Comments

Nikko Over a year ago

OK, Here is the link from where I get info to solve the problem. It says the problem is caused by the way pg handles CAPS.drupal.org/project/currency/issues/2007388

Daniel Faure Over a year ago

Many versions ago, ID was a reserved word in Postgres. So, if you wanted to have a column named ID, you should define and use it quoted like this: "ID". If your table was created with quoted column names, you'll have to quote always to use them in statements. Postgres manual

Nikko Over a year ago

OK, thank you. I encountered same problem with 'user'. I had a field as 'user' but when fetching values, it was always postgres as value for each row. Then I renamed it to 'login' and table values were fetched. So I need to use same technique there too as you described for id.

Collectives™ on Stack Overflow

Trouble in fetching results from PostgreSQL table using PDO

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related