Creating a MySQL Server Database Connection with PHP and Apache on FreeBSD

PHP has several functions to connect and interact with databases. MySql Server. The most common and frequently used by a system administrator to make requests from PHP to the MySQL Server database are:
  • mysqli_connect: Used to create a connection to the MySql database server.
  • mysqli_query: Used to submit SQL queries to the MySQL database.
  • mysqli_close: Used to close database connections.
In its work mysqli_connect must receive three parameters to be able to connect to the MySQL database: server (host) address, user name and user password. These 3 requirements come from the MySQL server, whether on a private server or hosting service. After mysqli_connect receives these 3 conditions it then returns data describing the connection, which will then be passed to mysqli_query.

Furthermore, all database manipulation is carried out using various SQL queries via mysqli_query. Using SQL queries, you can create and delete tables, make data selections based on filters of a specific type, and add and delete rows. In its work mysqli_query accepts two parameters:
  1. The first is data describing the connection (results from mysqli_connect), and
  2. The second is a SQL query in simple string form.
In this article we will learn how to connect PHP files to a MySql Server database. Before we go any further, you should read articles related to the material we will study now.



1. System Specifications
  • OS: FreeBSD 13.2
  • IP Address: 192.168.5.2
  • Hostname: ns1
  • Apache24
  • mod dan extension PHP
  • MySQL:  mysql80-server
  • php: php82
  • phpMyAdmin: phpMyAdmin5-php82


2. Create a MySQL Database

To start this lesson, make sure the Mysql server program is installed on the FreeBSD server, in this article we use the mysql80-server version. As a first step to make it easier to practice database connections with PHP, we will start by creating a MySQL Server database.

Before we start, make sure the "bind-address" script in the file "/usr/local/etc/mysql/my.cnf" must be changed to the FreeBSD private server IP, namely 192.168.5.2, as in the example below.

root@ns1:~ # ee /usr/local/etc/mysql/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address =
192.168.5.2
basedir = /usr/local
datadir = /var/db/mysql


a. Create a MySQL user

So that the MySQL database can be connected to PHP, a database user is needed which will bridge the MySQL and PHP. This user stores password information and various MySQL database information. Below is an example of creating a user with MySQL.

root@ns1:~ # mysql -u root -p
Enter password:
router
root@localhost [(none)]>

In the script above "router" is the root password of the MySQL server that has been installed on the FreeBSD machine. Once we are at the MySQL command line, we go to the MySQL database.

root@localhost [(none)]> use mysql;
Database changed
root@localhost [mysql]>
select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

root@localhost [mysql]>
The first script activates the MySQL database, while the second script looks at active users in the MySQL database. After that we continue by creating a new user. Type the command below to create a new user on the MySQL server.

root@localhost [mysql]> CREATE USER 'semeru'@'192.168.5.2' IDENTIFIED BY 'gunungsemeru';
Query OK, 0 rows affected (0.05 sec)

root@localhost [mysql]>
GRANT SELECT ON *.* TO 'semeru'@'192.168.5.2';
Query OK, 0 rows affected (0.02 sec)

root@localhost [mysql]>
The description of the script above is to create a user "semeru" with the password "gunungsemeru" and the host "192.168.5.2". For the host name, we can replace it with the hostname in the /etc/rc.conf file. In this article our FreeBSD server has the hostname "ns1". Below is how to create a user with the hostname "ns1".

root@localhost [mysql]> CREATE USER 'rinjani'@'ns1' IDENTIFIED BY 'gunungrinjani';
Query OK, 0 rows affected (0.05 sec)

root@localhost [mysql]>
GRANT SELECT ON *.* TO 'rinjani'@'ns1';
Query OK, 0 rows affected (0.03 sec)

root@localhost [mysql]>

b. Create databases and tables in MySQL

Now that we know how to create a user in MySQL, let's continue with creating a database and table. Below is the script used to create a database in MySQL.

root@localhost [mysql]> CREATE DATABASE bromo;
Query OK, 1 row affected (0.05 sec)

root@localhost [mysql]>
CREATE DATABASE anjani;
Query OK, 1 row affected (0.04 sec)

root@localhost [mysql]>
The script above creates a new database named "bromo" and "anjani". To see the results of creating the database, use the command below.

root@localhost [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| anjani |
| bromo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
After successfully creating a database, we will continue with creating tables. Below are the commands used to create tables on the MySQL server.

root@localhost [mysql]> CREATE TABLE gunung ( namagunung VARCHAR(128), daerah VARCHAR(90), ketinggian VARCHAR(30));
Query OK, 0 rows affected (0.18 sec)
The script above will create a new table called "mountain" with the contents of the tables "mountain name", "area" and "height". After that, we will enter data or enter data in the table. The command below is used to enter data into a table.

root@localhost [mysql]> INSERT INTO gunung VALUES('gunung semeru', 'Jawa Timur', '3676 m');
Query OK, 1 row affected (0.04 sec)

root@localhost [mysql]>
INSERT INTO gunung VALUES('gunung rinjani', 'NTB', '3726 m');
Query OK, 1 row affected (0.02 sec)

root@localhost [mysql]>
INSERT INTO gunung VALUES('gunung merbabu', 'Jawa Tengah', '3145 m');
Query OK, 1 row affected (0.02 sec)
To see the results, use the command below.

root@localhost [mysql]> SHOW TABLES;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| gunung |
| servers |
| user |
+------------------------------------------------------+
38 rows in set (0.00 sec)


root@localhost [mysql]>
SELECT * FROM gunung;
+----------------+-------------+------------+
| namagunung | daerah | ketinggian |
+----------------+-------------+------------+
| gunung semeru | Jawa Timur | 3676 m |
| gunung rinjani | NTB | 3726 m |
| gunung merbabu | Jawa Tengah | 3145 m |
+----------------+-------------+------------+
3 rows in set (0.00 sec)
To test or test the results of the material we have studied above, you can use "phpmyadmin". Try logging in with the users "semeru" and "rinjani". After successfully logging in, in phpmyadmin open the mysql database, and see the results.




3. Creating a MySQL Database Connection With PHP


a. Connection with mysqli

Before you study section 3 further, make sure the system specifications above have been installed on the FreeBSD server and that you have also read the articles we have recommended above. OK, let's get started with part 3 of this material. As a first step we will install the applications "php82-mysqli" and "php82-pdo". These two applications will connect PHP with the MySQL server.

root@ns1:~ # cd /usr/ports/databases/php82-mysqli/ && make install clean
root@ns1:/usr/ports/databases/php82-mysqli #
cd /usr/ports/databases/php82-pdo/ && make install clean
Let's continue the material, now it's time to connect PHP to the MySQL server. To test this connection, we will create a new file called "testkoneksi.php" which we will place in the "/usr/local/www/apache24/data" folder. In the file "testkoneksi.php" type the script below.

root@ns1:~ # ee /usr/local/www/apache24/data/testkoneksi.php

<?php
$servername = "192.168.5.2";
$username = "semeru";
$password = "gunungsemeru";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Happy! You have successfully connected PHP to the MySQL Server Database";
?>
The next step is to restart the apache24 web server.

root@ns1:~ # service apache24 restart
After the apache24 server has successfully restarted, open the Google Chrome, Yandex or Firefox web browser. In the address bar menu, type "http://192.168.5.2/testkoneksi.php", and the results will look like the image below.




b. Connection with pdo

After we have successfully created a connection with mysqli, now we continue with the pdo connection. Follow the steps below to create a PHP MySQL Server connection with PDO.

The first step is to create a file called "/usr/local/www/apache24/data/koneksipdo.php". In the file "koneksipdo.php" enter the script below.

root@ns1:~ # ee /usr/local/www/apache24/data/koneksipdo.php

<?php
$servername = "192.168.5.2";
$username = "semeru";
$password = "gunungsemeru";

try {
$conn = new PDO("mysql:host=$servername;
dbname=bromo", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Selamat! Anda telah berhasil menghubungkan PHP PDO dengan Database MySQL Server";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Try, pay attention to the blue script above. In making a MySql database connection with PHP PDO, a database is needed (this is what differentiates it from a MySQL connection). Because in the material of this article we have created a database with the names "bromo" and "anjani", so we can choose one of the two. In this case to create a connection with PDO, we take the "bromo" database.

How?, at this point do you understand about creating a PHP connection with MySQL Server. If not, restart the apache24 server.

root@ns1:~ # service apache24 restart
Then open the Google Chrome web browser, and type "http://192.168.5.2/koneksipdo.php", see the results, is your database connection with PHP PDO successful?

MySQLi and PDO have their respective advantages. It is important to remember that MySQLi is for MySQL databases only. Meanwhile, PDO can be used together with other databases such as MariaDB, MongoDB, PostgreSQL and others. PDO can simplify the transition process, so it is very good for use by databases other than MySQL servers.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post