MySQL connection methods

Started by Asokanvon, Feb 11, 2023, 12:01 AM

Previous topic - Next topic

AsokanvonTopic starter

I don't have much expertise in PHP and MySQL, which is why I am unsure about the best way to connect to a database. There are three ways that I know of - mysql_connect, mysqli, and PDO.

I have come across a few articles and videos that are over three years old, where they mention that mysql_connect is outdated and no longer used. However, I am interested in knowing what is currently considered as the correct and safe method to connect to MySQL in 2023. Any new article or video on this topic would be greatly appreciated.

On another note, I'd like to inquire about data encryption and how to store it in a secure format. I am aware of the RedBean library and some MySQL commands for encryption, but I'm curious to know the best and most secure approach for this purpose.


The question at hand is flawed since it actually consists of two questions: which driver to use and how to connect properly. The former can be answered by referring to the manuals for mysql_connect, mysqli, and PDO. However, the latter question is more critical yet receives little attention as most articles on the subject are outdated. Thankfully, there are resources available with current information.

Although theoretically possible, using mysqli as a driver requires preparation and can be inconvenient. If opting for this method, specific steps need to be taken to connect correctly, including throwing exceptions, setting encoding, and handling exception interception. Nonetheless, the best option from the three is PDO, and it is equally essential to connect properly while considering the same elements mentioned before.

Redbean could also be a viable option, but given the lack of documentation and explanations on the basics, it's most suitable for experienced developers. For password security, hashing with the password hash function is recommended over encryption.


When it comes to working with the database directly, PDO is the suitable choice. However, if you intend to operate with higher-level abstractions or ORM, then you need not ask which driver to use as they already implement all the necessary measures.


For beginners, mysqli_connect appears to be a sensible choice. However, safety concerns related to the connection method are not significant, especially for those just starting. Instead, security measures demand significant consideration.

Personally, I presently operate with doctrine orm, but when I commenced, I used the method that was erased from the seven. Initially, I found it challenging to grasp OOP techniques, though I was soon able to master it effortlessly. Nonetheless, the push towards OOP is diminishing the primary aspect of PHP's low entry threshold.


In PHP, there are two methods for connecting to a MySQL database: MySQLi and PDO.

MySQLi (MySQL Improved) is an exceptional MySQL extension that includes fresh features to the database interface. MySQLi functions follow both procedural and object-oriented paradigms and permit users to add or extract data. In comparison, MySQL breaks down data into a linear sequence of procedures that makes editing code from upper levels more difficult.

PDO (PHP Data Object) is an object-oriented approach that supports several databases types like Informix, MySQL, MSSQL, and PostgreSQL. In contrast to MySQLi, which includes both procedural and object-oriented paradigms, PDO only uses an object-oriented methodology.

The original mysql_ functions are deprecated due to their lack of safety and support. Prepared statements are one of the most valuable features of both approaches, as they speed up MySQL's execution time and prevent SQL injections from occurring.

To connect to your MySQL database using either method, you need to have the appropriate information like the database credentials and server name. If you are uploading your PHP script on the same server as the database, use "localhost" as your server name. Conversely, if you are accessing the database remotely, get the IP address from your hosting provider.

For those using MySQLi, the following are the basic steps to connect a PHP script to MySQL:

1. Go to the File Manager -> public_html
2. Create a new file with the extension .php and save it as "databaseconnect.php" or another name of your choice
3. Enter the relevant values after <?php
4. Establish a new connection using mysqli_connect()
5. Check if the connection is established successfully using if (!$conn)
6. Run mysqli_close() to terminate the connection.

The die() function will execute if the connection attempt was unsuccessful, while "Connected successfully" will be displayed if the connection is successful.