php MS Access SQL Query with multiple string parameters

Question

I am trying to construct an SQL statement with two string parameters. Essentially I am querying a MS Access table with php.

Is my syntax correct below?

$parm1 = "TPMS";
$parm2 = "Clamp In";

$sql = "SELECT * FROM archive where productfamily like ".$parm1 ."and where productderivative like". $parm2;

Now I have tried a one parameter string called $parm1, The syntax of the string is as below. Please note this is a MS Access table I am querying with php.

$parm1 = "'TPMS'";

Now the corresponding MS Access SQL statement is as follows which works:

$sql  = "SELECT * FROM archive where productfamily like $parm1 order by fullname asc"

Now the corresponding MS Access SQL statement with two parameters which does not work. Can somebody tell me why the second parameter does not work in the SQL statement? Is it perhaps my syntax?

$sql  = "SELECT * FROM archive where productfamily like $parm1 and "
$sql .= "where productderivative like $parm2 order by fullname asc";

tinonetic · Accepted Answer · 2017-05-11 19:42:49Z

Firstly, you need to enclose your string literals with single quotes: '

$parm1 = "'TPMS'";
$parm2 = "'Clamp In'"; 

$sql = "SELECT * FROM archive where productfamily like ".$parm1 ."and where productderivative like". $parm2;

Secondly, a LIKE statement is useful with a wildcard character

% The percent sign represents zero, one, or multiple characters
? The question mark(for Access) represents a single character

So that if you are looking for occurences that may include TPMS anywhere after the , you would have

For example:

$parm1 = "'TPMS%'";
$parm1 = "'%TPMS'";
$parm1 = "'%TPMS%'";
$parm1 = "'?T%'";
$parm1 = "'T?%?%'";
$parm1 = "'T%o'";

Which evaluate to the following SQL:

    WHERE productfamily LIKE 'TPMS%'    --Finds any values that starts with "TPMS"
    WHERE productfamily LIKE '%TPMS'    --Finds any values that ends with "TPMS"
    WHERE productfamily LIKE '%TPMS%'   --Finds any values that have "TPMS" in any position
    WHERE productfamily LIKE '?T%'  --Finds any values that have "T" in the second position
    WHERE productfamily LIKE 'T?%?%'    --Finds any values that starts with "T" and are at least 3 characters in length
    WHERE productfamily LIKE 'T%o'  --Finds any values that starts with "T" and ends with "o"

pendo · Accepted Answer · 2017-05-11 19:11:32Z

0

I'll adapt the code from the documentation here https://www.sitepoint.com/using-an-access-database-with-php/ to your situation.

$dbName = $_SERVER["DOCUMENT_ROOT"] . "yourpathhere\archive.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$pdo = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");

$parm1 = 'TPMS';
$parm2 = 'Clamp In';
$sql = 'SELECT * FROM archive where productfamily like :family and where productderivative like :derivative';

$pdo->prepare($sql)
$pdo->bindParam(':family', $parm1, PDO::PARAM_STR);
$pdo->bindParam(':derivative', $parm2, PDO::PARAM_STR);
$pdo->execute();

That should get you there. This is untested. If anything fails, let me know and I'll create some tables and run it.

edited May 11, 2017 at 19:11

answered May 11, 2017 at 15:46

pendo

8522 gold badges8 silver badges28 bronze badges

1 Comment

David Egan Over a year ago

Hi Pendo It's a MS Access Database not MYSQL I am using

Collectives™ on Stack Overflow

php MS Access SQL Query with multiple string parameters

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related