Search code examples
phppervasive-sql

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


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.


Solution

  • 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.