Making a dsn-less connection to a Microsoft Access Database
I need the details (with examples) of how to connect to a Microsoft Access database from php with a dsn-less connection.
Also sample of how to execute SQL ("Select * from table_name") and how to handle the result set.
In other words, the equivalent of:
$Host = "sqlc0e.megasqlservers.com
$User = "some_user";
$Password = "some_pwd";
$DBName = "some_database";
$TableName = "schedule";
$Link = mysql_connect ($Host, $User, $Password);
$Query = "SELECT * from $TableName where date = '" . $thedate . "' order by AMPM";
$result = mysql_db_query ($DBName, $Query, $Link);
$ns = mysql_num_rows ($result);
for($i = 0; $i , $ns $i++) {
$row = mysql_fetch_row ($result);
// do something with data
}
Thanks,
rkorts
Also sample of how to execute SQL ("Select * from table_name") and how to handle the result set.
In other words, the equivalent of:
$Host = "sqlc0e.megasqlservers.com
$User = "some_user";
$Password = "some_pwd";
$DBName = "some_database";
$TableName = "schedule";
$Link = mysql_connect ($Host, $User, $Password);
$Query = "SELECT * from $TableName where date = '" . $thedate . "' order by AMPM";
$result = mysql_db_query ($DBName, $Query, $Link);
$ns = mysql_num_rows ($result);
for($i = 0; $i , $ns $i++) {
$row = mysql_fetch_row ($result);
// do something with data
}
Thanks,
rkorts
$db=realpath("YOURDB.mdb")
$conn=new COM('ADODB.Connection');
$conn->Open("Provider=Micr
$sql="SELECT * from YOUTTBL";
$rs=$conn->Execute($sql);
while (!$rs->EOF){
echo $rs->Fields["FIELD"]->Valu
$rs->MoveNext();
}
$rs->Close();
$conn->Close();
$conn=new COM('ADODB.Connection');
$conn->Open("Provider=Micr
$sql="SELECT * from YOUTTBL";
$rs=$conn->Execute($sql);
while (!$rs->EOF){
echo $rs->Fields["FIELD"]->Valu
$rs->MoveNext();
}
$rs->Close();
$conn->Close();
I've seen the ADODB answer a LOT of the times when people want to use MS Access and PHP.
When using ODBC, you need to load the MS Access ODBC driver (The DSN shown by AngelIII is entirely suitable - more reading at http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAccess).
Is there any performance differential between using ODBC and COM for MS Access?
When using ODBC, you need to load the MS Access ODBC driver (The DSN shown by AngelIII is entirely suitable - more reading at http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAccess).
Is there any performance differential between using ODBC and COM for MS Access?
ASKER
To hiteshgupta1,
I tried your approach. It does not work. The following is an EXACT COPY of the php file. It produces ONLY "FullName field from Access" in the browser output.
Note, I put the print's in to try to see what was going wrong. They don't even produce anything.
What's wrong?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access from php</title>
</head>
<body>
FullName field from Access
<?php
$db=realpath("lss.mdb");
$conn=new COM('ADODB.Connection');
$conn->Open("Provider=Micr
print("Returned conn = $conn<br>\n");
$sql="SELECT * from customer";
$rs=$conn->Execute($sql);
print("Returned rs = $rs<br>\n");
while (!$rs->EOF){
echo $rs->Fields["FullName"]->V
$rs->MoveNext();
}
$rs->Close();
$conn->Close();
?>
</body>
</html>
I tried your approach. It does not work. The following is an EXACT COPY of the php file. It produces ONLY "FullName field from Access" in the browser output.
Note, I put the print's in to try to see what was going wrong. They don't even produce anything.
What's wrong?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access from php</title>
</head>
<body>
FullName field from Access
<?php
$db=realpath("lss.mdb");
$conn=new COM('ADODB.Connection');
$conn->Open("Provider=Micr
print("Returned conn = $conn<br>\n");
$sql="SELECT * from customer";
$rs=$conn->Execute($sql);
print("Returned rs = $rs<br>\n");
while (!$rs->EOF){
echo $rs->Fields["FullName"]->V
$rs->MoveNext();
}
$rs->Close();
$conn->Close();
?>
</body>
</html>
ASKER
To angelIII
I tried your approach. It does not work. The following is an EXACT COPY of the php file. It produces ONLY "FullName field " in the browser output.
Note I put the print in to try to see what's going on & it fails too.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<?php
print("FullName field\n");
$conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
print("conn result: $conn\n");
$sql="SELECT * FROM customer";
$rs=odbc_exec($conn,$sql);
while (odbc_fetch_row($rs)) {
$name=odbc_result($rs,"Ful
print("name = $name\n");
}
?>
</body>
</html>
I tried your approach. It does not work. The following is an EXACT COPY of the php file. It produces ONLY "FullName field " in the browser output.
Note I put the print in to try to see what's going on & it fails too.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<?php
print("FullName field\n");
$conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
print("conn result: $conn\n");
$sql="SELECT * FROM customer";
$rs=odbc_exec($conn,$sql);
while (odbc_fetch_row($rs)) {
$name=odbc_result($rs,"Ful
print("name = $name\n");
}
?>
</body>
</html>
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>It produces ONLY "FullName field " in the browser output.
well, I assume the values for all records for that field?
in that case, it works perfectly, the only thing you have to do is to add more lines:
$name=odbc_result($rs,"Ful
$otherfield=odbc_result($r
etc
and print them out as required...
well, I assume the values for all records for that field?
in that case, it works perfectly, the only thing you have to do is to add more lines:
$name=odbc_result($rs,"Ful
$otherfield=odbc_result($r
etc
and print them out as required...
<?php
// Define the ODBC Connection string.
$s_conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
// Connect to the ODBC Data source and return an ODBC Connection Resource.
$r_conn = odbc_pconnect($s_conn, '', ''); // More on the pconnect right after this broadcast.
// Define the SQL Statement to be used.
$s_SQL = 'SELECT * FROM customer';
// Execute the query and get a results set resource.
$r_results = odbc_exec($r_conn, $s_SQL);
// Build the output.
$s_output = '';
$s_keys = NULL;
$i_count = 0;
while (False !== ($a_row = odbc_fetch_array($r_result
{
if (is_null($a_keys))
{
$s_keys = '<tr><th>#</th><th>' . implode('</th><th>', array_keys($a_row)) . '</th></tr>';
}
$s_output = '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
}
// Output the entire results.
echo <<< END_HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<table border="1">
<thead>
{$s_keys}
</thead>
<tbody>
$s_output
</tbody>
</table>
</body>
</html>
END_HTML;
?>
// Define the ODBC Connection string.
$s_conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
// Connect to the ODBC Data source and return an ODBC Connection Resource.
$r_conn = odbc_pconnect($s_conn, '', ''); // More on the pconnect right after this broadcast.
// Define the SQL Statement to be used.
$s_SQL = 'SELECT * FROM customer';
// Execute the query and get a results set resource.
$r_results = odbc_exec($r_conn, $s_SQL);
// Build the output.
$s_output = '';
$s_keys = NULL;
$i_count = 0;
while (False !== ($a_row = odbc_fetch_array($r_result
{
if (is_null($a_keys))
{
$s_keys = '<tr><th>#</th><th>' . implode('</th><th>', array_keys($a_row)) . '</th></tr>';
}
$s_output = '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
}
// Output the entire results.
echo <<< END_HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<table border="1">
<thead>
{$s_keys}
</thead>
<tbody>
$s_output
</tbody>
</table>
</body>
</html>
END_HTML;
?>
Idiot!!!!
$s_output = '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
should be ...
$s_output .= '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
.= and not =
Append not assign.
$s_output = '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
should be ...
$s_output .= '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
.= and not =
Append not assign.
ASKER
To angelIII,
I did not make myself clear.
The php file above produces the EXACT html for the browser, as follows.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
FullName field
conn result: DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= ;DefaultDir=/services4/web
In other words, the php lines after print("conn result: $conn\n"); produce NOTHING. Note that even the </body> and </html> that are AFTER the ?> are missing.
I've never seen anything this strange in thousands of php pages I've written.
Any clues from ANY of you will be MOST appreciated. I'd say this behavior borders on the impossible.
I did not make myself clear.
The php file above produces the EXACT html for the browser, as follows.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
FullName field
conn result: DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= ;DefaultDir=/services4/web
In other words, the php lines after print("conn result: $conn\n"); produce NOTHING. Note that even the </body> and </html> that are AFTER the ?> are missing.
I've never seen anything this strange in thousands of php pages I've written.
Any clues from ANY of you will be MOST appreciated. I'd say this behavior borders on the impossible.
ASKER
To RQuadling,
I tried your script. Apparently it has a php error. See this link. Following the link is the ACTUAL SOURCE as I have it.
http://www.rkassociates.com/LSS/RQuadling.php
<?php
// Define the ODBC Connection string.
$s_conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
// Connect to the ODBC Data source and return an ODBC Connection Resource.
$r_conn = odbc_pconnect($s_conn, '', ''); // More on the pconnect right after this broadcast.
// Define the SQL Statement to be used.
$s_SQL = 'SELECT * FROM customer';
// Execute the query and get a results set resource.
$r_results = odbc_exec($r_conn, $s_SQL);
// Build the output.
$s_output = '';
$s_keys = NULL;
$i_count = 0;
while (False !== ($a_row = odbc_fetch_array($r_result
{
if (is_null($a_keys))
{
$s_keys = '<tr><th>#</th><th>' . implode('</th><th>', array_keys($a_row)) . '</th></tr>';
}
$s_output .= '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
}
// Output the entire results.
echo <<< END_HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<table border="1">
<thead>
{$s_keys}
</thead>
<tbody>
$s_output
</tbody>
</table>
</body>
</html>
END_HTML;
?>
Any suggestions?
rkorts
I tried your script. Apparently it has a php error. See this link. Following the link is the ACTUAL SOURCE as I have it.
http://www.rkassociates.com/LSS/RQuadling.php
<?php
// Define the ODBC Connection string.
$s_conn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("lss.mdb") ." ;DefaultDir=". realpath(".");
// Connect to the ODBC Data source and return an ODBC Connection Resource.
$r_conn = odbc_pconnect($s_conn, '', ''); // More on the pconnect right after this broadcast.
// Define the SQL Statement to be used.
$s_SQL = 'SELECT * FROM customer';
// Execute the query and get a results set resource.
$r_results = odbc_exec($r_conn, $s_SQL);
// Build the output.
$s_output = '';
$s_keys = NULL;
$i_count = 0;
while (False !== ($a_row = odbc_fetch_array($r_result
{
if (is_null($a_keys))
{
$s_keys = '<tr><th>#</th><th>' . implode('</th><th>', array_keys($a_row)) . '</th></tr>';
}
$s_output .= '<tr><th>' . ++$i_count . '</th><td>' . implode('</td><td>', $a_row) . '</td></tr>';
}
// Output the entire results.
echo <<< END_HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Test Access 2nd try</title>
</head>
<body>
<table border="1">
<thead>
{$s_keys}
</thead>
<tbody>
$s_output
</tbody>
</table>
</body>
</html>
END_HTML;
?>
Any suggestions?
rkorts
>To angelIII,
>I did not make myself clear.
Indeed, I did not understand it like that, now it is clear.
Now, you have to "debug" what is happening. put a line print "debug xy"; in between all the statements, to find out which one is actually making the php quit.
do you have any entries in the apache log (ie is the logging configured properly)
>I did not make myself clear.
Indeed, I did not understand it like that, now it is clear.
Now, you have to "debug" what is happening. put a line print "debug xy"; in between all the statements, to find out which one is actually making the php quit.
do you have any entries in the apache log (ie is the logging configured properly)
ASKER
To angelIII:
I turned on some error stuff. It says this:
Fatal error: Call to undefined function: odbc_exec() in /services4/webpages/r/k/rk
Does something have to be configured in php.ini or ???
Thanks
I turned on some error stuff. It says this:
Fatal error: Call to undefined function: odbc_exec() in /services4/webpages/r/k/rk
Does something have to be configured in php.ini or ???
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To angelIII:
I added the odbc_connect function per your code above. It produces this.
FullName field
Fatal error: Call to undefined function: odbc_connect() in /services4/webpages/r/k/rk
My take on this is that the various odbc_*** functions ARE NOT available. It's not clear from the link you gave me what settings should be used / not used in php.ini. If I could get that info, then I could get my host to configure it properly.
Thanks
I added the odbc_connect function per your code above. It produces this.
FullName field
Fatal error: Call to undefined function: odbc_connect() in /services4/webpages/r/k/rk
My take on this is that the various odbc_*** functions ARE NOT available. It's not clear from the link you gave me what settings should be used / not used in php.ini. If I could get that info, then I could get my host to configure it properly.
Thanks
can you run the function phpinfo() on a test page, and check if the odbc section is displayed resp. what odbc settings you have. looks like it is not activated.
I see you opened a new question for that:
https://www.experts-exchange.com/questions/21901223/php-odbc-functions.html
I see you opened a new question for that:
https://www.experts-exchange.com/questions/21901223/php-odbc-functions.html
ASKER
To angelIII:
It contains NOTHING about odbc. How should it be modified?
http://www.rkassociates.com/LSS/phpinfo.php
It contains NOTHING about odbc. How should it be modified?
http://www.rkassociates.com/LSS/phpinfo.php
ASKER
To angelIII:
Note that I awarded you the points on this one even though it is not solved.
I appreciate your continuing efforts to help.
The first answer to my "new" question points to something I already found that is of no value to me. Those kind of "put a link in as the answer" will NEVER get awarded points from me on Experts Exchange.
I have to communicate to my web host (who is largely NOT technical) how to set the php.ini settings in such a way as to make this work.
Thanks,
rkorts
Note that I awarded you the points on this one even though it is not solved.
I appreciate your continuing efforts to help.
The first answer to my "new" question points to something I already found that is of no value to me. Those kind of "put a link in as the answer" will NEVER get awarded points from me on Experts Exchange.
I have to communicate to my web host (who is largely NOT technical) how to set the php.ini settings in such a way as to make this work.
Thanks,
rkorts
check out in the php.ini file if you have a line containing extention=php_odbc.dll
I guess it is commented out (or not present at all).
ensure it is there and not commented, and try again
I guess it is commented out (or not present at all).
ensure it is there and not commented, and try again
ASKER
To angelIII:
I searched the page that results from phpinfo() for the letters "odbc" which ARE NOT on that page.
I have a call into my web host asking them to configure php.ini to enable odbc but they probably won't know what I'm talking about unless I tell them PRECISELY what parameters to set what way.
I searched the page that results from phpinfo() for the letters "odbc" which ARE NOT on that page.
I have a call into my web host asking them to configure php.ini to enable odbc but they probably won't know what I'm talking about unless I tell them PRECISELY what parameters to set what way.
as I said:
extention=php_odbc.dll
needs to be in the php.ini file (on windows platform)
extention=odbc.so
needs to be in the php.ini file (on *nix platform)
extention=php_odbc.dll
needs to be in the php.ini file (on windows platform)
extention=odbc.so
needs to be in the php.ini file (on *nix platform)
ODBC is now built into the windows version. No external DLL needed. Has been for a LONG time!
And the link you gave has no output at all! Very strange
And the link you gave has no output at all! Very strange
the phpinfo returned 4.1.1, so I guess odbc was not built-in yet
http://www.w3schools.com/php/php_db_odbc.asp
connection sample without dsn:
$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("Crafta.mdb") ." ;DefaultDir=". realpath(".");