delucis delucis - 2 months ago 17
PHP Question

Cannot query with PHP PDO a saved query in MS Access Database

I have a Microsoft Access database with a bunch of saved queries. I would like to use PHP to run and grab the results of these queries. I can query tables in the same database just fine. My understanding is that I can treat queries as if they were tables.

This will return data from the table "New Grower Fields" just fine:

$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$connString; SystemDB=$mdw; Uid=$username; Pwd=$password");
$sth = $db->prepare("SELECT * FROM [New Grower Fields]");
$sth->execute();
$results = $sth->fetchALL(PDO::FETCH_ASSOC);
print_r($results);


But if I want to use a saved query, which I believe should act just like querying a table, I don't get anything.

$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$connString; SystemDB=$mdw; Uid=$username; Pwd=$password");
$sth = $db->prepare("SELECT * FROM [Daily Tonnage by Plant]");
$sth->execute();
$results = $sth->fetchALL(PDO::FETCH_ASSOC);
print_r($results);


Is there any way to allow me to get the results of a saved query in MS Access with PHP? I'm fairly new to this. I appreciate any and all help! I will be happy to provide any additional information needed.

Answer

This seemed to solve it for me:

$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$connString; SystemDB=$mdw; Uid=$username; Pwd=$password");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $db->prepare("SELECT [Grade Date], NetWt FROM [Daily Tonnage by Plant]");
$sth->execute();

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
  print_r($row);
}

I added $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); and changed

$results = $sth->fetchALL(PDO::FETCH_ASSOC); to $row = $sth->fetch(PDO::FETCH_ASSOC)

Comments