Postgres 'where' query is not working with PHP

Question

I'm running some PHP code where I am trying to find rows with a specified parameter. I have successfully connected with the database, however I'm running into one problem.

When I run this code:

    $SQL = "SELECT * FROM schema.table";
    $result = pg_exec($dbconn, $SQL);
    while ($row = pg_fetch_array($result) {
          echo "data: " .$row . "<br/>\n";

I get a printed list of arrays showing that I was successful in taking this data from the database and being able to print it out into the website page.

However, I need to filter the data where I only want the rows with a specific ID. When I try this query:

    $SQL = "SELECT * FROM schema.table WHERE 'ID' = '123'";

pg_numrows($result) is 0 when there are four rows with ID of 123 and there is nothing to show and no array and I have no idea why adding the 'where' statement changes anything. What happened to the data, why does filtering it cause it to no longer work?

SOLVED: Figured out the answer for anyone running into the same problem.

    $SQL = "SELECT * FROM schema.table WHERE \"ID\" = '123'";

This is the syntax that must be used for character varying columns. Thanks for everyone making me realize I was running a funny code with wrong quotation marks.

Why is your ID surrounded by '', shouldn't it be 123 and not '123'? — Sefam
– Sefam, Commented Jun 23, 2014 at 17:10
If I don't use the single quotes on ID I get this error: Query failed: ERROR: column "id" does not exist. And when I take off single quotes from 123 I get: Query failed: ERROR: invalid input syntax for integer — user3756998
– user3756998, Commented Jun 23, 2014 at 17:51
You could simply write: $SQL = "SELECT * FROM schema.table WHERE ID = '123'"; There's no need for quoting the column name. as @user3763227 wrote in his last comment. — VMai
– VMai, Commented Jun 23, 2014 at 18:23

user3763227 · Accepted Answer · 2014-06-23 17:36:08Z

2

use like this..

$SQL = "SELECT * FROM schema.table WHERE ID = 123";

Never use single quotes for column names and never use single quotes for int type..

edited Jun 23, 2014 at 17:36

answered Jun 23, 2014 at 17:15

user3763227

2703 silver badges15 bronze badges

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

4 Comments

Mike Brant Over a year ago

You should explain what is wrong with the original syntax so the OP understands what the problem is.

user3756998 Over a year ago

If I don't use the single quotes on ID I get this error: Query failed: ERROR: column "id" does not exist. And when I take off single quotes from 123 I get: Query failed: ERROR: invalid input syntax for integer

user3763227 Over a year ago

please provide your table structure ie, type of each column with its name

user3763227 Over a year ago

@user3756998 then try this..$SQL = "SELECT * FROM schema.table WHERE ID = '123'"; use single quotes for 123 only.. since you are using varchar for ID

Collectives™ on Stack Overflow

Postgres 'where' query is not working with PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related