PHP and Access

paulsheffII · 9 Jun 2007 at 20:42

I'm building a site at the moment and this guy wants a live update of how busy the place the site is for actually is.

He's building something in access to handle customers. He'll obviously be on a windows machine, the site will be running on Linux.

So I either need to get his machine accessible from the web to get the php to connect to it and grab a number, or I was wondering if anyone knew if access could call an address say http://www.site_name.com/peeps_on_board.php?people=$value periodically?

I've not worked with Access much tbh.

Thanks

fluiduk · 9 Jun 2007 at 21:24

Don't bother using access for web based stuff, it's carp!

How often will the local data be changed?

You could setup a cron job to extract the data from the access database and upload it to your web server every hour or something, using xml, csv etc

Aaron

psyr33n · 9 Jun 2007 at 22:57

or I was wondering if anyone knew if access could call an address say http://www.site_name.com/peeps_on_board.php?people=$value periodically?

Output the data as XML, then write an Access macro to import it every so often.

paulsheffII · 10 Jun 2007 at 00:47

Sorry guys I've not made myself clear.

The staff will be using a local machine based interface for inputting customer data onto the access database during operations hours. For health and safety they'll need to know who is in the building at any one time. So this file on the PC in the building will know the exact number of people using the facilities at that one time. He wants to make that data available to the website live so people can see whether it's too busy to attend or not. What I need to do is

a) make it so that a web page can access that file sat on that machine so it can pull a live figure from file without it having to be added manually separately.
b) A macro thingy kind of a reverse of what psyr33n suggested that basically every five minutes goes:

Number_of_people_in_building = x;
getURL("http://www.website.com/how_busy.php?number=Number_of_people_in_building")

type thing to pass that parameter to the webpage so it can be saved to a MySQL. Not even sure that's possible with Access.

I could have course try and get him to scrap the local Access file and make him use an online CMS style to track people in and out of the building through a web interface with php and MySQL meaning the local and online service use the same DB. Course if the net connection goes down then it’s a problem so while it's still in the infancy of thinking I was trying to feel out any possibilities.

psyr33n · 10 Jun 2007 at 00:54

So you're essentially asking how to grab data from an Access database (stored on a local machine) using PHP (stored on a webserver)?

Are the computers networked?

paulsheffII · 10 Jun 2007 at 00:54

I wonder if there's something we could run under windows that grabs the figure from the Access file then passes it to the webpage if Access can't do that?

noob · 10 Jun 2007 at 03:31

fluiduk said:
Don't bother using access for web based stuff, it's carp!

For crying out loud when will people realise it's all to do with the client's circumstances. I've used Access in a web context and it was perfectly fine. Yes, I know it will not work for big projects but for small it is fine.

I know people have an 'alliegance' with certain languages, etc but instead of siding with a certain technology think about what the client needs because it is about what they need.

paulsheffII · 10 Jun 2007 at 03:48

psyr33n said:
So you're essentially asking how to grab data from an Access database (stored on a local machine) using PHP (stored on a webserver)?

Are the computers networked?

Yep that's what I'm going for. There will be a broadband connection available to the machine with the Access DB on it yes.

fluiduk · 10 Jun 2007 at 09:25

Hiya

You should be able to get an access macro to output the figure to a file for you.

You could then use sceduled tasks and the cmd ftp client to upload that file every five minutes.

Then on the server side you will need a script that reads the file and inserts it into the database, stick that on a cron job to run every five minutes.

I am afraid my access skills are rusty beyond belief, will have a look later and see how it can be done

Aaron

fluiduk · 10 Jun 2007 at 09:34

EDIT:

Use a query to get the value you need.

Then use a macro to run the query and then use 'OutPutTo' to run the query and the result to a .txt. .csv file etc.

This page explains the cmd ftp side of things http://webtools.live2support.com/windows/ftp.php

I am afraid I do not know how to get the access database to output every 5 minutes, at least not from the GUI, I suspect you could use you a loop in the vbscript

Mickey · 10 Jun 2007 at 12:46

fluiduk said:
EDIT:

Use a query to get the value you need.

Then use a macro to run the query and then use 'OutPutTo' to run the query and the result to a .txt. .csv file etc.

This page explains the cmd ftp side of things http://webtools.live2support.com/windows/ftp.php

I am afraid I do not know how to get the access database to output every 5 minutes, at least not from the GUI, I suspect you could use you a loop in the vbscript

You sure you can't think of a more fiddly/complicated/likely to break solution? Maybe they should have to print out the value, fly it across the office as a paper plane then fax the value to the OP for him to update the website... Honestly, I've never seen you make a sensible post.

Anyway...best option would probably be to write a quick program to pull out the value and call the PHP page as the OP suggested. Run it on startup/as a windows service and have it on a 5 minute loop, job done.

Mick.

fluiduk · 10 Jun 2007 at 12:58

I would rather that, than a url string which could be abused.

Anyway im off, don't appreciate being treated like a t**t when im just offering my opinion

Mickey · 10 Jun 2007 at 13:02

fluiduk said:
I would rather that, than a url string which could be abused.

Anyway im off, don't appreciate being treated like a t**t when im just offering my opinion

You'd rather a way that would probably endup breaking completley than calling an obscure page which no-one would know about? There are plenty of options for making the page secure anyway if there was an issue with that.

paulsheffII · 10 Jun 2007 at 14:23

Guys please, every suggestion is welcomed so thanks everyone for your input.

I think your suggestion Mickey is probably the best route, a go between that can read the access file and call a web page. I thought about just writing a classic ASP page that uses the Access file as a DB, meta refreshes every 5 minutes pulling the DB data and passing it to the site. I could always get that to load in IE on startup.

If anyone could point me in the direction of a cleaner way of doing that I'd be most grateful.

Mickey · 10 Jun 2007 at 14:37

paulsheffII said:
Guys please, every suggestion is welcomed so thanks everyone for your input.

I think your suggestion Mickey is probably the best route, a go between that can read the access file and call a web page. I thought about just writing a classic ASP page that uses the Access file as a DB, meta refreshes every 5 minutes pulling the DB data and passing it to the site. I could always get that to load in IE on startup.

If anyone could point me in the direction of a cleaner way of doing that I'd be most grateful.

It'd be cleaner to have an app running in the system tray, add me to msn if you'd like help: mick AT mick-adams DOT co DOT uk

Mick.