connecting to SQL database

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi Folk

I have been asked to create an e-commerce website, where we need to
connect to an SQL database. The database is used to run their POS in
several branches. As this database is on the company server and we are
hosting the website with a webhost we have to solve an  communication

Here are the options that I can see:

a. create a branch like environment on the webserver and then directly
access the branch database (OBDC??)

b. run a script that sucks data from the SQL environment and then ftps
it as a text file to a LAMP server. PHP and Mysql (my preferred
platform), will then notice the new file and do something with it?

c. have a direct OBDC connection between the website and the company's

What option do you recommend?  What difficulties do you see?

I would love to get some ideas from you and more ideas on the typical



Re: connecting to SQL database

windandwaves wrote:
Quoted text here. Click to load it


Too little information for what you're asking.

What database are they going to have on their company server?  Do they want you  
to have direct access to this server (possible security issues)?  How often is  
the database updated?  Do they need live data?  If not, how old can the data be  
and still be valid?

There are any number of possibilities beyond what you're asking.  But there's  
way too many variables to even have a reasonable list of possibilities.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: connecting to SQL database

Hi Jerry

Thank you for your reply.

It is a SQL server on their company server.  We dont want to talk to it
directly because of security issues. The database is updated daily most
of the time, with some specials going live almost instantly.

Your questions are actually really useful, because they give me more
confidence that I am on the right track.

What else do you like to know?

Thank you


Re: connecting to SQL database

windandwaves wrote:
Quoted text here. Click to load it


Sorry for the delay; I've been musing on your questions, thinking of the various  
options.  And I wouldn't necessarily say you're asking the right questions based  
on what I asked! :-)

You indicate it's a SQL server.  But there are several of them around there,  
such as Microsoft's SQL Server, Oracle, DB2, MySQL, Postgres and many others.  
What you need to do is going to be based the options available to you - and that  
depends on the server.

I can understand the security issues - but many SQL database servers can handle  
that.  For instance, you can limit external connections to originating from one  
IP address only (your web server), and further isolate that to only allow SELECT  
requests on specific tables and/or views.

Or if the database supports replication, you can have all or part of it  
replicated to another server.

But if they are limiting access to the company database due to security issues,  
then you won't be able to suck the data from the database.  The transfer will  
need to be initiated by the server.  Same with an ODBC connection to the server.

IOW, you're going to need to work with the DBA on the server end to discuss your  
options and how he wants to allow you to handle it.

And I would suggest you then ask in a newsgroup specific to that database;  
you'll get better answers on questions like this, because it really isn't a PHP  
question - it's database-related.

And BTW - I typically do not use ODBC for something like this, if there is a  
database-specific interface available.  ODBC is great for when you don't have a  
specific database in mind or it's one PHP can't talk to directly.  But being  
general-purpose, it will have higher overhead because it has to first translate  
your calls to database-specific ones, then call the database.  And you won't be  
able to make use of features specific to your database.

In your case, you know what database you'll be working with, and if the  
interface does exist you can make better use of your database.

Hope this helps.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline