?
Solved

Link SQL table to Webpage

Posted on 2016-11-11
9
Medium Priority
?
111 Views
Last Modified: 2016-11-14
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
Comment
Question by:leadtheway
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41884066
Please show us the script that writes to the SQL express database and some representative test data, thanks.
0
 
LVL 8

Expert Comment

by:Mike in IT
ID: 41884144
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 41884167
> 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:leadtheway
ID: 41884240
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41884244
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41884291
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 41884303
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41884371
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
 
LVL 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 2000 total points
ID: 41884396
With 4,000 rows of data, use sql manager studio to export to excel.  That would be the easiest.
1

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question