Do you want to learn how to connect PHP to lớn MySQL & perform different types of database operations? In this article, we"ll vị just that—we"ll discuss MySQL database connectivity in PHP.
PHP provides different ways to connect PHP to a MySQL database server. Until PHP 5.5, one of the most popular ways was with the MySQL extension—it provided a "procedural" way lớn connect khổng lồ the MySQL server. However, this extension is deprecated as of PHP 5.5, so we’re not going lớn discuss that.Bạn đã xem: How khổng lồ connect mysql database with php website
Currently, your two choices are PDO và MySQLi.
The PDO (PHP Data Objects) extension supports different types of underlying database servers along with the MySQL server. & thus, it’s portable in case you decide to lớn swap the underlying database vps at any point in the future.
On the other hand, the MySQLi extension is specific lớn the MySQL server & provides better speed và performance. The MySQLi extension might also be a little simpler lớn understand at first, since it lets you write SQL queries directly. If you"ve worked with SQL databases before, this will be very familiar. The PDO extension, however, creates a powerful mapping from the SQL database to lớn your PHP darkedeneurope.com & lets you vày a lot of database operations without needing to know the details of SQL or the database. In the long run & for larger projects, this can save a lot of coding và debugging effort.
In this post, I"ll use the MySQLi extension. But you can learn more about the PDO extension here on Envato Tuts+.
We’ll cover the following topics in this article:creating a MySQL connectioninserting, updating, and deleting recordsretrieving records
Best PHP Database Scripts on darkedeneurope.comCanyon
In this post, I"ll be showing you how to make a bare-metal connection to a MySQL database from PHP. This is an important skill khổng lồ have, but if you want lớn save time on your next project, you might want to upgrade to a professional database script. This will save time and make your work easier.
Explore the best and most useful PHP database scripts ever created on darkedeneurope.comCanyon. With a low-cost one-time payment, you can purchase these high-quality WordPress themes và improve your website experience for you & your visitors.
Here are some of the best PHP database scripts available on darkedeneurope.comCanyon for 2020.
Create a MySQL Connection
In this section, I"ll show you how you can connect lớn the MySQL server from your PHP script & create a connection object. Later on, this connection object will be used to run queries, fetch output, và manipulate the database records. As we discussed earlier, we’re going lớn use the PHP MySQLi extension for this article.
The MySQLi extension provides two different ways khổng lồ perform database operations. You can use it either the object-oriented way or the procedural way, but as this tutorial is aimed at beginners, I"ll stick with the procedural way. If you"re interested in seeing some object-oriented snippets, you can ping me in the comment section below & I’ll be happy khổng lồ provide them.
To start, go ahead & create the db_connect.php file under your document root with the following contents.
Note that I"ve used placeholders in the mysqli_connect function—you have to replace these with actual values.
Let’s go through each placeholder to lớn see what it stands for.HOST_NAME: This represents your MySQL server’s host-name or IP address. If you have installed the MySQL server on the same system along with PHP, you should use localhost or 127.0.0.1. On the other hand, if you’re using a MySQL hệ thống which is hosted externally, you can use the corresponding host-name or IP address.DATABASE_USERNAME: This represents the username of your MySQL user. Basically, this is the username which you use khổng lồ connect to your MySQL server.DATABASE_PASSWORD: This represents the password of your MySQL user. Again, this is the password which you use khổng lồ connect to your MySQL vps along with the MySQL username.DATABASE_NAME: This is the name of the MySQL database which you want to connect to. Once the connection is created, you"ll query this database for further operations.
Go ahead and replace these placeholders with actual values. In my case, I’ve installed the MySQL server locally, and I have a MySQL user with darkedeneurope.com-demo-user as the MySQL username and darkedeneurope.com-demo-password as the MySQL password. & with that, the above example looks lượt thích this:
If you run this example, it should create a database connection links and assign it khổng lồ the $mysqli_link variable, which we’ll use later on khổng lồ perform different types of database operations.
On the other hand, if there’s any problem setting up the connection, the mysqli_connect_errno function will return an error darkedeneurope.com, & the mysqli_connect_error function will display the actual error. In the above example, we’ve used it for debugging purposes.
Now, we have a successful connection khổng lồ the MySQL server, & we’ll see how to use it to perform different types of queries from the next section onwards.
How lớn Insert Records
In the previous section, we discussed how khổng lồ use the mysqli_connect function to lớn set up a database connection with the MySQL server. In this section, we’ll go ahead & discuss how khổng lồ use the connection object to perform INSERT queries.
If you want lớn follow along with the examples discussed in this article, you"ll need lớn create the following MySQL table in your database. It"s the table which we"re going khổng lồ use in all the examples from now on.
CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8mb4_unidarkedeneurope.com_ci NOT NULL, `last_name` varchar(255) COLLATE utf8mb4_unidarkedeneurope.com_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unidarkedeneurope.com_ci NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB mặc định CHARSET=utf8mb4 COLLATE=utf8mb4_unidarkedeneurope.com_ci;Go ahead & create the above MySQL table by using the phpMyAdmin software or command-line tool.
First, we mix up a database connection by using the mysqli_connect function, as we discussed earlier. After that, we’ve prepared the insert query which we’re going to execute later on. It"s important to cảnh báo that we"ve used the mysqli_real_escape_string function khổng lồ escape string values that we’re going lớn use in the insert query. Specifically, you must use this function when you’re dealing with values submitted via $_POST variables khổng lồ avoid SQL injection.
Finally, we’ve used the mysqli_query function, which takes two arguments. The first argument is the active connection links where the query will be executed. Và the second argument is the MySQL query which we want to lớn execute. The mysqli_query function returns TRUE if the query was executed successfully.
Finally, we’ve used the mysqli_close function to lớn close the active database connection. It’s a good practice to lớn close the database connection once you’re done with database operations.
Go ahead and run the script, và that should insert a record in the students table!
How khổng lồ Update Records
Updating the records in a database from the PHP script is very similar to the insert operation; the only difference is that the query is going to be the update query instead of the insert query.
Let’s revise the above example and update the first_name field, as shown in the following example.
Go ahead and run the script, and that should update the record in the students table.
How khổng lồ Select Records
In the earlier sections, we discussed how you can insert & update records in a database from the PHP script. In this section, we’ll explore how you can fetch records from a database by using the different types of MySQLi functions.
Firstly, you need to use the mysqli_query function to lớn execute the select query. Upon successful execution of the select query, the mysqli_query function returns the mysqli result object, which we could use lớn iterate over the records returned by the select query. When it comes to fetching and iterating over the records from the MySQLi result object, there are different functions available.mysqli_fetch_all: It allows you to lớn fetch all result rows at once. You can also specify whether you want results as an associative array, a numeric array, or both.mysqli_fetch_array: It allows you to retrieve one row at a time. And thus, you’ll have to use the while loop khổng lồ iterate over all the records. Again, you can specify whether you want a result row as an associative array, a numeric array, or both.mysqli_fetch_assoc: It fetches a result row one at a time as an associate array.mysqli_fetch_object: It fetches a result row one at a time as an object.
Let’s have a look at the following example lớn understand how it works:
On the other hand, if you use the mysqli_fetch_object function in the above example, you can access the values as shown in the following snippet. For brevity, I’ll only include the while loop snippet.
How khổng lồ Delete Records
In this section, we’ll see how to run delete queries from the PHP script. Deleting records from a database is a pretty straightforward operation since you just need to prepare the delete query & run it with the mysqli_query function.
Let’s go through the following example to lớn see how it works.
As you can see, everything is the same as we’ve seen already with the insert and update examples, except that the query is a delete query in this case.
So that"s how you can perform different types of database operations from the PHP script.
And with that, we’ve reached the kết thúc of this tutorial. What we’ve discussed so far today should help you khổng lồ strengthen database connectivity concepts, specifically how lớn connect PHP to lớn MySQL & perform different types of operations.
In this article, we discussed how to connect PHP to a MySQL database by using the MySQLi extension. From setting up a database connection to lớn executing different types of queries, we discussed almost every aspect of database connectivity.
Although I’ve tried khổng lồ keep things as simple as possible, if you still find anything confusing or want lớn know any specific thing in detail, feel không tính phí to post your queries using the feed below.
The Best PHP Scripts on darkedeneurope.comCanyon
Explore thousands of the best and most useful PHP scripts ever created on darkedeneurope.comCanyon. With a low-cost one-time payment, you can purchase these high-quality WordPress themes & improve your website experience for you & your visitors.