postgresql select query with variables

Question

I am trying to make a postgresql select query work with php, but can't get past the syntax.

I have a variable and a query that returns the surname of any match.

$name = 'Smith';
$query = 'SELECT surname FROM emploee WHERE name= '.$name.';';
$a = pg_query(connect(),$query );
while($row = pg_fetch_array($a)){ echo "Match";    }

For those who wonder why do I have to declare Smith as a variable, $name is not always equals to Smith. The value of $name is taken correctly from another query. However, this is not working. I read in an article that I should use pg_query_params() but couldn't get that to work neither.

Your help would be highly appreciated

Are you sure you have database connection? And for pg_queruy_params() you need a SQL-placeholder like $1 in your SQL-statement. — Frank Heikens
– Frank Heikens, Commented Feb 13, 2015 at 9:04
I am sure I do. I have established it earlier but didn't include it in the post. Also, the value I get for $name is taken in another query that uses that connection, so it works. As for pg_query_params() I read the php manual and saw the example, but wasn't able to adapt it to my case. The problem is on including a variable in the query (quote issues). I just dont know how to make it right. — Lazarus Rising
– Lazarus Rising, Commented Feb 13, 2015 at 9:08
@parveen: Although it is a good point, the inner design of the actual code makes it impossible for more than 1 row to return. And anyway, the number of Match strings is a way to check. But you obviously couldn't notice this in the piece of code that I posted. — Lazarus Rising
– Lazarus Rising, Commented Feb 13, 2015 at 13:35

Bang · Accepted Answer · 2015-02-13 09:57:32Z

1

Try this :

$query = "SELECT surname FROM emploee WHERE name= '" . $name . "';";

And the best way without binding :

$query = sprintf("SELECT surname FROM emploee WHERE name = '%s'", pg_escape_string($name));

And if you want to use binding :

$result = pg_query_params(connect(), 'SELECT surname FROM emploee WHERE name = $1', array($name));

As you get a result from other query ' Smith', there is a white space.

To remove white space from $name, you can do : $name = trim($name);

edited Feb 13, 2015 at 9:57

answered Feb 13, 2015 at 9:07

Bang

9394 silver badges11 bronze badges

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

13 Comments

Lazarus Rising Over a year ago

This is the error I get with the first try: Warning: pg_query(): Query failed: ERROR: column " Smith" does not exist LINE 1: SELECT surname FROM emploee WHERE name= " John"; ^ in //file_path With the second statement I get this error : pg_fetch_array() expects parameter 1 to be resource, null given in

Bang Over a year ago

I have change " to ', your query should look like SELECT surname FROM emploee WHERE name= 'John';

Frank Heikens Over a year ago

Don't use pg_query without any protection against SQL injection! If you really don't want to use pg_query_params(), you must use pg_escape_string() for your input.

Lazarus Rising Over a year ago

@Bang: Thanks a lot! Both alternatives work, in the way I posed the question. I would love to accept your question. However, in my original code (as I mentioned earlier in the post) I dont assign the values of $name manually. I get them from another query. So unless I assign the value as $name = 'Smith'; it wont work. My bad that i simplified the question in such a manner.

Frank Heikens Over a year ago

If you don't work secure now, your code will be insecure forever.... You would be the first who would make code secure afterwards. Security is not an add on, it's a way of coding. And you need just 3 changes to make it safe: use pg_query_params(), use placeholders in your SQL and create an array to use in pg_query_params(). It only takes a minute!

|

Lazarus Rising · Accepted Answer · 2015-02-13 10:43:14Z

These are the two methods that worked, suggested by Bang and Frank Heikens respectively. Since they only commented, I am posting it as an answer for those who might come up the same situation. However, I would strongly advise them to read the comments too. I learned a couple of stuff on the way, you might as well.

Bang's suggestions->

$a = trim($name); 
$query = "SELECT surname FROM employee WHERE name= '" . $a . "';"; 
$result = pg_query(connect(), $query); 
while($row = pg_fetch_array($result)){ echo "Match"; }

Frank Heikens suggestions ->

$n = trim($name);
$s = trim($surname);
$params = array ($n, $s);
$result = pg_query_params(connect(), 'SELECT office FROM emploee WHERE name = $1 and surname = $2', $params);
while($row = pg_fetch_array($result)){ $k = $row['path']." ".$row['office'];  echo $k; }

In both cases I have to use trim (not sure if this will be your case too). I added a third field office to demonstrate how can I take several arguments. If anyone has other critics, suggestions or solutions, be my guest. I will try everyone of them and let you know.

mahmoud alaa · Accepted Answer · 2018-01-08 12:15:01Z

0

With this code above: you can print "match" for each record matched with query

$a = trim($name); 
$query = "SELECT surname FROM employee WHERE name= '" . $a . "';"; 
$result = pg_query(connect(), $query); 
while($row = pg_fetch_array($result)){ echo "Match"; }

But certainly you need to print the values returned: just make it echo $row['columnName']

Full details here:https://w3resource.com/PostgreSQL/select.php

answered Jan 8, 2018 at 12:15

mahmoud alaa

1111 silver badge7 bronze badges

Collectives™ on Stack Overflow

postgresql select query with variables

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related