John Davidson

Message:

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.

---

My Answer:
To use PSQL OR in PHP with a variable, you can use prepared statements to prevent SQL injection and dynamically build your query based on the user input. Here's an example of how you can achieve this:

php
// Assuming $query contains the user input

// Establish a connection to the database
$dsn = "odbc:Driver={Pervasive ODBC Client Interface};ServerName=localhost;dbq=your_database;";
$username = "your_username";
$password = "your_password";

try {
$pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}

// Prepare the SQL query
$sql = "SELECT * FROM your_table WHERE workcenter = :query OR EmployeeName LIKE :query";
$stmt = $pdo->prepare($sql);

// Bind the parameter
$stmt->bindParam(':query', $query, PDO::PARAM_STR);

// Execute the query
$stmt->execute();

// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Output the results
foreach ($results as $row) {
// Do something with the data
}

// Close the connection
$pdo = null;


In this example, we use a prepared statement to safely insert the user input into the query. The `:query` placeholder is bound to the `$query` variable using the `bindParam` method. This helps prevent SQL injection attacks.

Make sure to replace `your_database`, `your_table`, `your_username`, and `your_password` with your actual database details. Also, adjust the connection string (`$dsn`) to match your Pervasive database configuration.

By using prepared statements and binding parameters, you can safely execute queries with user input in PSQL (Pervasive) using PHP.