How To Create Interface For Sql Database
- Home
- Databases
- MySQL
Hi community, i have been creating and administering Databases for some time. However as a concerned Systems administrator who doesn't want to make the life of these "users /Data entrants" hard when feeling in data into the Database. I was wondering if their is a way to create User interfaces for them with in MySQL so as they don't get to see that ugly interface on their side. Will be glad for important links that are add with in.
Thanks
If you have any experience with .NET programming, you could build either a Web interface, or a WinForms interface through Visual Studio. The free (for small development teams) Community Edition can be downloaded from Microsoft here:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx
Of course, to connect to your MySQL database from your .NET application of choice, you'll need the MySQL .NET connector as well, which can be found here:
https://dev.mysql.com/downloads/connector/net/6.9.html
Here's a (very) brief and simple example of making the connection and retrieving data from the database:
Imports MySql.Data.MySqlClient Public Function ConnectToMySQLDatabase () As DataTable Dim MySQLConn As New MySqlConnectionStringBuilder With MySQLConn . Server = < YOURSERVERNAME > . Database = < MYSQLDATABASE > . UserID = < MYSQLUSERNAME > . Password = < MYSQLPASSWORD > End With Dim MySQLDB As New MySqlConnection ( MySQLConn . ConnectionString ) Dim MyCommand As New MySqlCommand Dim MyAdapter As New MySqlDataAdapter Dim MyData As New DataTable Dim SQL As String = String . Empty Try MySQLDB . Open () SQL = "SELECT * FROM <sometable>" MyCommand . CommandText = SQL MyCommand . Connection = MySQLDB MyAdapter . SelectCommand = MyCommand MyAdapter . Fill ( MyData ) Catch ex As Exception MyData . Clear () MyData . Dispose () MessageBox . Show ( ex . Message ) Finally If Not MyCommand Is Nothing Then MyCommand . Dispose () End If If Not MyAdapter Is Nothing Then MyAdapter . Dispose () End If If Not MySQLDB Is Nothing Then MySQLDB . Close () MySQLDB . Dispose () End If End Try Return MyData End Function
Of course, it sounds like you'd require a lot more if you're planning on giving them access to manipulate the data and save their changes back to the database, but these are the "basics". Also, this code is only an example. I know it won't work exactly as written. You would have to fill in the "variables" I've designated in the "<>" sections, and then I'm only about 98% sure it will work because, well, there's always the possibility of a typo here or there.
- ROM
- CPU
- RAM
- GPU
12 Replies
You have a couple of options here. However, if you're working with multiple people, I recommend a PHP page to get all of it done. If you're going to go that route, though, you need a page for them to log in to keep session information. This session information will tell you who edits what and you can keep fields in there to record the times, etc. This would be the most universal route to go.
The other option I recommend is a Microsoft Access front end. You can tie front end forms with backend SQL databases. Although this can become cumbersome and (depending on the DB size) can slow things down, it is an option. At least with a custom PHP page you can work with the data directly.
Thanks you so much Jackal Lear, how ever i have ever tried your second option with access but failed to have a success and i kinda of got reluctant to go ahead, how ever if you can recommend me to some sort of reference that be great.
If you have a little of programming experience PHP shouldn't be to hard, let us know.
You could also try www.phpmyadmin.net you can assign different permissions to different users.
The reference would depend on the version of Access you're using, Macros, security options, etc. I think it would be easier in this case to use a HTML/PHP page. That and you can't track and have history of too many changes in Access and when it comes to multiple people with sensitive data, you need that.
When you design something in PHP, the first thing you want to do is keep tracking of who does what. So, in order to do that, you need a login page to start the session on login. This will help you do that.
http://www.tutorialspoint.com/php/php_mysql_login.htm
Once you established sessions based on user ID's or whatever, you can now keep track of who does what. You can do that by putting in a textbox that echoes the user ID and saves it into a custom field when the user saves the record; make sure this textbox is hidden (visibility = false).
I know this will take a bit to learn, but MySQL and HTML/PHP go hand in hand; they work very well together. Feel free to message me if you want more help.
I'm pretty sure our gui is written in Visual studio...
Almost all programming languages have some way or another to talk to different databases, therefor you can write a C#, C++ or java program to talk to MySQL, it just happends that HTML and PHP are the easiest to get started, no developers kits needed.lancelotrussell wrote:
I'm pretty sure our gui is written in Visual studio...
If you have any experience with .NET programming, you could build either a Web interface, or a WinForms interface through Visual Studio. The free (for small development teams) Community Edition can be downloaded from Microsoft here:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx
Of course, to connect to your MySQL database from your .NET application of choice, you'll need the MySQL .NET connector as well, which can be found here:
https://dev.mysql.com/downloads/connector/net/6.9.html
Here's a (very) brief and simple example of making the connection and retrieving data from the database:
Imports MySql.Data.MySqlClient Public Function ConnectToMySQLDatabase () As DataTable Dim MySQLConn As New MySqlConnectionStringBuilder With MySQLConn . Server = < YOURSERVERNAME > . Database = < MYSQLDATABASE > . UserID = < MYSQLUSERNAME > . Password = < MYSQLPASSWORD > End With Dim MySQLDB As New MySqlConnection ( MySQLConn . ConnectionString ) Dim MyCommand As New MySqlCommand Dim MyAdapter As New MySqlDataAdapter Dim MyData As New DataTable Dim SQL As String = String . Empty Try MySQLDB . Open () SQL = "SELECT * FROM <sometable>" MyCommand . CommandText = SQL MyCommand . Connection = MySQLDB MyAdapter . SelectCommand = MyCommand MyAdapter . Fill ( MyData ) Catch ex As Exception MyData . Clear () MyData . Dispose () MessageBox . Show ( ex . Message ) Finally If Not MyCommand Is Nothing Then MyCommand . Dispose () End If If Not MyAdapter Is Nothing Then MyAdapter . Dispose () End If If Not MySQLDB Is Nothing Then MySQLDB . Close () MySQLDB . Dispose () End If End Try Return MyData End Function
Of course, it sounds like you'd require a lot more if you're planning on giving them access to manipulate the data and save their changes back to the database, but these are the "basics". Also, this code is only an example. I know it won't work exactly as written. You would have to fill in the "variables" I've designated in the "<>" sections, and then I'm only about 98% sure it will work because, well, there's always the possibility of a typo here or there.
Databases should never be see by end users. Databases are just raw data stores. And you don't make interfaces with a database, that's the wrong idea. Databases are not for end users to interact with... at all.
What you want is an application that sits in front of the database. This is what connects to and talks to the database. The end users only see the application.
Yes Scott, am with you 100% but my problem is that these data entrants complain of how the interface appears when they are trying to feed in data. They say that its is more Techy, and complicated for them to fill in the Data. That's why if i could get a solution of creating some thing like Web interface in form of an app with blank fields where they can just type the Data just like when your Signing up for Facebook.
Thanks Phat for that Brilliant Idea.
Can't use HTML, it's not a programming language itself and it can't connect to databases.Crush3rNL wrote:
Almost all programming languages have some way or another to talk to different databases, therefor you can write a C#, C++ or java program to talk to MySQL, it just happends that HTML and PHP are the easiest to get started, no developers kits needed.lancelotrussell wrote:
I'm pretty sure our gui is written in Visual studio...
Scott Alan Miller wrote:
Can't use HTML, it's not a programming language itself and it can't connect to databases.
Crush3rNL wrote:
HTML and PHP
You need to combination, PHP is the back-end language, HTML is just the front-end.
Please SAM, I might not know my storage, but I do know my web developing ;)
Glad you found it helpful. With the tools above, you should be able to completely design and build a front-end that keeps the users out of the database proper, but gives them access to the data they require. VS 2015 CE should give you everything you need to build the Web interface you're envisioning. Obviously, I'm more of a Visual Basic programmer than anything, and I personally still prefer to do WinForms applications for much of my own development, but this same basic code construction can be used for either Web or WinForms, and can easily be translated to C#, if that's more to your requirements.Valhacks wrote:
Yes Scott, am with you 100% but my problem is that these data entrants complain of how the interface appears when they are trying to feed in data. They say that its is more Techy, and complicated for them to fill in the Data. That's why if i could get a solution of creating some thing like Web interface in form of an app with blank fields where they can just type the Data just like when your Signing up for Facebook.
Thanks Phat for that Brilliant Idea.
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.
How To Create Interface For Sql Database
Source: https://community.spiceworks.com/topic/1811063-mysql-user-interface-creation
Posted by: sailerimalk1982.blogspot.com
0 Response to "How To Create Interface For Sql Database"
Post a Comment