Modern computers allow you to process a large amount of a wide variety of information. At the same time, it is very important for the user that the information is presented in a systematic, convenient form.
This requires the creation of a database in mysql .
Purpose and capabilities of the mysql DBMS
used to structure various data. most popular system mysql database management. With the help of the created mysql databases you can comfortably process information (add, delete, sort, change) stored in the computer's memory.
Mysql is a relational database, that is, it allows you to process and present data in the most human-friendly form - tables.
Tables allow, when processing information, to increase:
- efficiency;
- flexibility;
- performance.
The created database in mysql is characterized by:
- processing speed and support for large amounts of data;
- simplicity and reliability in use;
- the presence of a rich set of useful properties;
- support for almost all operating systems.
The mysql DBMS is effectively used in a wide variety of industries and areas.
How to create a mysql database
For self-creation mysql database on the host you need:
Connecting database to mysql server
Let's see how to connect mysql database?
MySQL is a multi-user software product that can be used by multiple users at the same time.
The DBMS provides a system that delimits access to each user. Identification is carried out using a login (name) and password.
In order to connect the mysql database to the server, you must:
- Specify your login (username);
- Enter password;
- Define hostname (hostname) and port.
The last parameters, if necessary, can be clarified with the administrator.
How to connect mysql to php?
The php software package, which allows you to create scripts (scripts), is located on the server.
To connect mysql to php, you need to know four attributes:
- database username;
- the name of the database itself;
- hostname;
- user password.
1. You need to create a file of the future script in the html editor:
Thanks to the “ mysql_connect" function, a connection to the server is provided, and using the " mysql_select_db" function, the required database is selected.
If functions fail using the "mysql_error()" statement, the reason for the error will be displayed.
Steps to connect delphi to mysql
To connect the Delphi application development shell to the database, the following programs must be installed and configured:
- MySQL server
- database with necessary tables;
- MySQL Connector ODBC.
Connecting delphi to mysql starts with creating a new Delphi project and placing the TADOConnection component on the form.
Database connection mysql is created using the mysql_connect() function. Variables with which the connection is established are indicated in brackets.
What exactly?
1. $location - indicates the server on which the script is located. In most cases this is localhost.
2. $user - in this variable we prescribe the name of the database user
3. $password - database user password
After connecting to the database, be sure to select a database name. The mysql_select_db() function is used for this. We write two parameters in brackets:
1. $dbname - specify the name of your database in this variable. The name can be anything. We write everything, of course, in English.
2. $connect - database connection handle. In case of an unsuccessful connection to the database, the variable takes the argument false
The database connection code is as follows:
No database connection
"); exit(); ) ?>
You can write this code directly inside any file where you work with the database. But in most cases they create separate file where this code is written. In the same place, you can specify all variables and constants to specify general settings the entire application.
How to create connection to phpmyadmin database
Most often, any web programmer starts such an operation on a local server. Because at first any new web application or site is written and edited on a regular home computer. After the programmer checks all the systems and makes sure that everything works reliably and smoothly. Only after that everything is uploaded to a remote server.
Connection occurs, as described above, using the join functions and the select function Database. Only a small difference. If you do everything local computer database username, in most cases root. There is no password or we prescribe the simplest one, two, three.
Why complicate the whole system for yourself?
phpmyadmin this is a special web interface for managing all databases located on your local server. Since it is extremely inconvenient to manage the base through the console.
Create a database connection to the site in php
Now let's move on to the most important work of transferring our site or application to a remote server. Now you should keep in mind that for the normal operation of your site you will need paid hosting with support for PHP 5 and higher, MySql must be with the Phpmyadmin interface and the entire interface file manager, to manage your site's files.
When buying hosting, you must be given an information letter where all the parameters of your server will be indicated. And for database connection to your site, you create your own database, prescribe a name and password.
On many hostings, everything happens in different ways. Or you yourself prescribe a login, password or password, the login is set automatically when the database is created.
The connection code is the following:
No database connection"); exit(); ) if (! @mysql_select_db($dbname,$connect)) ( echo("
No database connection
"); exit(); ) ?>
As you can see, nothing complicated. You just took the same file and just changed a few variables, that's all. Just remember one rule when moving a site to a remote server, you must change three variables in the configuration file, namely:
1. $dbname = "base"; // database name
2. $user = "yourlogin"; // database username
3. $password = "123456789"; // database user password
Once you have installed the MySQL server, you can only connect to it from the same machine. This was done for system security purposes, since in most cases MySQL is used on the Web, and there in rare cases the web server and database server are smashed.
In the corporate segment, it is more correct to use 1 server for all similar tasks. It's silly to keep MySQL with one database on every server where it might be needed. Therefore, the database server must be one, which has the required number of databases for different tasks.
By the way, in the Web, the database server and the web server are separated only in cases where the capacity of one server is not enough. A dedicated server is rented and the database is hosted on it. I would like to clarify that it is reasonable to do this only within one DC.
Let's connect to MySQL remotely
In the configuration file of MySQL itself there is a parameter bind address, which is responsible for this, so:
- open the configuration file for editing:
sudo nano /etc/mysql/my.cnf - comment the line:
# bind-address = 127.0.0.1 - restart MySQL:
sudo service mysql restart
Commenting out this line is equivalent to assigning an IP address 0.0.0.0 , that is, allow everyone to connect. You cannot specify 2 IP addresses in this line. If you need to limit the connection to the MySQL server, you need to use the capabilities of iptables.
Now you can connect to the database server through a telnet client, but in order to go inside you need Account, which is allowed to connect from other than localhost. To do this, you need to connect to MySQL from the server itself and create a user like this:
MySQL -u root -p
And create a user with the command:
GRANT ALL PRIVILEGES ON *.* TO "new_user"@"remote_address" IDENTIFIED BY "password" WITH GRANT OPTION;
This line creates a user with unlimited rights, which is not entirely correct to do. It is more correct to limit the access of the user who connects remotely as much as possible. So the command might look like this:
GRANT SELECT,INSERT,DELETE,UPDATE,CREATE,ALTER ON "base_name".* TO "new_user"@"remote_address" IDENTIFIED BY "password" WITH GRANT OPTION;
With this command, we limited the user's authority to only one database and actions with it.
A complete list of all rights that can be given to a user:
- ALL PRIVILEGES– all rights to the specified object, except for assigning rights to this object;
- CREATE– the right to create a table;
- ALTER– the right to change the table;
- DROP– the right to destroy the table;
- LOCK TABLES– the right to lock the table;
- CREATE TEMPORARY TABLES– the right to create temporary tables;
- CREATE ROUTINE– the right to create stored procedures and functions;
- ALTER ROUTINE– the right to change or destroy stored procedures and functions;
- CREATE VIEW- the right to create a presentation;
- TRIGGER– the right to create and destroy triggers;
- INDEX– rights to create and destroy indexes;
- EXECUTE– the right to execute stored procedures and functions;
- EVENT– the right to create events;
- CREATE USER– the rights to create, destroy, rename the user and remove all rights. Assigned only at the global level;
- SELECT- the right to choose;
- DELETE– the right to erasure;
- INSERT- the right to insert;
- UPDATE- the right to update;
- FILE– the right to use the SELECT ... INTO OUTFILE and LOAD DATA INFILE commands;
- PROCESS– the right to view all processes with the SHOW PROCESSLIST command;
- SHOW DATABASES– the right to view the list of schemes;
- SHOW VIEW– the right to view the list of views;
- SHUTDOWN- the right to close.
This document describes how to set up a MySQL database connection from the NetBeans IDE. Once connected with MySQL, you can start working in the IDE's database explorer, creating new databases and tables, populating the tables with data, and making the structure and contents of the databases available to SQL queries. This tutorial is designed for beginners with a basic understanding of database management who need to apply their knowledge to working with MySQL in the NetBeans IDE.
Setting MySQL Server Properties
NetBeans IDE comes with included support for MySQL RDBMS. Before you can access the MySQL database server in NetBeans IDE, you must set the MySQL server properties.
Starting the MySQL Server
Before attempting to connect to the MySQL database server, you must ensure that it is running on your computer. If the database server is not connected, you will see (disconnected) next to the username in the MySQL Server node in the "Service" window and you won't be able to expand the node.
To connect to the database server, ensure that the MySQL database server is running on the machine, right-click Databases > MySQL Server node in the Services window, and select Connect. You may be prompted to enter a password to connect to the server.
Once the server is connected, you will be able to expand the MySQL Server node and view all available MySQL databases.
Create a database instance and connect to it
The SQL editor is a widely used way to interact with databases. NetBeans IDE provides a built-in SQL editor for this. The SQL editor is usually accessed using the "Run command" option from context menu connection node (or child nodes of the connection node). After establishing a connection to the MySQL server, you can create a new database instance in the SQL editor. To continue with this tutorial, create an instance named MyNewDatabase:
Creating database tables
Once you have established a connection to the MyNewDatabase database, you can begin learning how to create tables, populate them with data, and modify data in tables. This provides users with a deeper understanding of the database explorer features, as well as NetBeans IDE's support for SQL files.
The database MyNewDatabase is currently empty. In the IDE, a database table can be added using the Create Table dialog or by typing SQL query and run it directly from the SQL editor. Both methods can be used.
Using the SQL Editor
Using the Create Table Dialog Box
Working with data in a table
You can use the SQL editor in NetBeans IDE to work with tabular data. By executing SQL queries on a database, you can add, modify, and delete data in database structures. For adding new record(rows) to the Counselor table, follow the steps below.
Executing the SQL script
Another way to manage table data in NetBeans IDE is to run an external SQL script directly in the IDE. If the SQL script was created in a different location, you can simply open it in the NetBeans IDE and run it in the SQL editor.
For clarity, download the file and save it on your computer. This script is designed to create two tables similar to the tables we just created (Counselor and Subject) and immediately populate them with data.
Since this script overwrites already existing tables, remove Counselor and Subject to fix the table creation process when the script is run. Deleting tables
- Right-click the Counselor and Subject table nodes in the database explorer, then select Delete.
- Click the Yes button on the Confirm Object Deletion dialog box. Note that the dialog box lists the tables that will be deleted.
When you click Yes on the Confirm Object Deletion dialog box, the table nodes are automatically deleted from the Database Explorer.
Executing SQL Script in MyNewDatabase Database
additional information
This is the final section of the Connecting to a MySQL Database tutorial. This document demonstrates setting up MySQL on a user's machine and setting up a connection to a database server from the NetBeans IDE. You also learned how to work with MySQL in the IDE's Database Explorer to instantiate databases and tables, populate them with data, and run SQL queries.
More detailed training courses are available at the following resources:
- Building a simple web application using a MySQL database. This tutorial provides information on how to create simple two-tier web applications in the IDE using a generated MySQL database.
To get the most out of your MySQL database, it's important to understand how to connect from your PHP user program to a MySQL database.
This tutorial describes the following three methods along with a corresponding PHP example program that will explain how to connect using PHP to a database.
- Connecting with the Mysqli extension (recommended)
- Connecting with PDO (recommended)
- Connecting with legacy mysql_ functions (deprecated)
To do this, you need to install the PHP-MySQL package.
Based on the RedHat distribution including , use yum to PHP-MySQL installations as shown below.
Yum install php-mysql
Depending on your system, above we will install or update the following dependencies:
- php-cli
- php common
- php pdo
- php-pgsql
Once everything is set, the phpinfo page will display the MySQL module as shown below:
For all of the examples below, we will be connecting to a MySQL database that already exists. If you are new to MySQL, this is a good place to start: .
Note: Everything described here will also work with MariaDB as it does with MySQL.
1. Connecting in PHP using the Mysqli extension
MySQLi stands for MySQL Improved.
Note that on most distributions (eg: CentOS), PHP-MySQLi is already part of the PHP-MySQL package. This way you don't have to search and install the PHP-MySQLi package. All you have to do is install the PHP-MySQL package to get a working Mysqli extension on your system.
Create the following mysqli.php file in Apache's DocumentRoot:
connect_error) ( die("Error: unable to connect: " . $conn->connect_error); ) echo "Connecting to database.
"; $result = $conn->query("SELECT name FROM employee"); echo "Number of rows: $result->num_rows"; $result->close(); $conn->close(); ?>
In the above:
- MySQLi - This function will initiate a new connection using the Mysqli extension. This function will take four arguments:
- Hostname where the MySQL database is running
- MySQL connection username
- Password for mysql user
- MySQL database to connect to.
- Request function - Use it to specify your MySQL query. In this example, we select the name column from the employee database.
- Finally, we display the number of rows selected using the num_rows variable. We also close the connection as shown above.
Database connection. Number of lines: 4
Note: If you are trying to connect to a remote MySQL database, then you can do this to avoid the host connection denied error: How to allow a MySQL client to connect to a remote MySQL server.
2. Connecting with PHP to MySQL with PDO extension
PDO stands for PHP Data Objects.
PDO_MYSQL implements the PDO interface provided by PHP for connecting a program to a MySQL database.
Most Linux distributions(eg CentOS and RedHat), the PHP-PDO package is already included in the PHP-MySQL package. This way you don't have to search and install the PHP-PDO package. All you have to do is install the PHP-MySQL package to get the PDO_MYSQL PHP extension working on your system.
Create the following MySQL-pdo.php file in your Apache DocumentRoot:
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connecting to a database.
"; $sql = "SELECT name FROM employee"; print "Employee name:
"; foreach ($conn->query($sql) as $row) ( print $row["name"] . "
"; ) $conn = null; ) catch(PDOException $err) ( echo "Error: unable to connect: " . $err->getMessage(); ) ?>
In the above:
- new PDO - Creates a new PDO object that will take the following three arguments:
- MySQL connection string: will be in the format “mysql:host=$hostname;dbname=$dbname”. In the example above, the DB is running on localhost and we connect to the andreyex database.
- Username to connect to MySQL.
- Password for the mysql user.
- $sql variable - Create the SQL query you want to execute. In this example, we select the name column from the employee table.
- query($sql) - Here we execute the SQL query we just created.
- foreach - this is where we run through the above commands and store them in a variable string $ and then we display them with the print command.
- In MySQL PDO, to close a connection, simply reset the value of the $conn variable.
When calling mysqli.php from your browser, you will see the following output, indicating that PHP was able to connect to the MySQL database and fetch the data.
Database connection. Employee Name: Siteslan Maria Oleg
3. PHP connection using mysql_ functions (deprecated)
Use this method only if you are using more old version PHP and can't update it to new version for some reason.
This is a deprecated PHP 5.5 extension. But as of PHP 7.0 version, this won't work as it has been removed.
Since PHP 5.5, when you use these functions, they will generate an E_DEPRECATED error.
Create the following MySQL-legacy.php file under Apache DocumentRoot:
"; $result = mysql_query("SELECT name FROM employee"); $row = mysql_fetch_row($result); echo "Employee 1: ", $row, "
\n"; mysql_close($conn); ?>
In the above:
- The mysql_connect function takes three arguments: 1) the hostname where the MySQL database is running, 2) the username to connect to MySQL, 3) the password for the MySQL user. Here we connect to the MySQL database that is running on the local server using the root username and password.
- mysql_select_db function - As the name suggests, will select the database you want to connect to. This is equivalent to the "use" command. In this example, we are connecting to the andreyex database.
- mysql_query Function - Use this to specify your MySQL query. In this example, we select the name column from the employee database.
- mysql_fetch_row - Use this function to fetch rows from the SQL query we just created.
- Finally close the connection with the mysql_close command as shown above.
When calling MySQL-legacy.php from your browser, you will see the following output, indicating that PHP was able to connect to the MySQL database and fetch the data.
Database connection. Employee 1: AndreyEx
