If you are a beginner to website development, it may be beneficial to know how to connect to a script. In this way, you will be able to modify, view or manage the tables created in the MySQL database. This article will show you the easiest ways to do it, let’s get started.
Create a MySQL database (Optional)
This step is necessary in case you don’t have one yet. MySQL database created. If you are a user, you can easily create a new one through the hPanel in a few steps:
- look for the menu MySQL databases in the section Databases.
- Fill in the necessary fields and press To create.
Keep in mind that when creating a database, it will be blank and you must before you can manipulate them.
Take note of the credentials of the MySQL database you just created for the next step. Don’t forget to write down the database username and password too!
Two ways a PHP script can connect to MySQL
There are two methods to connect with PHP to a MySQL database: MySQL i Y PDO.
MySQL i are the acronyms for MySQL Improved. It is an exclusive MySQL extension that adds new features to the interface of a MySQL database. MySQLi is both procedural and object-oriented, with the first attribute being inherited from the previous version of MySQL.
The original MySQL breaks a task down into linear, step-by-step procedures, which makes modification difficult because you have to edit the code from the top. Meanwhile, MySQLi views data as a set of interchangeable objects with functions, allowing users to easily add or delete data.
PDO are the acronyms for PHP DataObject. Unlike MySQLi, PDO is only object-oriented and supports several different database types that use PHP, such as MySQL, MSSQL, Informix, and PostgreSQL.
The original functions of mysql_ they are in disuse and should not be used because they are not secure and are no longer maintained or developed.
One of the most important features that both support is the prepared statements, which speeds up the time required for MySQL to execute the same query multiple times. It is also used to prevent SQL injection attacks when making changes to the database.
Whichever method you use, you will need the correct information to be able to connect to the MySQL database you have created. This is where the MySQL database details you have previously saved come in handy.
You also need the server name either hostname correct for the configuration. uses «localhost» as the hostname of your MySQL server. In general, this is the name you’ll want to use if you uploaded your PHP script to the same server as the database.
Conversely, if you’re connecting to a database from a remote location (for example, your computer), you’ll need to use the IP adress of the MySQL server. For more details, contact your hosting provider so they can provide you with the correct information on what to use as your hostname.
Using MySQLi to connect a PHP script to MySQL
Follow these steps to use MySQLi to connect a PHP script to MySQL:
- go to File Manager -> public_html.
- Create a new file by clicking on the top menu icon.
- save it as databaseconnect.php. You can replace the name with whatever you want, just make sure you’re using php as the extension.
- Double click to open the file and copy and paste the following lines of code into it. Change the first four values below with the credentials you noted earlier.
MySQLi Code Explained
The main method used in this script is mysqli_connect(). This is an internal PHP function to establish a new connection to a MySQL server.
At the beginning of our code, we see few variable declarations and values assigned to those variables. We generally need four variables to establish a proper connection: $servername, $database, $username, and $password. In the code, we’ve set the exact data from our database as values for those variables, so they can be passed to the function.
If the connection is not successful, the function is executed die(). This basically kills the script and throws the connection established error message. By default, the MySQL connection error will say connection failed followed by an exact error message describing the problem.
On the other hand, if the MySQL connection is successful, the code will print Connected successfully.
The last part of the code is mysqli_close, which will simply allow you to close the connection to the database manually. If not specified, MySQL connections will close on their own after the script completes.
Use PDO to connect a PHP script to MySQL
The other method you can use to connect a PHP script to MySQL is PDO. It is similar to the previous method, but with a slight variation:
- In public_htmlcreate a file called pdoconfig.php and insert the following code. As always, don’t forget to replace the placeholder values with the information from your database. Save it and close it once you’re done.
- Create another file called databaseconnect.php in the same directory, but with the following code. If you named the above file differently, be sure to change the value of require_once. getMessage()); }
PDO code explanation
A PDO database connection requires you to create a new PDO object with a Data Source Name (DSN – Data Source Name), Username Y password.
The DSN defines the database type, the database name, and any other information, if necessary. These are the variables and values that we declare inside the file dbconfig.phpreferenced once per line require_once in databaseconnect.php.
In the latter, you will find the code try…catch… This means that the script will try (try) connect to MySQL using the provided code, but if there is a problem, the code in the capture section will be executed (catch). you can use the catch block to display connection error messages or run fallback code if connection fails try block.
If the connection is successful, it will print the message «Connected to $dbname at $host successfully«. However, if the attempt fails, the capture code will display a simple error message and kill the script.
Verify connectivity and resolve common errors
To check if the connection is successful, access your domain like this: yourdomain/databaseconnect.php. If you name the PHP file in a different way, make sure to change the above address accordingly.
You’ll see “connected successfully» or variants of this message if everything is working fine.
Now, if the connection was not successful, you will see something different. The errors are slightly different for MySQLi and PDO.
Wrong Password Error
This error occurs if we change the password or any credentials in the PHP code (but not change it in the actual database).
In case you see a message like “Access denied” either “Could not connect to database” Accompanied by “(using password: YES)» in the end, the first thing you should do is check the database details. There could be a typo or a missing part.
Can’t connect to MySQL server
If you get the message “Can’t connect to MySQL server on ‘server’ (110)» in MySQLi, it means that the script did not get a response from a server. This happens when we configure «server” instead of “localhost” What $servernameand the name is not recognized.
The error message in PDO will look like “Connection failed: SQLSTATE » followed by more details indicating that the My SQL host was not found. But the way to fix it is the same as above.
And, of course, it’s always important to remember a golden rule for troubleshooting: check your site’s error log.
The log can be found in the same folder where the script is executed. For example, if we are running a script in public_htmlwe will find the record error_log in the same folder.
conclusion
In this tutorial, you have learned the basics of how to connect a PHP script to a MySQL database using MySQLi Y PHP Data Objects (PDO).
Hopefully, this guide was helpful to those who are just getting started with web development. After all, connecting to a database is the first and most important step when working with more advanced configurations and scripts.
Let us know in the comments if you run into any issues following this guide.
Gustavo is passionate about creating websites. He focuses on the application of SEO strategies at for Spain and Latin America, as well as the creation of high-level content. When he is not applying new WordPress tricks you can find him playing the guitar, traveling or taking an online course.
Facebook Comments
Disqus