MySQL front end tools like Workbench PhpMyAdmin and MySQL connection libraries

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.

Python MySQL

Python users can use mysqlclient , sqlalchemy to manage MySQL.

Java & MySQL

Java in your libraries add mysql-connector.jar and start using MySQL from your java programs.

SQL and Functions

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.

PHP using MySQLi

<?Php
require "config.php";// Database connection file.

$query="select * from student";
if ($result_set = $connection->query($query)) {
while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
echo $row['id'],$row['name'],$row['class'],$row['mark']."<br>";
}
 $result_set->close();
}
?>

Python using mysql.connector

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)

Java jdbc

	String url = "jdbc:mysql://localhost:3306/my_tutorial";
	String uname = "root";
	String pass = ""; // your password 
	String query = " SELECT * FROM STUDENT ";
	
	Class.forName("com.mysql.jdbc.Driver");
	Connection conn = DriverManager.getConnection(url,uname,pass);
	Statement st= conn.createStatement();
	ResultSet rs= st.executeQuery(query);
	
	rs.next();
	System.out.println(rs.getString("name"));

Installation of Database Tables & records of a Database What is SQL
SELECT query

Subscribe to our YouTube Channel here

Managing MySQL database Frontend tools