Acquaintance with MySQL: functions, setup 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, goods, customer mail addresses. 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 look at the basic functions of MySQL and learn how to perform simple database queries.

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 to your working machine. If you have your own host, then the database is already installed on the web server. Each 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 can 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.

Creating the first database

After installing XAMPP, type localhost / dashboard / at the browser prompt. A welcome letter opens with the 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 for creating a new one. Click it and enter any name, let it be myFirstDB. To the right of the name field is a drop-down list for selecting the encoding in which you need to find utf8_general_ci.

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 SQL, and type the following.

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Now you have a table in which users can enter their name and mailing address. The user_id column will be automatically populated and incremented by one in each new row. You can access information from phpMySQL, but it is easier to do this directly from php scripts using the Mysqli functions.

Connecting to a database from scripts

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

First you need to connect to the database, for this you need the host name, user, database and password to enter.

 <?php DEFINE("DATABASE_HOST", "localhost"); DEFINE("DATABASE_USER_NAME","root"); DEFINE("DATABASE_PASSWORD",""); DEFINE("DATABASE_NAME","myFirstDB"); ?>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

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

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Entering data in a table

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

 <form action="create_user.php" method="POST" id="signup_form" <label for="username"> :</label> <input type="text" name="username" size="20" class="required" /> <label for="email">E-Mail Address:</label> <input type="email" name="email" size="50" /> <input type="submit" value="Join" /> <input type="reset" value="Restart" /> </form>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The form submits data to the create_user.php script.

 <?php require_once 'scripts/config.php'; //    $username = htmlspecialchars(trim($_REQUEST['username'])); $email = htmlspecialchars(trim($_REQUEST['email'])); //        MySQL $insert_sql = "INSERT INTO users(username, email) VALUES(?,?)"; //   $stmt = $mysqli->prepare($insert_sql) or die("    "); $stmt->bind_param('ss', $username, $email) or die("     "); $stmt->execute() or die("    "); $stmt->close(); ?>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

On the top line, the script asks for the config.php file with the database connection. Using the variables $ username and $ email, it collects the lines entered by the user and, using the Mysqli functions, puts them into the table. Check that the script is working, and then open phpMyAdmin and see how your users table has changed.