ODBC WHERE clause for MS Access database

[edpatterson]

I can not seem to be able to either find the correct search criteria to locate the answer here or a way to get a query to return selected rows from an access database.

This works
$sql = 'SELECT last_name, first_name, middle_initial FROM names';
$result = odbc_exec($dbconn, $sql);

(loop to display the names)

This does not work
$sql = 'SELECT last_name, first_name, middle_initial FROM names WHERE last_name = "Smith"';
$result = odbc_exec($dbconn, $sql);

(loop to display the names)

I have tried numerous variations on quoting Smith, and yes, there are last names in the table equal Smith

Any and all help will be appreciated.

 

[jpadie]

try writing the query in MS Access QBE, switch to sql view and copy and paste the sql.

 

[edpatterson]

Tried that, it failed. While I was waiting for a reply here I found a fix in the ODBC area.

$sql = "SELECT last_name, first_name, middle_initial, author_id FROM [author] WHERE [last_name]='Smith'";

or

$sql = "SELECT last_name, first_name, middle_initial, author_id FROM `author` WHERE `last_name`='Smith'";

I had not considered using back tics or brackets to delimit the statement.

Ed

 

[jpadie]

that's why i suggested using the access QBE. it automatically adds the square brackets where needed

 

[edpatterson]

Perhaps I did not follow your suggestion. I have not seen QBE since the very early days of FoxPro. I built the query in the GUI and then used the View SQL. I then copied and pasted the contents into my web page. It failed, it added parens ()'s not brackets []'s.

Now I can't get the WHERE to work with IP Addresses. I'll continue trying various escape and encapsulation methods.

Thanks for your help!

Ed

 

[a6m1n0]

It shouldn't be this complicated. I use all sorts of clauses with PHP 5 and MS Access via ODBC on Windows and have never has these problems.

$sql = 'SELECT field1,field2 FROM tablename WHERE field1=\'foo\''
(string in single quotes escaped by slashes)

$sql = 'SELECT field1,field2 FROM tablename WHERE field2=100'
(integer not enclosed in single quotes)

Check and make certain of the field *data type*. Do not enclose integers or dates, but enclose everything else in single quotes.


-a6m1n0

Curiosity only kills cats.

 

[jpadie]

it's not about the escaping so much. access needs ambiguous field and table names to be disambiguated.

 

[a6m1n0]

Hm...that makes no sense to me, but I am a programmer not a DBA. I just know I have been using PHP5+ODBC with MSSQL/Access/iSeries5/MySQL all using the same four functions I wrote in PHP (select/insert/update/delete) and have zero problems.

Regardless, here are some links to information avaiable:

General Examples:

http://msdn2.microsoft.com/en-us/library/bb208930.aspx

The WHERE Clause:

http://msdn2.microsoft.com/en-us/library/bb221200.aspx

Here is a highly rated tutorial about this subject:

http://www.phpfreaks.com/tutorials/61/0.php

BTW: The site you are running PHP on, what version of PHP is it? Is it on Windows with IIS? Are you accessing PHP via ISAPI or CGI? Are you using a DSN connection? What is your connection string for ODBC? (Don't post your real user/pass of course if you use one).

-a6m1n0

Curiosity only kills cats.

 

[edpatterson]

Thanks for the assistance and especially the links.

As it turns out I has an oh where a zero belonged. Perhaps it was for the best because I have learned a great deal.

For what it is worth I am running Apache 2, PHP 5 and MySQL 5.0.33. I tried to get PHP running on IIS for a few hours then went back to Apache.

The 'application' is a kludge of things. User and host information from an Access database, logging is performed to a MySQL database.

Ed