PHP and Access

Hi,

First timer here.
Where's the best place to educate myself on how to use PHP in
conjunction with Microsoft Access--good online tutorials, sites, blogs,
etc...? I work at a small online grocery service that would like to
develop interfacing between our databases with the web to allow
customers/users to directly manipulate profile data and make orders
that will filter directly into our database without manual entry.

thx,
Ross

re: PHP and Access

rcnews@gmail.com wrote:You can do it with ODBC. Are you confident that Access can handle the
load, though?

miguel
--
Photos from 40 countries on 5 continents: http://travel.u.nu
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world: http://airport.u.nu

re: PHP and Access

Miguel Cruz skrev:Access can be pretty tough... But any I agree with Miguel. You should
try some of the alternatives like SQLite or something. SQLite will work
on many other platforms than just Windows (even though you can get
Access working on Linux, it isnt something that it is fun, and most
users wont even bother going through the trouble for something like Access).

You wont find many guides on how to work with Access and PHP, though it
is totally possible.


If you decide to work with Access anyway, here is what you will have to
do in Windows.

1. Go to the control panel @ Windows.
2. Go to administration.
3. Go to Data Sources (ODBC) (I think that is what it is called).
4. Click on "System-DSN".
5. Click "Add".
6. Choose "Microsoft Access Driver (*.mdb)"
7. Choose a name-id for the connection (something simple like "myaccess").
8. Click "Choose" and choose the access database, that the connection
should work with.


Now you make a testing PHP-document to test the actual connection.

Write something like:
<?
$conn = odbc_connect("myacces");

$f_test = odbc_exec($conn, "SELECT * FROM testtable") or
die(odbc_errormsg($conn));
while($d_test = odbc_fetch_array($f_test){
print_r($d_test);
}
?>


Be aware, that you should create "testtable" and fill it with somedata,
so that PHP will print something :)

I havent testet any of the code.

--
Best regards and good luck
Kasper Johansen aka knj

re: PHP and Access

I did it with ODBC to gain myself some time to install and learn MySQL.

If ODBC load is too high, you can use COM to create dbOpenTable type
recordset objects. Never had any load problem with those.

Best regards

rcnews@gmail.com wrote:

re: PHP and Access

I suggest you to convert your access database into some other db. For
example postgresql or mysql.

Kaarel

rcnews@gmail.com wrote:

re: PHP and Access

Thanks for all the responses.

It sounds like Microsoft Access is not a favorite among programmers
here. But I'm afraid I'm stuck with it (for now at least), since our
company has been using it for about 4 years and we don't have the
resources/time right now to switch over to another system (though I
like Dikkie Dik's advice of a progressive transfer).

That being said, I'm actually wondering if PHP is the best language to
use for connecting our Access database to the website based on your
comments? What I know is that we're currently using PHP (written by
another technician) to have our website forms filter data into email
accounts, so that the data can be manually entered later on in Access.
You say it's possible, Kasper, to combine Access and PHP, but not that
common. So maybe the best thing is to switch to another database
system. At the same time, I don't fully understand some of your
suggestions. For example, are softwares like SQLite or MySQL a
replacement for your Microsoft Access database, or are they associate
programs to help connect your existing databases to your website? The
idea of transferring our existing data into another software seems
rather daunting.

I hope you understand what I'm getting at.
your student,
Ross

Kasper Johansen wrote:

re: PHP and Access

Definately agree with that last post. Store the data in a separate
database MySQL is the most common.

This is exactly the sort of job XML was designed for. Dump data out of
the layer database in XML and then user XML parser to read it in to
Access.

Obiron

re: PHP and Access

rcnews@gmail.com wrote:And most other environments where people have been exposed to the
alternatives.
I have done this (a few years back) and I didn't run into any particular
difficulties in terms of being able to make things happen.

However, at that time - and maybe things have changed - it seemed like a
lot of the relationalness of my queries was being handled by the ODBC
drivers or something, because queries that hit several large tables -
even if they didn't use very much of their data - resulted in huge
amounts of data traveling over the network.

As a consequence it ran at about 5% of the speed of a MySQL solution.

If you are just selecting rows by ID and that's it, then this doesn't
matter. But if your database structure is complex, and the behavior I
observed in 2001 is still the case now, then performance could be an
issue.

miguel
--
Photos from 40 countries on 5 continents: http://travel.u.nu
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world: http://airport.u.nu

re: PHP and Access

It sounds like Microsoft Access is not a favorite among programmersThat's a tough question. There is no "best" language. Every language has
its own pros and cons. PHP and MySQL are such an often used combination
that it is hard to be wrong with that. But PHP does not bite Access.

One of the pros of PHP is that it is easy to find a provider that
supports it. These providers will not necessarily support Access to be
installed on their system though. If you are hosting a site yourself or
building an intranet application within your own office, that's no problem.

But if the database gets large, Access has a commercial limit built in,
so I heared. Not that I ever built a database that large. Not even in
MySQL or SQL Server.

What the best language is, depends on your skills, systems and
preferences. Off course, Access goes well with ASP or ASP .NET too. And
maybe you can use it with Ruby On Rails...

MySQL is more like SQL Server than like Access. MySQL is a full blown
database server. No fancy forms, reports, wizzards, etc.

Migrating databases is not that difficult. Your source code in PHP must
be ready for the change, so an abstraction layer is useful. I don't know
if your PHP code is object-oriented, or if you are familiar with the
terms. It means that you define generic "database" or "query" objects,
of which you can build both MySQL and Access variants. There are
probably enough database layers available if you don't want to write one
yourself.

Getting the data over to another database is easy with access. Simply
create linked tables to the new database and run some queries. Remember
that if you want to query a linked table to a database server with
Access, a pass-through query is much faster, but is written in the
server's SQL dialect.

Good luck

re: PHP and Access

rcnews@gmail.com wrote:It most definitely isn't. I've had to do a somewhat major project with
it, and it was horrible. But while everyone is trying to steer you
away from it and into something more 'practical', allow me to play
devil's advocate...
Access sucks. Let's get that out of the way right now. But what it
does do well is allow very easy creation of forms and reports. If you
have an Access system that has been around for years, I would be very
surprised if it didn't already have dozens of forms and reports that
have been added into it over the years. Putting aside the notion of
connecting to Access via PHP for a moment, consider the fact that if
you migrate the data to another database like MySQL, what happens to
all of those forms and reports? You either have to recreate them all
from scratch on the web, or redirect Access to use the remote tables
rather than the internal ones (never done this personally, so I don't
know how easy/reliable it would be). Either way, you're adding more
man-hours to the project which may not be worth the payoff.

When you weigh the options, it may just be more practical right now to
simply connect php to Access to dump the data, and then make a note
somewhere of a nice 'project' that you can do some time down the road
when there is more time/resources available.

re: PHP and Access

On 8 Aug 2006 08:50:32 -0700, rcnews@gmail.com wrote:
Without getting into the Access/MySQL debate, it's perfectly feasible
(for a medium sized application). I ran Access with Coldfusion for
many years with no real problems.

Many of the criticisms of Access used to be related to how it
performed in a networked environment (arrrgh!). PHP connects as one
user (sort of) and many of the network table and file locking problems
don't seem to happen (at least in my limited experience).

Setting up and querying the ODBC connections can look a bit daunting,
but it's no more complex than MySQL. There are, however, a few
"gotchas" ...

1) Date formats. Access uses some pretty odd time/date formatting -
you'd have to Google around for the best ways round these. Time/date
functions have given me more grief than anything else.

2) Access uses Jet-SQL, which is slightly different to other
"flavours" of SQL. If you're familiar with it, then you'll be fine.
99% of the work in outputting from a database is in the design of your
SQL statement. *Don't* just cut & paste from Access's query builder
.... it's VILE! Unnecessary nested brackets about 20 deep.

I can't think of a single place you could go for help. Any PHP.NET
online manual is by far the best, as it's crammed with loads of
contributionsand fixes from users.

I'd say *go for it* - just give it a try!

Adam.