Introducing MySQL: features, configuration and connection

Table of contents:

Introducing MySQL: features, configuration and connection
Introducing MySQL: features, configuration and connection

What does the website include? Mandatory set of HTML pages with markup, text, headings; CSS tables for beauty and style. To make the resource interactive, designers create javascript and php scripts. And also any full-fledged application needs data - lists of users, products, mailing addresses of customers. Of course, they can be stored in separate files. But it is much safer and more convenient to use a database. The most popular today is MySQL. Let's understand the basic functions of MySQL and learn how to perform simple database queries.

Basic Functions of MySQL
Basic Functions of MySQL

Software setup

MySQL is a program that runs on a computer. Therefore, in order to have something to work with, you must first download MySQL on your work machine. If you have your own host, then the database is already installed on the web server. Every hosting provider provides this service, so just go to phpMyAdmin.

To download and configure MySQL on a personal computer, download and install a local server. It could be Denver, OpenServer or XAMPP(LAMPP for Linux). In this article, all examples are created on XAMPP. The working directory is in C:/xampp/htdocs. For convenience, create a separate directory with the name of the site, in which there will be a separate scripts folder for php files.

MySQL setup
MySQL setup

Creating the first database

After installing XAMPP, type localhost/dashboard/ in your browser command line. A welcome email will open with a navigation bar at the top. Click on phpMySQL and follow the link. You will be taken to a special web application that provides access to MySQL. The left column contains a list of existing databases and a button to create a new one. Press it and enter any name, let it be myFirstDB. To the right of the field for the name is a drop-down list for selecting the encoding, in which you need to find utf8_general_ci.

A MySQL function to store information and provide access to it. All data is in the form of tables consisting of rows and columns. You can create such a table on the command line or through the phpMySQL interface. To do this, select myFirstDB, click the button at the top of the SQL and type the following.

CREATE TABLE users(user_id int AUTO_INCREMENT PRIMARY KEY, username varchar(50), email varchar(100));

You now have a table where users can enter their name and postal address. The user_id column will be filled in automatically and incremented by one on each new row. You can access information from phpMySQL, but it's easier to do it directly from php scripts using Mysqli functions.

php mysqli functions to connect to db
php mysqli functions to connect to db

Connecting to a database from scripts

In your working directory, in the scripts folder, create a config.php document. Here you will store configuration settings, php functions of Mysqli to access the database. You will later connect this file to work with MySQL from other scripts.

First you need to connect to the database, for this you will need the hostname, username, database and login password.

Using the Mysqli function, we will connect to the previously created myFirstDB.

//for object oriented style $mysqli=new mysqli(DATABASE_HOST, DATABASE_USER_NAME, DATABASE_PASSWORD, DATABASE_NAME) or die("Can't connect to MySQL: ".mysqli_connect_error()); //procedural $link=mysqli_connect(DATABASE_HOST, DATABASE_USER_NAME, DATABASE_PASSWORD, DATABASE_NAME) or die("Can't connect to MySQL: ".mysqli_connect_error());

Entering data into a table

In order not to enter data manually every time, you need a script that will receive information from a web form and automatically write it to a table. Create a simple form with fields matching the column names users.

<form action="create_user.php" method="POST" Username: E-Mail Address:

Form submits data to create_user.php script.

prepare($insert_sql) or die("Error creating prepared query"); $stmt->bind_param('ss', $username, $email) or die("Error creatingprepared request parameters"); $stmt->execute() or die("Error sending prepared request"); $stmt->close(); ?>

On the top line, the script requests a config.php file with a database connection. Using the $username and $email variables, collects the strings entered by the user and, using Mysqli functions, enters them into a table. Test the script and then open phpMyAdmin and see how your users table has changed.

Popular topic

Editor's choice

  • What is a proxy server and what is it for?
    What is a proxy server and what is it for?

    The word "proxy" has ever been heard by any of us, but not everyone knows what a proxy server is and what its purpose is

  • Google Chrome not working. What to do?
    Google Chrome not working. What to do?

    So why doesn't Chrome launch when we click on its desktop icon? Google Chrome not working for several reasons

  • Recovery console. Main benefits of using
    Recovery console. Main benefits of using

    Computers are quite technically complex devices. In this regard, many users from time to time encounter failures in the operation of the operating system and other programs. In most cases, you can fix problems in a matter of minutes, especially if the computer technician has the necessary skills. If a critical failure occurs, it is recommended to use the recovery console

  • What is the cluster size?
    What is the cluster size?

    The space of any storage medium (hard drive or flash drive) is not a whole piece, but a system of memory cells called clusters

  • What is defragmentation and why is it needed?
    What is defragmentation and why is it needed?

    Modern users are often spoiled by powerful computers and inexpensive components to such an extent that they do not even know the basic concepts. That is why they often find themselves in a situation where the car begins to shamelessly “slow down” and respond with extreme reluctance to any commands. As a rule, “evil viruses” that have entered the computer are blamed for everything, but sometimes the reality turns out to be much more prosaic