MySQL vs MS Access

[Epsilon]

I'm fairly good with PHP/MySQL by now. Well, I still use the online documentation a lot for reference, but I get by just fine and I've done some good sized applications.

But I may have a project coming up in a couple weeks where someone is going to want me to develop a member registration system for them on a MS Access database, which I've never done. They're okay with PHP, but they don't want MySQL because they already have a small Access DB on their Windows web server (yuk!) that they will want to keep, and have me expand on it.

So my dilemma is whether to try to learn Access over the next two weeks or turn down the job. Does anyone have experience programming with PHP/Access? I'm wondering how similar it is to MySQL. If they're fairly similar I think I could do it. But if Access is way different (in terms of programming syntax), I might just turn it down.

Any ideas?

[idx]

Besides telling them to visit clueville, I wouldn't be very into the job unless the money was just that good. If the webserver is on their LAN/dmz, you might look into converting the data to MySQL and setting them up with ODBC access to it. So then they can dork away in Access. Basically get to the bottom of why they feel they need the _data_ stored in access. Sure, if there are other apps that you don't want to mess with [that use this DB], then it might be less trouble to just leave it in access and go ODBC.

Check out PEAR::DB and ADODB.

http://php.weblogs.com/ADODB
http://pear.php.net

Both should provide generic SQL functions, so you shouldn't have to change much to get it working with access, then possibly move to MySQL in the future. Just stay away from db-specific things like MySQL's LIMIT. Use, for example in ADODB, the $db->SelectLimit($query, $n) function to convert your SQL into proper LIMIT/OFFSET syntax for that specific engine.

-r

[npa]

remind them that mysql and apache both run on windows, and are both free

[sde]

i did this a long time ago using the odbc method. you will need to enable the odbc dll in your php.ini file.

if you don't have the dll file, download the non-installer version of php and you will find it in there.

http://www.php.net/manual/en/function.odbc-connect.php

there is all the odbc functions. pretty straight forward .. nothing really new to learn, .. just use it like you were using mysql_<whatever>

you will be doing things a little differently, but not much. you don't have access to some of the cool mysql features like mysql_insert_id()

now i don't think i've ever tried, but you may want to look into the mssql functions too. i'm not sure if it is the same type of connections as MS Access, but it may be worth looking into. same thing as the odbc dll, .. comment it out, make sure you have it, then try it.

here is the php support for mssql.

http://www.php.net/manual/en/function.mssql-connect.php

this is just from my experience, .. definately look into idx's suggestion too. i know odbc is slower than most other connections out there so take that into consideration too.

[Epsilon]

Thanks sde and idx. I'll look into all the suggestions. Unfortunately, this particular client will not be hosting on my server (if they were, I'd insist that they use MySQL). They have one of those lame, $20/month virtual hosting accounts, on a Windows server no less, so I will have no control over php.ini.

I think I'll contact the host and ask if they have the ODBC dll installed, or if they'd be willing to install it. If not, I'll try to convince the client to move their hosting over to my server. And if they don't want to do that, I'll just turn the job down. It's too much of a pain in the ass, and I have plenty of other work to keep me busy.

Thanks for the tips, though. I learned something.

[idx]

(mental note taken on the odbc dll thing.. have to keep that in mind for the future )

you will be doing things a little differently, but not much. you don't have access to some of the cool mysql features like mysql_insert_id()

Yeah, that part sucks, although I believe both pear and ADODB support virtual Insert_ID support. (just a guess with pear, I know ado works) eg: Instead of using the built-in `auto_increment` functionality, the ado class stores and manages ID's in a table. So you just call something like $id = $db->GenID('field_name'); to get your virtual auto_increment value to use. Or $id = $db->Link_ID->Insert_ID(); for the last ID used. (PO_Insert_ID() for the portable table-based ID's)

r