Web Site & MS SQL

I need to have my website which is hosted by a third party access our corporate Microsoft SQL database using PHP so our customers can access some data.  What exactly needs to be done?  I'm using WAMP version 5.1

Thanks in advance
chasmx1Asked:
Who is Participating?
 
Ray PaseurCommented:
A RESTful interface might do the job nicely.  Here is a teaching example showing all the necessary moving parts.  Please read it over and then post back with any specific question.
<?php // RAY_REST_get_last_name.php
error_reporting(E_ALL);



// DEMONSTRATE HOW A RESTFUL WEB SERVICE WORKS
// INPUT FIRST NAME, OUTPUT FAMILY NAME
// CALLING EXAMPLE:
// file_get_contents('http://laprbass.com/RAY_REST_get_last_name.php?key=ABC&resp=XML&name=Ray');



// OUR DATA MODEL CONTAINS ALL THE ANSWERS - THIS COULD BE A DATA BASE - AS SIMPLE OR COMPLEX AS NEEDED
$dataModel
= array
( 'Brian'   => 'Portlock'
, 'Ray'     => 'Paseur'
, 'Richard' => 'Quadling'
, 'Dave'    => 'Baldwin'
)
;


// RESPONSE CAN BE PLAIN TEXT OR XML FORMAT
$alpha = NULL;
$omega = NULL;
if ( (isset($_GET["resp"])) && ($_GET["resp"] == 'XML') )
{
    // PREPARE THE XML WRAPPER
    $alpha = '<response>';
    $omega = '</response>';
}



// TEST THE 'API KEY' - THIS COULD BE A DATA BASE VALIDATION LOOKUP - AS SIMPLE OR COMPLEX AS NEEDED
$key = (!empty($_GET["key"])) ? $_GET["key"] : FALSE;
if ($key !== 'ABC')
{
    echo $alpha . 'BOGUS API KEY' . $omega;
    die();
}



// LOOK UP THE FAMILY NAME
$name = (!empty($_GET["name"])) ? $_GET["name"] : 'UNKNOWN';

// IF THE NAME FROM THE URL IS FOUND IN THE DATA MODEL
if (array_key_exists($name, $dataModel))
{
    // RETURNS THE APPROPRIATE FAMILY NAME FROM THE DATA MODEL
    echo $alpha . $dataModel[$name] . $omega;
    die();
}

// RETURNS THE UNKNOWN NAME INDICATOR
else
{
    echo $alpha . 'UNKNOWN' . $omega;
    die();
}

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I would create a web service that returns the data.
0
 
chasmx1Author Commented:
Can you be more specific?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Dave BaldwinFixer of ProblemsCommented:
What kind of hosting is your third party provider?  Microsoft's current PHP SQL driver only runs on Windows, not Linux.
0
 
chasmx1Author Commented:
My Web hosting company uses unix and our company is Windows Server 2008 based.
0
 
Dave BaldwinFixer of ProblemsCommented:
There are a couple of ODBC drivers for Linux that will connect to SQL Server 2008 but they're not free.  'php_mssql' and 'FreeTDS' won't work with SQL Server 2008.  While you can develop software on your WAMP platform that will work on Windows and connect to SQL Server 2008, you will not be able to transfer it to your Unix hosting and make it work.  The drivers are not available for Linux.  This has come up several times this year and no one has been able to make it work (for free).
0
 
chasmx1Author Commented:
Is there a way to connect from Network Solutions's unix server that is hosting my site back to my Sql Server to retrieve my data to display data for our customers?  The only other thing i can think of is to export the needed data into a MySQL database on the web server.
0
 
Dave BaldwinFixer of ProblemsCommented:
Does your SQL Server 2008 have a public IP address?  Are you running IIS on it?  How often does the data get updated?
0
 
Ray PaseurCommented:
A RESTful interface is the way that web services are provided between servers.  TheHiTechCoach gave you good advice and I provided an example of how it is done.

Best of luck with it, ~Ray
0
 
chasmx1Author Commented:
I can forward sql traffic to it via the router's port 1433 forwarding and yes IIS is running on it.
0
 
Alpesh PatelAssistant ConsultantCommented:
First of all If you wish to give access of some objects to that user then create a SQL user for that particular database and give read write or some permission.

You can also give SQL object level permission using Grant and Revoke functions.
0
 
chasmx1Author Commented:
I'm not familar with the RESTful interface.  What has to been done on the web site server and the corporate server?

Thanks
0
 
Dave BaldwinFixer of ProblemsCommented:
You have to create two pages or programs, one on each server.  The one on the server where the SQL Server is accepts requests from outside, does a query on the database, and passes the results back as a response to the original request.  As shown in Ray's code,  you can use something like

file_get_contents('http://laprbass.com/RAY_REST_get_last_name.php?key=ABC&resp=XML&name=Ray');

on the outside web server to make a request to the corporate server.  The query string at the end has the data that you want from the database.
0
 
chasmx1Author Commented:
Does the SQL Server have to be running PHP and IIS?  DO you know where I can get some examples of a complete SQL data sample?

Thanks
0
 
Dave BaldwinFixer of ProblemsCommented:
You said that SQL Server 2008 was running on Windows Server 2008 with IIS.  You can use any language you want on that server to return data.  You can install PHP if that makes it easier for you.  Just make sure that the version you install is compatible with the server.  If you install PHP, then you must install the Microsoft PHP SQL driver and the SQL Native client so that PHP can talk to the SQL Server.
0
 
chasmx1Author Commented:
Is their anything I should know about getting SQL data from the company's SQL server into the PHP pages at our hosting company?
0
 
Dave BaldwinFixer of ProblemsCommented:
We've told you basically what you need to do.  What is it that you don't understand?  Maybe if it is too complicated, you should hire a developer to do this project.
0
 
chasmx1Author Commented:
Sorry to bother you. I'll figure it out myself.
0
 
Dave BaldwinFixer of ProblemsCommented:
It's not a bother.  Ray and I and TheHiTechCoach and others spend a lot of time trying to help people here.  So far, I can't tell if anything we have said makes any sense to you.  You have to give us something to work with so we'll be able to figure out what comes next.

If you have never programmed or done PHP pages, then there is a lot that you will need to learn.  And programming is lots and lots of details that have to be correct for programs to work.  I can't tell you in a single post here what you have to do.
0
 
chasmx1Author Commented:
I have 30+ years as a programmer and have coded in over 40 languages but I'm new to PHP.  I just wanted a leg up to save some time.









0
 
Dave BaldwinFixer of ProblemsCommented:
Ray made a post a couple of days ago about PHP saying it's a lot like a 'real' programming language (somewhat 'C' like)... but made to be 'easy' with loose typing and the odd kinds of things you find in javascript.  I find myself mutiplying variables by 1 sometimes to make sure PHP thinks its a number.  The biggest advantage of PHP is their web site which has all their docs online.  http://www.php.net/  I consider it the best reference site of it's type on the web.

The problem you will confront is that Microsoft doesn't care if you are able to connect to MS SQL Server from Linux.  UnixODBC and FreeTDS worked up to SQL 2000 but nothing has been added to them after that.  But Microsoft has changed their interfaces with SQL 2005 and 2008 so the only drivers I know about are some ODBC drivers for Linux that you can buy or use the Microsoft drivers on Windows.

If you have used a language to create web pages, the RESTful interface is slightly different.  Instead of returning a full page, you just return the data to the page that is asking for it.  Hope that makes some sense.
0
 
chasmx1Author Commented:
How about importing the necessary tables into mySQL on the hosting server from MS SQL on the company server?  There are only a few thousand rows and we only add about 50 new rows per day.  Then I could do all my querries against the mySQL database on the web site.
0
 
Dave BaldwinFixer of ProblemsCommented:
If you can do that once a day at a slow time, that would probably be better.  Just less trafffic overall.  I've never exported for SQL Server so I'd have to look that up.  Network Solutions has phpmyAdmin available to manage your MySQL databases so that would be easy for the initial export/import.

You would still need a pair of programs, one on each server, to do the daily updates.  It be easier to schedule the company server and have it contact a secure page on Network Solutions to do it.  That would eliminate any need to make changes to the firewall or anything else.
0
 
chasmx1Author Commented:
Sounds like a winner.
0
 
chasmx1Author Commented:
Is there a way to have my website do updates to it's MySQL database on some kind of timer using PHP or a SQL script?
0
 
Dave BaldwinFixer of ProblemsCommented:
Network Solutions does provide for Scheduled Tasks / Cron Jobs.  The problem with doing it on their server is that you won't have access to your SQL Server unless it has a public IP address where it can be accessed directly from the internet.  Your web site does allow that kind of access so you can write program on the SQL Server that can contact it.

I would create a program on the SQL server that can be launched by Windows Scheduled Tasks every night.  It would query your database for the updates and collect them.  Then it would do a page request to a PHP page on the web hosting and send the updates as a POST so that the PHP program could do the INSERTs into the MySQL database on the hosting.  I make sure there is a ridiculously long access code when I do something like that so it would be difficult to type it in.  Something like "access=1acode2thebase4data3could5for9zee73quirt8jj".  Or longer.
0
 
chasmx1Author Commented:
I'm not sure how to access a web page programmatically from within a Transact SQL script.

Thanks
0
 
Dave BaldwinFixer of ProblemsCommented:
I meant that you could use VB or C# to create a program that will get the data from the database and then make a POST to the web page.
0
 
chasmx1Author Commented:
Okay I'll try it.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.