Best option/opinion on how to load website data - Html/PHP into a SQL Server Database?

Question

I have been thinking and trying to expand my knowledge of different tools. One thing I have never had personal experience with is getting website data that users enter, and have that loaded into a SQL Server Database. There are several tables in this database and I need a tool/advise on which is a good tool to use to have that data loaded into my data warehouse after end users enter information for different fields into the website. I am an experienced SQL user but have never needed to do this. This wouldn't be needed for any enterprise application, but more for personal use/knowledge. Can anyone provide any insight on the best tool/api they have used to make this happen. Obviously I know this can be done from sharepoint but I am looking for other ideas. Thanks in advance

Answer 1

You can obviously do it manually in PHP with any open connection, but there are so many security things you have to account for.

If your starting out your better off using a PHP framework which handles a lot of the under the hood work for you. If there's a security issue with the framework the framework maintainers will handle it.

You must validate all user inputted data to avoid hackers slipping junk or hostile things into your form.

A framework also allows you to generate a working product faster, and your not constantly worried about the fine details.

Answer 2

You can do it directly from a Stored Procedure:

Enable Ole Automation Procedures:

sp_configure 'show advanced options', 1
GO
RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;

Just run this:

Declare @Object as Int;
DECLARE @hr  int
Declare @json as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
                 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json), N'$.elements')
WITH (   
      [type] nvarchar(max) N'$.type'   ,
      [id]   nvarchar(max) N'$.id',
      [lat]   nvarchar(max) N'$.lat',
      [lon]   nvarchar(max) N'$.lon',
      [amenity]   nvarchar(max) N'$.tags.amenity',
      [name]   nvarchar(max) N'$.tags.name'     
)
EXEC sp_OADestroy @Object

This query will retrieve data about all the cinemas in Auckland from OpenStreetMap.

Yes, is a REST call directly from SSMS so you can create a stored procedure and your SQL Server will automatically call your website.