Last updated on December 11th, 2023
What is MySQL database?
MySQL is an open-source relational database management system (RDBMS) that helps you store, organize, and manage large amounts of data. It is one of the most popular database systems used for web applications and is known for its reliability, flexibility, and ease of use.
Key aspects of MySQL:
1. Relational Database Management System (RDBMS): MySQL is designed to manage data in a structured format, organizing it into tables with rows and columns. This structure allows for efficient storage, retrieval, and manipulation of data.
2. Open-Source: Being open-source means that MySQL is free to use and can be modified to fit specific needs. It is supported by a large community of developers, making it a popular choice for various projects.
3. Data Management: MySQL enables you to create, read, update, and delete data from databases. You can perform operations such as adding new records, updating existing data, and deleting unwanted information.
4. Scalability: MySQL is known for its scalability, meaning it can handle large volumes of data and numerous concurrent users. This makes it suitable for applications that require efficient handling of heavy workloads.
5. Compatibility: MySQL is compatible with various operating systems, including Windows, Linux, and macOS. It also works seamlessly with different programming languages, making it a versatile choice for developers.
6. Security: MySQL provides security features to protect your data, including user authentication, access control, and encryption. This ensures that sensitive information remains safe from unauthorized access.
MySQL is part of the XAMPP package, so if you have XAMPP installed, you also have a MySQL database installed.
What is PHPMyAdmin?
PHPMyAdmin is a free and open-source tool written in PHP that provides a web-based graphical user interface (GUI) for managing and administering MySQL databases. It allows users to interact with MySQL databases through a web browser, making it easier to perform various database management tasks without the need for extensive knowledge of the MySQL command-line interface.
Key aspects of PHPMyAdmin:
1. Web-Based Interface: PHPMyAdmin provides a web-based interface that can be accessed through a web browser. It enables users to perform various tasks such as creating and managing databases, executing SQL queries, and managing tables and data visually.
2. Database Management: Users can create, modify, and delete databases, tables, fields, and rows in MySQL databases using an intuitive graphical interface. This makes it easier for beginners to handle database operations without having to use complex command-line instructions.
3. User Privileges Management: PHPMyAdmin allows administrators to set user privileges and manage user accounts within the MySQL database. This feature helps control access to the database and ensures that only authorized users can perform specific operations.
4. SQL Query Execution: Users can execute SQL queries directly within PHPMyAdmin. This feature is beneficial for running custom queries, retrieving specific data, or performing advanced database operations without the need for a separate MySQL client.
5. Import and Export Data: PHPMyAdmin facilitates the import and export of data in various formats, including SQL, CSV, and XML. This feature is useful for backing up databases, transferring data between different systems, or migrating data to other platforms.
PHPMyAdmin is part of the XAMPP package, so if you have XAMPP installed, you also have PHPMyAdmin installed.
How to create a MySQL database using PHPMyAdmin?
You should already have XAMPP installed on your computer. If not, please read the tutorial “What is XAMPP and how to use it“.
Now follow this simple step by step guide:
Step 1: Start the Apache and MySQL Services
Open XAMPP Control Panel.
Start the Apache and MySQL services by clicking on the ‘Start’ buttons next to them.
Step 2: Access PHPMyAdmin
Open your web browser.
Type “http://localhost/phpmyadmin/” in the address bar and press Enter.
This will take you to the PHPMyAdmin dashboard, where you can manage your MySQL databases.
Step 3: Create a Database and a table in it
In the PHPMyAdmin interface, click on the “Databases” tab from the top menu.
Enter a name for your new database in the “Create database” field (for example: my_test_db) and click the “Create” button. Now your new database is created.
Create a table in your database: enter a name for your table in the “Table name” field (for example: my_test_table) and click the “Create” button. Now your table in your database is created.
Several columns are automatically created in the table. By default, the number of columns is 4. Name one of the columns by typing the name of the column in the name field, for example my_test_col. Leave the other fields with their default values. Save the column in table by clicking the Save button at the bottom of the page.
How to connect to MySQL database in PHP?
We will connect to a database using the ‘new mysqli’ statement.
Now that you have XAMPP installed and your database created, you can connect to the database using PHP. Use the following code as an example:
<?php
$servername = "localhost"; // server name, usually 'localhost'
$username = "root"; // default username for XAMPP is 'root'
$password = ""; // default password is blank
$dbname = "your_database_name"; // the name of the database you created, for example my_test_db
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
The above script is fundamental for connecting to a MySQL database using PHP.
To connect to your database, you need to put in your PHP script the following information:
– server name: usually the server name is ‘localhost’
– username: the default username for XAMPP is ‘root’
– password: the default password is blank (empty space in quotes)
– database name: the name of the database, in our example: my_test_db
All this data can be assigned to variables, as in the example: $servername, $username, $password, $dbname.
Instead of setting the host, the username, the password, and the database name as variable values first, you can set them directly as the value of the $conn variable, like this:
$conn = new mysqli(“localhost”, “root”, “”, “msg_db”);
Remember to replace “your_database_name” with the name of the database you created in PHPMyAdmin (in our example: my_test_db).
You should remember that the connection to the MySQL database is made using the ‘new mysqli’ command, and the syntax of the construct is:
new mysqli (server_name, user_name, password, database_name);.
The contents of the construct are assigned as the value of the $conn variable.
mysqli stands for “MySQL Improved. It’s an improved version of the older mysql extension, offering additional features and support for the newer MySQL server versions. Just remember that mysqli is a PHP extension that is used to establish a connection to a MySQL server.
Before using mysqli, as already explained, you must provide connection details such as the server name, username, password, and the name of the database you want to connect to.
Once connected, you can use the $conn object to execute SQL queries on the database. This allows you to perform operations like selecting data, inserting records, updating data, and deleting records.
Also, mysqli provides mechanisms to handle errors that might occur during database operations. In the code, the ‘$conn->connect_error’ property is checked to see if there’s an error in establishing the initial connection. This way the script checks if the connection was successful. If there’s an error, the script will terminate, and the error message will be displayed using die(). In PHP, die() is a function that terminates the execution of a script and prints a message. It’s often used for handling critical errors or situations where further execution is not possible or meaningful. When die() is called, the script execution stops immediately. Any code that comes after the die() statement will not be executed.
If the connection is successful, the echo line will be executed, and the message “Connected successfully” will be printed to the screen.
Now put the above code in a page test.php that should be located in a htdocs folder (C:\xampp\htdocs).
Open your web browser, type “http://localhost/test.php” in the address bar and press Enter.
If everything is OK, you should see the message “Connected successfully” in your test.php page.
So you have established a basic connection to your MySQL database using XAMPP.
How to delete MySQL table and database?
Delete table
Open PHPMyAdmin and click the “Operations” button from the top menu.
Then click the “Delete the table (DROP)” link from the “Delete data or table” section in the bottom of the page.
You will see the message “You are about to DESTROY a complete table! Do you really want to execute “DROP TABLE your_database_name . your_table_name”?”. Click “OK”. Now your table is deleted.
Delete database
Open PHPMyAdmin and click the “Operations” button from the top menu.
Then click the “Drop the database (DROP)” link from the “Remove database” section.
You will see the message “You are about to DESTROY a complete database! Do you really want to execute “DROP DATABASE your_database_name”?”. Click “OK”. Now your database is deleted. If you open your test.php page you will see an error message.