Querying Database from different applications and updating records – SQL basics
MySQL runs on using Client Server architecture and you need a front end tool to manage this database. You can download and install MySQL database from MySQL.com.
GUI Front end tools to handle MySQL database
Requirement of webserver
It is not necessary that you must have webserver to work on MySQL database. You can use MySQL workbench as front end tool to handle MySQL database. However the combination of running PHP with MySQL database is most popular so this configuration works with any webserver like Apache , IIS and others.
MySQL workbench
Download and use this front end tool to connect to MySQL database if you want to use as desktop application. No need to install PHP or webserver to connect to MySQL database. If you are not using PHP then MySQL workbench is the best choice. As a tool of Oracle Corporation you can easily connect and use it. You can store your set of queries as sql files and reuse it.
PhpMyAdmin
If you have PHP support with a web server running, this is one more good choice for you to manage MySQL database. You can use it in your localhost or can deploy it in your website. Most likely your web host is already providing you PhpMyAdmin support to manage your database. PhpMyAdmin is available in many languages (not programming language) you can select language of your choice.
You can run PhpMyAdmin in your local host and connect to google cloud to manage your MySQL database after doing proper settings like IP configuration etc.
Using programming languages
From your PHP or Python or Java or from any other programming language you can connect to MySQL database and execute these queries. You need to install the particular driver or connector or use the library to establish connection from your script to MySQL server.
PHP MySQL
We can connect our PHP script to MySQL by using mysqli support. The old one was mysql and it is not recommended now as it is replaced by mysqli or known as mysql improved.
PHP PDO
Connection from PHP to MySQL is available through PDO ( portable data object ). PDO offers a common data management layer for PHP to connect to 12 different databases. However mysqli is exclusively for connection between PHP and MySQL.
Queries are common but functions are different in different libraries. The most common query to collect records is SELECT query.
SELECT * FROM table_name
This query remain same irrespective of platform we use to retrieve the records. The respective driver or library of the platform will have its own function to execute the above query. Here are some sample codes using the common query in different languages to get the same records from the MySQL table.
import mysql.connector
my_connect = mysql.connector.connect(
host="localhost",
user="userid",
passwd="password",
database="database_name"
)
####### end of connection ####
my_cursor = my_connect.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone() # we get a tuple
#print each cell ( column ) in a line
print(my_result)