Best MySQL Tutorial For Beginners in 7 Days
The MySQL Tutorial for Beginners is a course primarily designed for those who wanted to learn MySQL Tutorial in PHP for a short period of time. In this MySQL Tutorial for Beginners, we will be using MySQL Tutorial with PHP to manipulate MySQL Database.
MySQL in PHP Tutorial
MySQL is an open-source database server and is the most popular nowadays. On the other hand, it is paired with PHP scripts to build powerful and dynamic server-side applications.
Take Note: Before you begin with this MySQL in PHP Tutorial make sure that you have basic knowledge about PHP and HTML to avoid confusion.
What is a Database?
A database is a structure wherein you can store all information that can be easily accessed, manage and update. It is composed of two types: a flat database and a relational database.
Flat File Database VS Relational Database
- A Flat File database is stored on hard drives just like a text file.
- A Relational database is a structure that recognizes relations among stored items of information. MySQL is a relational database.
What is the use of Database?
When it comes to storing information, databases are most useful. For instance, when you wanted to store information about all the students in a school.
With the use of the database, you can group different parts of the student’s information into separate tables to help store your information in a logical way.
An example of these tables might be Students, Teachers, and Subjects. Each of these tables contains columns specific to these three modules.
To help store information related to each student, the Students table might have the following columns: Student id, Name, Address, Age, Gender, Subject, and Teacher.
What is the Purpose of MySQL Tutorial With PHP?
The purpose of this MySQL Tutorial with PHP is to teach those who want to learn MySQL Tutorial in PHP environment to develop their skills in web development.
MySQL setup guide
Purchasing some space on a shared web host is the easiest way to experiment with MySQL and PHP. However, you can set up MySQL manually on your personal computer at home.
Beginners would find this difficult to do and it requires more than a few lessons!
So if you’re ready and you think that you got what it takes, go over to MySQL.com for more information on installing MySQL by yourself.
MySQL Setup in Panel
The Shared hosting provider has different types of control panels. Since CPanel is the most popular hosting provider, this tutorial assumes that you are using it.
You can click here if you are looking for some alternative to CPanel.
The first thing that you need to do, find the link that would allow you to administer MySQL.
In this CPanel, the icon is named MySQL Databases. Once you get there, do the following before you can start using MySQL.
Setup MySQL Database in cPanel
- Create a new database
- Create a new user with password
- Assign the user to the database
Helpful tool – PHPMyAdmin!
PHPMyAdmin is an open-source and free hosting provider that you can install anywhere you want.
Note: In this tutorial, we will be using XAMPP.
This allows you to view all the MySQL databases, tables, entries, and you can also perform SQL queries through a web browser.
Even though in this MySQL Tutorial For Beginners, you will learn how to create MySQL Databases, Tables, and all other MySQL tasks through PHP, we still encourage you to know more about phpMyAdmin.
It’s a user-friendly interface because this allows you to do a lot of common MySQL tasks easily and quickly.
MySQL Admin Commands
In this section, we will learn about the MySQL admin commands and the different options that are available in administering your MySQL service using xampp after it is successfully installed.
MySQL Admin using Command Line
The command-line interface is commonly used by an old-school programmer to perform MySQL queries.
If you have already installed MySQL on your Microsoft Windows operating system you can open a command line by clicking the Start Menu and launch Window’s command-line interface by typing the keyword “cmd” into the text field and press Enter on the keyboard.
See the image below using MySQL Command-Line on how to perform MySQL Create Query Table.
MySQL Graphical User Interface(GUI)
There are so many free MySQL administration tools that are available and many developers prefer these free Graphical User Interfaces( GUI ) on the command line.
These are the most popularly used: phpMyAdmin, MySQL Administrator and Navicat. See the image a sample on how to create a MySQL table using PHPMyAdmin.
MySQL Syntax
MySQL “code” can be easily read unlike the hardest programming languages such as C, C# or C++.
There are very few special characters and symbols that are required in creating a MySQL query.
Most of the queries are composed entirely of English words.
Example of MySQL Syntax
SELECT * FROM TABLENAME WHERE FIELD1 = "TEST";MySQL Tutorial For Beginners Strategies
The MySQL language is not as complicated as other programming languages. On the other hand, this tutorial focuses on the combination of MySQL Tutorial in PHP for beginners with examples.
Most examples are ready to copy, paste, and run on your web server. Sounds good, right?
MySQL Queries Capitalization
Capitalizing words in MySQL queries are a good programming habit because it stands out from the rest of the code, in that way, the queries are much easier to read.
This is an example of the MySQL query that retrieves all the information from the table (“table”) using PHP.
$RESULT = MYSQL_QUERY(“SELECT * FROM TABLE”)The line of code that is used is valid in PHP, yet it also has a valid MySQL.
SELECT * FROM example is the MySQL code.
The SELECT and FROM are the MySQL keywords used in this query.
You can also view complete MySQL keywords in here, but you have nothing to worry about memorizing them because you could program in MySQL for years without using all of these MySQL keywords.
Reminders from the Author
Note: Don’t rush things when learning MySQL Tutorial with PHP, the best way that you have to do is take a lesson at a time.
The slower you progress, the more you will be well-informed about this MySQL Tutorial with the PHP and database system.
What is MySQL Database
MySQL database is used in organizing a group of tables. For instance, when you create different tables that shared a common theme you need to group them into one database to manage it easily.
How to Create Database in MySQL using PHP Code
Creating a database directly through a PHP script does not allow by most web hosts but they require to use the PHP/MySQL administration tools on the web host control panel to create databases.
To Create a Database in MySQL using PHP Code.
Step 1. Create a PHP File and Name it as “phpconnect.php”.
Step 2. Add the following PHP code and Save
<?php
$server = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = new mysqli($server, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Creating a database named mysqltuts
$sql = "CREATE DATABASE mysqltuts";
if ($conn->query($sql) === TRUE) {
echo "a mysql database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
// closing connection
$conn->close();
?> Step 3. Test the PHP Code using your web browser.
Step 4. Check your PHPMyAdmin and you will see there the newly created database.
MySQL Database Example:
- Server – localhost
- Database – testdb
- Table – table
- Username – useradmin
- Password – passadmin
Note: In the advanced tutorial, the table may change but everything else will remain the same.
Set the server name(localhost) to a server because all of our scripts are going to be placed on the server where MySQL is located.
Correct the URL, if the MySQL server was running on a different machine.
MySQL Connect
Since PHP 5 or later can now work with MySQL database the MySQLi or PDO_MySQL extension should be used. The functions include:
- Mysqli_connect()
- PDO::_construct()
Which is better MySQLi or PDO?
Well the answer depends on you, but both MySQLi and PDO have their advantages:
The MySQLi can only work with MySQL databases unlike the PDO will work on 12 different Database systems.
Meaning if you want to switch your project to use, the PDO makes the process easy, because you are no longer required to rewrite all the codes but you have only to change the connection string and few queries unlike to MySQLi, you have to recode or rewrite the entire code including the queries.
PDO and MySQLi are Object-oriented, only MySQLi offers a procedural API.
Here are the examples of MySQL in Both MySQLi and PDO
Reminder:
Before connecting to MySQL and access the data, make sure that the server is open.
Object-Oriented Example of MySQLi
<?php
$server = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = new mysqli($server, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connecting to database failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>Closing Example of the MySQLi (Object-Oriented)
$conn->close();Procedural Example of MySQLi
<?php
$server = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = mysqli_connect($server, $username, $password);
// Check connection
if (!$conn) {
die("Connecting to database failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>Closing Example of MySQLi Procedural
mysqli_close($conn);PHP PDO Example
<?php
$server = "localhost";
$username = "root";
$password = "";
try {
$conn = new PDO("mysql:host=$server;dbname=test", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>An example of Closing the PDO:
$conn = null;In Summary
In this course, MySQL Tutorial For Beginners in 7 days with examples. We discussed, what is Database and the use of the database, as well as the purpose of this MySQL Tutorial with PHP.
We have understood that this MySQL Tutorial For beginners with examples is intended for those individuals who want to learn MySQL tutorial in PHP.
Again this is only Part 1 of our 7 days training, our next part will be a little bit more interesting for I will focus the discussion on How to Create Database tables in different ways.
Frequently Asked Questions
The MySQL Tutorial for Beginners is a course primarily designed for those who wanted to learn MySQL Tutorial in PHP for a short period of time.
MySQL is an open-source database server and is the most popular nowadays. On the other hand, it is paired with PHP scripts to build powerful and dynamic server-side applications.
The purpose of this MySQL Tutorial with PHP is to teach those who want to learn MySQL Tutorial in PHP environment to develop their skills in web development.
A database is a structure wherein you can store all information that can be easily accessed, manage and update. It is composed of two types: a flat database and a relational database.
MySQL database is used in organizing a group of tables. For instance, when you create different tables that shared a common theme you need to group them into one database to manage it easily.
Since MySQL is open-source, you can download it for free.
Its better to use mySQLi because i have knowledge in mySQL database than PDO. But based on the definition i think PDO is more practical since it runs in 12 different databases.
Good Day sir! As a beginner this tutorial is so helpful to us as a student because it helps us to find some things that can make our work easier. Well, Both PDO and MYSQLi have their own advantages as you said in this tutorial but both of them has also its own function that greatly affect our work and for me PDO is the best way to use. Why PDO? because it can switch immediately into the next pages unlike MYSQLi that it only stick into one database. So, PDO has its own capability to help us easily as a student and also as a beginner in this area. So thankyou for doing this kind of page that really satisfy us! Continue doing pages that can help everyone. Thankyousomuch and Godbless!
Which is better to use PDO or MySQLi?
Both PDO and MySQLi have their advantages as stated above. For a mean time, as a beginner I prepare to use a MySQLi because I have a little knowlegde about it and it can only work with MySQL databases unlike the PDO will work on 12 different database system. Although PDO make a process more efficient unlike MySQLi.
Hopefully I can explore about PDO, to makes the process easy.
Which is better to use PDO or MySQLi?
Both PDO and MySQLi have their advantages as stated above. For a mean time, as a beginner I prepare to use a MySQLi because I have a little knowledge about it and it can only work with MySQL databases unlike the PDO will work on 12 different database system. Although PDO make a process more efficient unlike MySQLi.
Hopefully I can explore about PDO, to makes the process easy.
Good evening Sir! Upon reading your MySQL tutorial I learned the PHP code on how to create a database in MySQL and on how to connect the PHP to a MySQL database. But aside on that we can also use PDO on how to connect to database. I have also read that PDO’s core advantage over MySQLi is its database driver support. PDO supports 12 different drivers opposed to MySQLi, which supports MySQL ONLY. As a beginner, I would choose MySQLi over PDO, it is because I am already familiar with MySQLi. I don’t have any experiences using PDO but I am currently imterested on how it works. Thank you for this tutorial. God Bless.
This lesson help me how to familiarize mysql database this article also show me the difference between mysqli and pdo queries i also familiarise some function of mysql data base thanks to this article
Good day sir! I choose PDO, it is more advantageous than mySQLi because this database system only runs in MySQL while PDO can run on 12 different databases. And this will prevent us from rewriting the codes when transferring database .
Good day sir! In refer to the question above which is better to use MySQLi or PDO, for me I choose PDO because base on the definition and the sample code that I perform recently it is more convenient to use PDO it is easy to learn and the scope of PDO is more broader compare to MySQLi.
It is better to use PDO because i want expand my learnings and its design goal is to create a lightweight consistent interface for accessing databases in PHP.
Good day sir, in refers to the question above which is better to use MySQLi or PDO, my opinion is that PDO should be used by default, especially as a beginner, due to its versatility, general predictability and useful fetch modes. Ultimately, PDO wins this battle with ease, with support for twelve different database drivers.
For me as a beginner, I will choose MySQLi because it is more easy to understand because it offers procedural API.
Which better to use PDO or MySQLi? Both of them are better to used and they have own advantages. As a beginner I will choose to use PDO is a general databases abstraction layer with support for MySQL among many other database. It is a well-design API and will let you more easily move to another database if necessary
Hello Sir, good pm!
If I have to choose between MySQLi and PDO? I’d rather choose PDO because as the article states, PDO can work into 12 different database systems. In using PDO, it can make our own work much easier. As you said, using MySQLi will be a hard battle for us, since, we still have to recode the entire codes of our work. So, as a beginner I’ll choose which I think can make my work efficient. On the other hand PDO has also a bloody process but atleast it has advantages. But both are heplful, both are difficult and both are satisfying. Thank you Sir!
Good evening, Sir!
If I have to choose what’s better to use between MySQLi and PDO. For me now, as a beginner I will choose MySQLi, because I have some knowledge about it and it’s easy to understand. PDO on the other hand can give us advantages because we can use it in 12 different database system and it’s easy for us to switch database if we want and we are no longer to write all of our codes in it we have just to change some connection string and some queries. But I saw that some of the terms/language used in PDO are hard to understand, But if have some enough knowledge in using PDO I will use it always. That’s all, thanks you for this tutorial Sir. Godbless.
Goodevening sir. As For me, i prefer to choose PDO rather than MySQL. Because just as stated above, MySQL only works on database which is MySQL Database unlike PDO that can works on 12 databases. So as a beginner it is suitable to choose the easiest way for me to increase my knowledge little by little, and that is PDO. Thank You.
As I read your tutorials from the beginners for me, I preffered to choose MYSQLi it’s because I already tried using MYSQLi and it’s easy to use rather than PDO.
Comparing the two databases, for me I would likely to choose MYSQLi because I already had the knowledge on how it works rather than PDO. As explained above, PDO is capable in supporting multiple drivers unlike MYSQLi but as a beginner like me it is better to start learning MYSQLi.
Thanks for this tutorial it is very helpfull