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.
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.
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.
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.
