How to use PSQL (pervasive) OR in PHP with a variable?

Question

This is part of my query:

where workcenter = '$query' or EmployeeName like '$query'

workcenter is a number and EmployeeName is a string. Users enter either a number or a name and this searches the table for either the workcenter # or the employee.

In my PSQL editor this query works when hardcoded:

where workcenter = 'Adrian' or EmployeeName like '%Adrian%'

How am I supposed to write the EmployeeName like '$query' so it returns results?

I've tried:
%'$query'%
'%$query%'
'%{$query}%'

EDIT: after following @mirtheli suggestion I tried

$base = 'select Employee,EmployeeName,WorkcenterName from zzHENEmplWkcntr ';
$query = $_GET['wc'];comes from input text (example: 1234 or adrian)
$psql = $base . "where workcenter = '$query' or EmployeeName like \'%' .$query . '%\'";
echo "Query: ".$psql;
Output: select Employee,EmployeeName,WorkcenterName from zzHENEmplWkcntr where workcenter = 'adrian' or EmployeeName like \'%' . adrian . '%\'

Error I get: Fatal error: Uncaught Error: Call to a member function execute() on bool in C:\inetpub\wwwroot\viewWorkcenter.php:21 Stack trace: #0 {main} thrown in C:\inetpub\wwwroot\viewWorkcenter.php on line 21

LINE 20 & 21:

$qry = $conn->prepare($psql);
$qry->execute();

My understanding is that this error means the query returned no results.

Post relevant snippet of your code otherwise it's hard to say what you are really doing there. — Marcin Orlowski, Apr 29 '21 at 11:02
I posted the query where I am getting the error. what more do you need to see? — ProfoundHypnotic, Apr 29 '21 at 13:37
Please share code that is readable and that does not contain syntax error — Nico Haase, Apr 29 '21 at 15:27

mirtheil · Accepted Answer · 2021-04-29T15:18:56.073

You'd need to concatenate the wildcard characters (%) with the $query value. You also need to escape the single quotes. I'm using the PSQL DEMODATA so my field names are different than yours but it should get the point across. You'll use something like:

$basesql = 'select * from class ';
$OrClause = 'GER';
$sql = $basesql . ' where id > 10  or name like \'%' . $OrClause . '%\'';
echo ($sql . '<br>');

which displays:

select * from class where id > 10 or name like '%GER%'

EDIT: The code posted in the question after I posted my answer is done a little different. The problem is that when double quotes and single quotes are mixed, it can cause confusion.
Fixed code is:

$psql = $base . "where workcenter = '$query' or EmployeeName like '%" .$query . "%'";

and generates:

Query: select Employee,EmployeeName,WorkcenterName from zzHENEmplWkcntr where workcenter = 'fff' or EmployeeName like '%VALUE%'

Since the string is using the double quotes, the single quotes around the value do not need to be escaped.

I updated my question with my code reflecting your example. I'm still having the same error. When I print out the sql statement it looks like the quotes are not being escaped. — ProfoundHypnotic, Apr 29 '21 at 14:32
Thank you I was able to get this working with your help. I appreciate you taking the time to deal with my issues. YOu deserver a medal or a cuddle — ProfoundHypnotic, Apr 29 '21 at 15:43

How to use PSQL (pervasive) OR in PHP with a variable?

1 Answers1