Leadtheway
asked on
Link SQL table to Webpage
So i have a script that writes to an SQL express database table mostly for auditing and alerting purposes, I did it like this because it was too large as an CSV for excel, But I'd still like to view and manipulate the data. Is there a way to do this with HTML/IIS. It can either be its own page or a embedded table thats searchable. I have never messed with this type of scenario, but I think it would be helpful to learn. Any help would be wonderful
Please show us the script that writes to the SQL express database and some representative test data, thanks.
There are a lot of different ways that you can accomplish this. Most of them require a lot of programming in the back end, but will make it look nice on the front end. Since you're using PHP it wouldn't be that difficult to create a table in a webpage that will show you everything in your DB table, and even to setup pagination so that it's not just one long list on your webpage. You could use something like DataTables to make it easily searchable, though their Edit plugin is a paid service, you could just create your own.
Though like Ray says we need more information to be able to actually help you with something that will work for you.
Though like Ray says we need more information to be able to actually help you with something that will work for you.
> Is there a way to do this with HTML/IIS.
Html of course can't output data, only display. You will need to rely on sql server or server side code for that. But once you do have data, you can use html and javascript to create charts and spreadsheets either by hand or preferably with a library. Some examples are https://handsontable.com/ or http://www.chartjs.org/. There are a lot of other options here. Using these types of tools does take some expertise in either using php (or .NET) or sql server to get the data to an html page.
Take a look at some reporting tools that can connect to your database and allow you to easily generate reports. These may be much easier to use if you are not a developer.
https://www.domo.com/
https://www.qlik.com/ and see the free version https://www.qlik.com/us/products/qlikview/personal-edition.
http://www.tableau.com/ I have tested this out. I like the mapping features if your data is spatial.
https://doc.periscopedata.com/doc/
Html of course can't output data, only display. You will need to rely on sql server or server side code for that. But once you do have data, you can use html and javascript to create charts and spreadsheets either by hand or preferably with a library. Some examples are https://handsontable.com/ or http://www.chartjs.org/. There are a lot of other options here. Using these types of tools does take some expertise in either using php (or .NET) or sql server to get the data to an html page.
Take a look at some reporting tools that can connect to your database and allow you to easily generate reports. These may be much easier to use if you are not a developer.
https://www.domo.com/
https://www.qlik.com/ and see the free version https://www.qlik.com/us/products/qlikview/personal-edition.
http://www.tableau.com/ I have tested this out. I like the mapping features if your data is spatial.
https://doc.periscopedata.com/doc/
ASKER
yes, i only need to display, SQL is the backend this is the complete script including connection info
$ConnectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"
function WriteSQL ($query)
{
if ($debug -eq $true) {Write-Host $query}
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $connectionString
$Connection.Open()
$Command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $Connection
$command.CommandText = $query
$command.ExecuteNonQuery()
$connection.Close()
}
$ErrorActionPreference = "Continue"
$strComputer = $env:ComputerName
$colDrives = Get-PSDrive -PSProvider Filesystem
ForEach ($DriveLetter in $colDrives) {
$StartPath = "E:\Share\"
Get-ChildItem -LiteralPath $StartPath -Recurse -Directory |
ForEach {
$FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath)
(Get-Item -LiteralPath $FullPath).GetAccessControl() |
Select * -Expand Access | where {$_.IsInherited -notlike "true" } |
Select @{N='ServerName';E={$strComputer}},
@{N='FullPath';E={$FullPath}},
@{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}},
@{N='Owner';E={$_.Owner}},
@{N='Trustee';E={$_.IdentityReference}},
@{N='Inherited';E={$_.IsInherited}},
@{N='InheritanceFlags';E={$_.InheritanceFlags}},
@{N='AceFlag';E={$_.PropagationFlags}},
@{N='AceType';E={$_.AccessControlType}},
@{N='AccessMasks';E={$_.FileSystemRights}} } |
%{
$query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
WriteSQL $query
}
}
ASKER
and i haven't set on a front end, I'd like something that can be manipulated through web browser, Whichever is easier and repeatable
What programming language are you using in this code snippet? You can ask a moderator to put your question into that Zone, and you will get more eyes on the question.
Ray, that looks like Windows Powershell. For what it is worth, you can hit php pages via Powershell like below. I have some scheduled tasks I run this way.
@leadtheway, I think Ray was asking about any PHP code that you may have been using.
It looks like there are 3 parts here. 1) Sending data to sql server (servername,fullpath,type, owner,trus tee,inheri ted,inheri tanceflags ,aceflag,a cetype,acc essmasks) 2) Accessing the data from sql server and 3) Viewing the data from a web browser.
Can you help us understand about your use better? About how many rows of data get sent to sql server? Is that a static number or will it increase by x every month? How do you need to manipulate the data, view, sort, filter? If we assist you with a php option for accessing the database are you able to follow or do you need assistance with writing something from scratch? (if so, please see https://www.experts-exchange.com/gigs/ or https://www.experts-exchange.com/live/ as we typically will help with code here and not always write a complete solution from scratch). I think once we get past step 2, you can choose the front end..
Let's try and make this thread focus on just one item. If you need help accessing data, we can assist with that, then ask a new question for the 3rd part to keep things succinct.
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -c "(new-object system.net.webclient).downloadstring('https://domain.com/path_to/page.php')"
@leadtheway, I think Ray was asking about any PHP code that you may have been using.
It looks like there are 3 parts here. 1) Sending data to sql server (servername,fullpath,type,
Can you help us understand about your use better? About how many rows of data get sent to sql server? Is that a static number or will it increase by x every month? How do you need to manipulate the data, view, sort, filter? If we assist you with a php option for accessing the database are you able to follow or do you need assistance with writing something from scratch? (if so, please see https://www.experts-exchange.com/gigs/ or https://www.experts-exchange.com/live/ as we typically will help with code here and not always write a complete solution from scratch). I think once we get past step 2, you can choose the front end..
Let's try and make this thread focus on just one item. If you need help accessing data, we can assist with that, then ask a new question for the 3rd part to keep things succinct.
ASKER
the number of lines will vary depending on what i run the script against, for this current table its about 4k table rows. I was hoping there was something simple i could drop in an HTML page that would pull this data and be able to view and sort it. I might have to try visual studio, i already have the database connected to it, just getting into a usable format
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.