Programming mysql databases on Windows 10 Pro

By Kiley McDaniel

CEMC Conference 2017

http://www.cemc.uwaterloo.ca/

http://www.cemc.uwaterloo.ca/events/csteachers.html

Categories: networking (TCP/IP, ports), databases, programming, PHP, Python, Java, Windows, web server (LAMP/XAMPP), web design

When you have turned on IIS (Internet Information Services) in Programs and Features then Windows 10 will install the webserver equivalent of Apache.   Be sure to enable CGI found under IIS -> World Wide Web Services -> Application Development Features. Once the install is complete you can test the IIS by typing “localhost” or 127.0.0.1 in the browser of your choice to determine if the service is running.   If it was installed correctly then you will see the following result in the browser:

Next, you need to double check that Handler Mappings and FastCGI Settings are listed in the IIS Manager dialogue found in Administrative Tools before installing php. Handler Mappings and FastCGI Settings will appear if you enabled CGI as illustrated above.

Next, you can start install of LAMP (Linux.Apache mySQL PHP)   or XAMPP (Windows platform).   We can use Web Platform Installer - Microsoft 5.0   (https://www.microsoft.com/web/downloads/platform.aspx) to start this process.   I installed the following applications:

Next, we need to test our mysql database environment.   We want to use phpmyadmin to create and manage mysql databases and tables.   Eventually, we want to be able to write programs in php, Python and Java to access data using SQL (standard query language : ex. select * from spy where age > 29) to return datasets.   We can use mysql to teach database concepts (ex. Structure, fields, records, etc), big data, analytics and test algorithms on the world’s most popular open source database platform used by Google, Facebook and Twitter.

Once you have copied “phpmyadmin” to wwwroot then you can test the application by typing “localhost/phpmyadmin” in a browser URL locator and the following should appear :

Type in username (ex. root) and your secure password and the following should show up in the browser:

Et voila!  You have a live webserver you can use with others to test web applications, learn about databases, web programming (ex. php), install Moodle or Wordpress, test JDBC in Java and the options are endless.   On my network, the webserver is running on 192.168.2.31 and the port 3306 is open for mysql connections!  This connects nicely to the study of networking and introduces some advanced IT topics like ports, nodes, rights, chmod, e-commerce, netsh, netstat and can provide a nice segway for IoT.

LESSON 2 - Using phpmyadmin to convert a flat text file database (comma delimited) into a mysql database.

teamstats2.txt

===========

10,55,"Joe Smasher"

3,15,"Tanner Williams"

23,45,"Chris Simon"

23,33,"David Hayter"

43,65,"Greg Miller"

54,54,"Nathan Drake"

34,45,"Daemon Hatfield"

1,100,"Mao Zedong"

23,34,"Ryan Clements"

43,78,"Bobert Dermarkar"

23,45,"Tom Jones"

32,100,"Roberto Alomar"

23,34,"Brad Dundas"

19,78,"Adrianna Majewski"

Text based file :  Notepad

Proposed database structure:

Let’s imports the file as CSV into phpmyadmin and create a mysql database:

We will select CSV format for comma delimited text file:

The image below shows that the file has been imported.

In the image below, we can browse the data in mysql format.   Next, we will use Operations and some edit tools to change the name of the db and table and field names to match our original db design:

Use Operations to change db and table names:

You can change field names in Structure view:

Our db is starting to look good in mysql:

Lesson 3 - Testing mysql database using SQL Tool Pro app - Android app

Before an app like SQL Tool Pro can access your mysql database you will need to grant permissions from mysql command line prompt:

SQLTool Pro app to query mysql db on IIS-WIN10

=====================================

https://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.19' IDENTIFIED BY 'icemcd' WITH GRANT OPTION;

node : 35  = Priv Android = works

node : 15  = Android tablet = works

node : 19  = Playbook = Java JDBC fails

node : 17  = iphone 4 = no suitable app - need ios9 or higher

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.15' IDENTIFIED BY 'icemcd' WITH GRANT OPTION;

# this enables tablet to work on mysql server = check status under phpmyadmin to verify grants

You can  verify grants in phpmyadmin under User Accounts:

Once the nodes or IP addresses on your network have been granted root privilege to mysql server then you can configure SQL Tool Pro app on Android device to access remotely your databases.  If the app is good then the client will allow you to use SQL to return a recordset that matches your query.

Here is how things will look in the app that will test your mysql database setup:

config SQL connection on app.png

Add your connections to mysql databases

config SQL connection on port 3306.png

Configure connection to mysql db running on your IIS (localhost or 127.0.0.1) and mysql server (ex. Version 5.7) on port 3306.   Port 3306 is a dedicated mysql port.

view of teamstats table created in phpmyadmin.png

If your smartphone or tablet have been granted access then you will see the table associated with the mysql db appear in SQL Tool Pro.

SQL query result.png

When you click on the table all records will appear (default query :  select * from ‘table_name’)

SQL Tool Pro - SQL query db.png

Next, you can change the default query to something different and see what new results you get.  Great way to practice SQL.

In the image below, you will see the results generated by the query where we wanted to know how many batters hit the ball more that 20 times  (select * from ‘teamstats’ where hits > 20 ).   This activity will reinforce data types and database design considerations (ex. Db structure).

query results hits.png

In conclusion, this activity will help people understand how the most popular open source database in the world works.  The IIS php mysql phpmyadmin setup will enable dynamic web design, database programming and reinforce the importance of understanding networks.  Personally, I look forward to connecting Java and Python programs to data and using the “LAMP” to test algorithms.

Next Steps

========

Connect to mysql db in Python:

https://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python

Connect to mysql db in Java:

https://stackoverflow.com/questions/2839321/connect-java-to-a-mysql-database

Relational DB:

https://www.sitepoint.com/understanding-sql-joins-mysql-database/

BIG Next Steps

============

Expand database programming to include Big Data, IoT, analytics, AI, deep learning, machine learning and algorithms.   Connect to larger more interesting datasets.

By Kiley McDaniel





 

Tweet