connecting to an Access data base

how do u do it?

[flying monkey]

i want to connect to a Access data base but dont know how to do it. also how would u add more rows if needed and how would u write to the cells in the tables.

[Distel]

The search terms "access database php" and "microsoft access database php" in Google seem to get some results.

[flying monkey]

really didnt find anything......still need help

[bcolflesh]

Microsoft Access Database ODBC and PHP Tutorial
w3.phpfreaks.com/tutorials/61/0.php

[flying monkey]

i looked at that but did not make sence..........still dont know how to access the feilds

[jatar_k]

You can also try
Unified ODBC functions [ca.php.net]
there is a link in the user comments that looks pretty good as well

<added>coopster on the same wavelength, same time ;)

[edited by: jatar_k at 2:11 am (utc) on Nov. 25, 2003]

[coopster]

The tutorial is explaining that you need to use the PHP ODBC [us3.php.net] (Open Database Connectivity) functions. Are you saying you don't know how to create the SQL (Structured Query Lanuage)?

[flying monkey]

that where i have trouble..........i got a data base but get an error saying

"Previous Entries in databaseError in odbc_exec( no cursor returned )"

[coopster]

OK, sounds like you need some SQL help. If you are having trouble creating the query statement, post it here and we can help. If you used any of the tutorial examples, it probably looks something like this:

$cur= odbc_exec( $cnx, "select ... from ... "); 

I prefer to create the query statement, then plug it into the function. That way I can print my statement if I ever have issues like you are having. I recommend the practice:

$sql = "SELECT fields FROM table WHERE string='$string'"; 
// exit($sql); // uncomment this line to preview your statement. 
$cur = odbc_exec($cnx, $sql); 

>>same wavelength, same time...
jatar_k, How does the old Proverb go? Great Minds...
hehe
;)

[flying monkey]

what is the "where string='$string'" supposed to do

[coopster]

A WHERE clause will allow you to select specific rows for processing. For example:

$dogname = 'BowWow'; 
$sql = "SELECT dogname, dogage FROM pets where dogname='$dogname'"; 

Would return only the rows that have dogs named 'BowWow'.

[flying monkey]

awwwwwww cool thnaks

[flying monkey]

this is my code and it does not work:

<?php
$cnx= odbc_connect("test", " "," ");
$sql= "Select LastName, FirstName From test";
$cur= odbc_exec($cnx, $sql);
while(odbc_fetch_row($cur))
{
$LastName = odbc_result($result, 1);
$FirstName = odbc_result($result, 2);
print("$LastName $FirstName\n");
}

odbc_close($cnx)

?>

[flying monkey]

does access have to be installed on the comp running the server?

[flying monkey]

i am having trouble at this peice of code:

$cnx = odbc_connect( 'test2' , '', '' );
if (!$cnx) {
Error_handler( "Error in odbc_connect" , $cnx );
}
$SQL_Exec_String = "Insert Into People (fristname, lastname, phone)
Values ('$name','$lname','$pnum')";

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

i keep getting an error "Error2 in odbc_exec( no cursor returned )"

ive tried everthing i know.

[coopster]

OK, let's start with a simple SELECT statement and add some error checking to make sure you actually are getting connected and able to access data in tables. I'll use the example you had earlier, but we'll add some error checking that will stop the script and let us know exactly where your troubles start. Make sure you have a table named

test

created with two fields in it and enter some data into the table. The two fields will be character strings,

LastName

and

FirstName

, so you may want to name them accordingly for clarification.

<?php 
$cnx = odbc_connect('test', 'user','password') or exit("Could not connect to ODBC database!"); 
$sql = "SELECT * FROM test"; // The asterisk (*) says to select ALL fields 
$result = odbc_exec($cnx, $sql) or exit(odbc_errormsg()); 
while(odbc_fetch_row($result)) { 
$LastName = odbc_result($result, 1); 
$FirstName = odbc_result($result, 2); 
print("$LastName $FirstName\n"); 
} 
odbc_close($cnx) 
?> 

[flying monkey]

well i have the reading the data base working where i am reading data for a table url:

but cant get the writing to the data base working

[edited by: jatar_k at 11:43 pm (utc) on Nov. 25, 2003]
[edit reason] no personal urls thanks [/edit]

[coopster]

OK, then use the same sample script to see what, if any, error message is printing out when you try to write to the database. Let's attempt to write out some data, then read it back in. I've added some new lines to the script in bold:

<?php  
$cnx = odbc_connect('test', 'user','password') or exit("Could not connect to ODBC database!"); 
$sql = "INSERT INTO test (LastName, FirstName) VALUES ('TestLastName', 'TestFirstName')"; 
odbc_exec($cnx, $sql) or exit(odbc_errormsg()); 
$sql = "SELECT * FROM test"; // The asterisk (*) says to select ALL fields 
$result = odbc_exec($cnx, $sql) or exit(odbc_errormsg()); 
while(odbc_fetch_row($result)) { 
$LastName = odbc_result($result, 1); 
$FirstName = odbc_result($result, 2); 
print("$LastName $FirstName\n"); 
} 
odbc_close($cnx) 
?> 

[flying monkey]

this is the error i get: "Error2 in odbc_exec( no cursor returned )"

i will try the example u gave me.

[flying monkey]

the stuff i am trying to write to the data base in for a form they filled out on another page.......also the method is post.

[coopster]

The snippet here is simply to find out if we are able to perform some simple tasks. Mainly

1. connect to the database
2. read from tables in the database
3. write to tables in the database

Once we can confirm that these 3 tasks can be met successfully, we'll move on to processing the form data.

Make sense?

[flying monkey]

yes but i can connect to the data base, read from the data base, but cant write to it..........its driving me crazy

[coopster]

(PASS) >>yes but i can connect to the data base,
(PASS) >>read from the data base,
(FAIL) >>but cant write to it

OK, we're getting there. Regarding the last code snippet we we are using here (msg# 18) we should be getting an error message when we execute the INSERT INTO statement with the odbc_exec() function, correct? What errormsg is given?

>>its driving me crazy
Yeah, but the feeling you get when you overcome your issues is awesome -- hang in there ;)

[flying monkey]

with the code in message 18 i get the error:
"[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement."

im guessing that the error is in the insert statement.

[coopster]

I found this regarding your error:

This commonly occurs when you have a field name that is a reserved word.
Certain words (like table, field, date, note, select, password, level etc) are reserved by either ADO, OLEDB or by Access for use as commands or system objects.
You can get a list of reserved words in Access from the online help.
You should never use these words as names for tables or fields nor should you have spaces in your tablenames. It is recommended that you rename your offending tables/fields and adjust your SQL statement accordingly.

Hmmm. We aren't using any reserved words here...test, LastName, FirstName are not on the Access reserved word list...and we have single quotation marks around our values list. Well, we could try adding brackets around our fieldnames and/or we could also try changing the ODBC cursor type:

$cnx = odbc_connect('test', 'user','password', SQL_CUR_USE_ODBC) or exit("Could not connect to ODBC database!");  
$sql = "INSERT INTO test ([LastName], [FirstName]) VALUES ('TestLastName', 'TestFirstName')"; 

[flying monkey]

now i am getting a different error. now it states:
"[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query."

[coopster]

OK, I think you have a permissions issue then:

There are several things that may cause this ODBC error. Possible reasons:

• The permissions that are set on the directory the database is in. IUSR privileges must be set to "read/write".
  
• The permissions on the database itself does not have full read/write privileges in effect.
  
• This error can also occur when the database is located outside of the inetpub/wwwroot directory. Though the information is still able to be viewed and searched, it cannot be updated unless it is in the wwwroot directory.
  

Additional information
For additional information on this error, please refer to following article in Microsoft's Knowledge Base:

Q174640 - PRB: ASP: "The query is Not Updateable..." Error when Updating
Link: h**p://support.microsoft.com/default.aspx?scid=kb;EN-US;q175168

[flying monkey]

hey coopster thanks for all ur help.....i finally fixed the probalem....it was a permissions issue. but i do have another question? how would u delete an entry?

[coopster]

I'll tell you how to delete the entry, but you have to tell the forum how you fixed the problem, deal? ;)

$sql = "DELETE FROM test WHERE LastName = 'TestLastName' AND FirstName = 'TestFirstName'"; 

This statement says to delete from the table "test" ONLY those rows where the LastName field equals the string we specified AND the FirstName field equals the string specified. Both coniditions must be met before the DELETE operation is successful.

The reason I ask for you to explain how you fixed the problem is so that future readers of this thread in this forum will be able to overcome the same obstacles you did. Thanks flying_monkey :)

[flying monkey]

well i am running a windows 2003 server with IIS6. so i had to go the data base and right click and hit properties. there is a tab for security. make sure the that the internet user has the ability to read write and modify the file. this fixed my problem. hope this helps anyone.