Link to home
Start Free TrialLog in
Avatar of Leadtheway
LeadthewayFlag for United States of America

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

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.
Avatar of Scott Fell
> 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/
Avatar of Leadtheway

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
             }

    }
             

Open in new window

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.
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -c "(new-object system.net.webclient).downloadstring('https://domain.com/path_to/page.php')"

Open in new window


@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,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks)  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.
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
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial