• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

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
0
leadtheway
Asked:
leadtheway
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Ray PaseurCommented:
Please show us the script that writes to the SQL express database and some representative test data, thanks.
0
 
Mike in ITIT System AdministratorCommented:
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.
0
 
Scott Fell, EE MVEDeveloperCommented:
> 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/
1
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
leadthewayAuthor Commented:
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

0
 
leadthewayAuthor Commented:
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
0
 
Ray PaseurCommented:
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.
0
 
Scott Fell, EE MVEDeveloperCommented:
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.
0
 
leadthewayAuthor Commented:
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
0
 
Scott Fell, EE MVEDeveloperCommented:
With 4,000 rows of data, use sql manager studio to export to excel.  That would be the easiest.
1

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now