[RESOLVED] Syntax error in query expression

gPHPc

I’m learning how to use an MS Access DB with PHP. The only online tutorial I found was at:

[url]http://www.phpbuilder.com/columns/siddarth20000228.php3[/url]

I got a few errors using the code supplied in the tutorial and was able to clean things up and now get the following error:

[B]Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '$FirstName'., SQL state 37000 in SQLExecDirect[/B]

I’ve searched and haven’t been able to find a solution. Any help would be greatly appreciated.

Here’s the problem section of code from DataAccess.php:

<?php


function Enter_New_Entry($FirstName,$LastName,$PhoneNumber)
	{

$cnx = odbc_connect( 'WebTutorial' , 'root', '' );
if (!$cnx)
	{
    Error_handler( "Error in odbc_connect" , $cnx );
	}

$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
        Values ($FirstName, $LastName, $PhoneNumber)";

$cur = odbc_exec( $cnx, $SQL_Exec_String );
if (!$cur)
	{
    Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
	}

odbc_close( $cnx);
}

?>

HalfaBee

Try echoing $SQL_Exec_String to see what it contains.

gPHPc

I get what I expected and what looks correct to my newbie eyes:

Insert Into People (FirstName, LastName, PhoneNumber) Values ($FirstName, $LastName, $PhoneNumber)

bpat1434

SQL columns should be encapsed in and and strings for insertion should be surrounded by ' and '.

gPHPc

Am I reading this right? Different style of single quotes?

bpat1434

well columns get back-ticks ( ` ) while data gets single quotes.

gPHPc

Wow! Thanks. I'm not able to test now, but I'm excited to try it.

What's the key combo for back-tick?

Thanks!

bpat1434

On a standard US English keyboard, it's the key just next to the "1" key. Without shift it's a back-tick, with shift it's a tilde "~".

gPHPc

Never mind, I found it. Doh!

gPHPc

Thanks.

HalfaBee

If this is what is being echoed, it is not expanding the variables.

Insert Into People (FirstName, LastName, PhoneNumber) Values ($FirstName, $LastName, $PhoneNumber)

gPHPc

I don't understand by "expanding the variables".

HalfaBee wrote:
If this is what is being echoed, it is not expanding the variables.

Insert Into People (FirstName, LastName, PhoneNumber) Values ($FirstName, $LastName, $PhoneNumber)

laserlight

SQL columns should be encapsed in and and strings for insertion should be surrounded by ' and '.

Are you sure that will work for MS Access? I believe the use of backticks to escape identifiers is MySQL specific. The use of double quotes and brackets seem more common (though the use of MySQL with PHP is so common that the use of backticks seems common instead).

EDIT:
Ah, it appears that double quotes are the identifier delimiters specified by SQL 92.

gPHPc

I think I've lost faith in the tutorial I cited.

Is there another online tutorial to learn how to use PHP to display and add data to an MS Access database?

Thanks for all the previous replies.

laserlight

I think I've lost faith in the tutorial I cited.

It may be redeemable, though I agree that a tutorial whose examples were not tested by the author cannot be trusted.

So, before you give up on this tutorial (I do not know of any such tutorials myself, admittedly), what is your current problem? If you are still having the same problem, change the SQL statement to:

$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
                    Values ('$FirstName', '$LastName', '$PhoneNumber')";

The identifiers are not SQL keywords so they do not need to be quoted, but if you want to quote them you will probably have better luck with double quotes than with backticks:

$SQL_Exec_String = "Insert Into People (\"FirstName\", \"LastName\", \"PhoneNumber\")
                    Values ('$FirstName', '$LastName', '$PhoneNumber')";

bpat1434

Variables being expanded meaning that the value of the variables should be there, not the variable names themselves. I.e.

$fname = 'Brett';
$lname = 'Someone';
$phone = '1234567890';

$SQL_Exec_String = "INSERT INTO People (\"FirstName\", \"LastName\", \"PhoneNumber\") VALUES ('$fname', '$lname', '$phone')";

echo $SQL_Exec_String;

That shoudl give the following output:

INSERT INTO People ("FirstName", "LastName", "PhoneNumber") VALUES ('Brett', 'Someone', '1234567890')

gPHPc

That's what I mean about the Tutorial code missing something. 😕

bpat1434 wrote:
Variables being expanded meaning that the value of the variables should be there, not the variable names themselves. I.e.
$fname = 'Brett';
$lname = 'Someone';
$phone = '1234567890';

$SQL_Exec_String = "INSERT INTO People (\"FirstName\", \"LastName\", \"PhoneNumber\") VALUES ('$fname', '$lname', '$phone')";

echo $SQL_Exec_String;
That shoudl give the following output:
INSERT INTO People ("FirstName", "LastName", "PhoneNumber") VALUES ('Brett', 'Someone', '1234567890')

gPHPc

Thanks, laserlight. My goal is to display and add data with a MS Access database

You've cleared up the tick, single quotes confusion for me. However, I'm having trouble with the Values and Undefined variables. I'm not sure where or how to declare the variables.

As it is, I'm adding the value names instead of the values to my database.

laserlight wrote:
It may be redeemable, though I agree that a tutorial whose examples were not tested by the author cannot be trusted.

So, before you give up on this tutorial (I do not know of any such tutorials myself, admittedly), what is your current problem? If you are still having the same problem, change the SQL statement to:
$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
                    Values ('$FirstName', '$LastName', '$PhoneNumber')";
The identifiers are not SQL keywords so they do not need to be quoted, but if you want to quote them you will probably have better luck with double quotes than with backticks:
$SQL_Exec_String = "Insert Into People (\"FirstName\", \"LastName\", \"PhoneNumber\")
                    Values ('$FirstName', '$LastName', '$PhoneNumber')";

gPHPc

I've spoken with my client and we've decided to move to MySQL. I think things will be safer and more in line with industry standards with MySQL.

Thanks again for the help from everyone.

laserlight

I think things will be safer and more in line with industry standards with MySQL.

Yes, though now you need to keep in mind that by default MySQL uses the non-standard backticks as identifier delimiters, heh 🙂