How PHP Connects with MySQL Databases

Create a MySQL Database (Optional):

This procedure becomes essential in situations where a MySQL database is not yet established. For users availing Hostinger services, the option to generate a new database is conveniently provided through Hostinger's hPanel, employing a highly straightforward process:

1. Locate the section dedicated to "MySQL Databases" within the "Databases" category.
2. Fill in all required fields comprehensively and proceed to select the "Create" option.

It's crucial to bear in mind that when initiating a new database, it will be in an empty state, necessitating the input of all requisite data prior to any form of manipulation.

Two Ways a PHP Script Can Connect to MySQL:

There exist two approaches for establishing a connection between PHP and a MySQL database: MySQLi and PDO.

MySQLi stands for MySQL Improved. It's an exclusive MySQL extension that introduces new functionalities to the MySQL database interface. MySQLi operates both in a procedural and an object-oriented paradigm, with the latter attribute inherited from previous MySQL versions.

The original MySQL approach involves linear and sequential procedural procedures, which can complicate modifications since adjustments require changes to the code from the beginning. In contrast, MySQLi perceives data as a set of interchangeable objects with associated functions, facilitating user-friendly data addition and deletion.

On the other hand, PDO stands for PHP Data Object. Unlike MySQLi, PDO is exclusively focused on the object-oriented paradigm and supports various types of databases usable with PHP, including MySQL, MSSQL, Informix, and PostgreSQL.

Important Note: The original mysql_ functions are deprecated and should not be used due to security concerns and lack of maintenance.

One of the most significant features offered by both alternatives is the implementation of prepared statements, greatly speeding up the execution time of repetitive queries in MySQL. Furthermore, their use is essential for guarding against SQL injection attacks when making changes to the database.

Regardless of the chosen method, precise information is required to establish a connection to the pre-existing MySQL database. At this juncture, the stored details about the MySQL database come into play.

Similarly, accurate server or host names are necessary for configuration. For Hostinger, "localhost" is used as the host name for the MySQL server. In general, this is the designation to be employed if the PHP script is hosted on the same server as the database.

Conversely, when connecting to a database from a remote location (e.g., your personal computer), the IP address of the MySQL server must be used. To obtain detailed information, contacting the hosting provider is recommended for acquiring accurate host name specifications.

Using MySQLi to Connect a PHP Script to MySQL:

Follow these instructions to employ MySQLi and connect a PHP script to MySQL:

1. Access the File Manager and navigate to the public_html folder.
2. Create a new file by clicking on the icon in the top menu.
3. Save it with the name "databaseconnect.php". If you prefer another name, ensure that the extension is ".php".
4. Double-click to open the file and then copy and paste the provided code into it. Be sure to modify the first four values under "<?php" with the credentials you noted down earlier.

Explanation of MySQLi Code:

The crux of this script lies in the mysqli_connect() method. This PHP internal function is designed to establish a new connection to a MySQL server.

At the beginning of the code, a set of variable declarations and their corresponding values are visible. Ordinarily, four variables are required to create an appropriate connection: $servername, $database, $username, and $password. In this code, the specific database details are assigned as values to these variables, allowing them to be passed to the function.

If the connection is not successful, the die() function is executed. Essentially, this terminates the script and displays the established connection error message. By default, MySQL's connection error message will read "Connection failed," followed by a precise description of the encountered problem.

In contrast, if the MySQL connection is successful, the code will print "Connected successfully."

The final part of the code, mysqli_close, provides a way to manually close the database connection. If not specified, MySQL connections will automatically close once the script execution is complete.

Using PDO to Connect a PHP Script to MySQL:

The alternative approach to connect a PHP script with MySQL is using PDO. While it shares similarities with the previous method, there's a slight variation:

1. In the public_html folder, create a file named "pdoconfig.php" and insert the provided code. As usual, replace the placeholder values with relevant database information. Conclude the process by saving and closing the file when finished.
2. Create a new file named "databaseconnect.php" in the same directory and input the provided code. If you've given the previous file a different name, ensure to modify the value of "require_once" accordingly.

Explanation of PDO Code:

To establish a connection to the database using PDO, a new instance of the PDO class needs to be created, providing a Data Source Name (DSN), username, and password.

The DSN defines the database type, name, and any other necessary information. These are the values declared in the "dbconfig.php" file and later referenced using the "require_once" line in the "databaseconnect.php" file.

Within "databaseconnect.php," you'll encounter a "try...catch" structure. This signifies that the script will attempt (try) to establish a MySQL connection using the code provided within the "try" block. In case an issue arises, the code within the "catch" block is activated. The "catch" block can be used to display connection error messages or execute an alternative set of instructions if the "try" section fails.

If the connection is successfully established, the message "Connected to $dbname at $host successfully" will be printed. However, if the connection attempt is unsuccessful, the "catch" block will display a concise error message and terminate script execution.

Verifying Connectivity and Resolving Common Errors:

To confirm whether the connection is successful, access your domain as follows: "yourdomain/databaseconnect.php." If you've named the PHP file differently, make sure to adjust the above address accordingly.

If everything is functioning correctly, you will see the message "Connected successfully" or variations of it.

However, if the connection isn't successful, you'll encounter different outcomes. Errors slightly differ between MySQLi and PDO.

Incorrect Password Error: This error occurs when a password or other credentials are changed in the PHP code but not updated in the actual database.

If you see a message like "Access denied" or "Could not connect to database," accompanied by "(using password: YES)" at the end, the first step is to verify the database details. There could be a typographical error or missing information.

Inability to Connect to MySQL Server: If you receive the message "Can’t connect to MySQL server on 'server' (110)" in MySQLi, it indicates that the script didn't receive a response from a server. This occurs when we've set "server" instead of "localhost" as $servername, and the name isn't recognized.

For PDO, the error message will appear as "Connection failed: SQLSTATE[Hy000] [2002]," followed by more details indicating that the MySQL host wasn't found. However, the solution is the same as the previous case.

Of course, it's always advisable to remember a golden rule for error resolution: check your site's error log.

This log can be found in the same folder where the script is executed. For instance, if you're running a script in the public_html folder, you'll find the "error_log" log in the same directory.