With PHP, you can connect to and manipulate databases.
MySQL is the most popular database system used with PHP.
What is MySQL?
- MySQL is a database system used on the web
- MySQL is a database system that runs on a server
- MySQL is ideal for both small and large applications
- MySQL is very fast, reliable, and easy to use
- MySQL uses standard SQL
- MySQL compiles on a number of platforms
- MySQL is free to download and use
- MySQL is developed, distributed, and supported by Oracle Corporation
- MySQL is named after co-founder Monty Widenius's daughter: My
The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.
Databases are useful for storing information categorically. A company may have a database with the following tables:
- Employees
- Products
- Customers
- Orders
PHP + MySQL Database System
- PHP combined with MySQL are cross-platform [you can develop in Windows and serve on a Unix platform]
Database Queries
A query is a question or a request.
We can query a database for specific information and have a recordset returned.
Look at the following query [using standard SQL]:
SELECT LastName FROM Employees
The query above selects all the data in the "LastName" column from the "Employees" table.
To learn more about SQL, please visit our SQL tutorial.
Download MySQL Database
If you don't have a PHP server with a MySQL Database, you can download it for free here: //www.mysql.com
Facts About MySQL Database
MySQL is the de-facto standard database system for web sites with HUGE volumes of both data and end-users [like Facebook, Twitter, and Wikipedia].
Another great thing about MySQL is that it can be scaled down to support embedded database applications.
Look at //www.mysql.com/customers/ for an overview of companies using MySQL.
What is MySQL?
MySQL is an open-source relational database management system [RDBMS]. It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation.
- The data in a MySQL database are stored in tables which consists of columns and rows.
- MySQL is a database system that runs on a server.
- MySQL is ideal for both small and large applications.
- MySQL is very fast, reliable, and easy to use database system.It uses standard SQL
- MySQL compiles on a number of platforms.
Downloading MySQL Database
MySQL can be downloaded for free from this link.
How to connect PHP with MySQL Database?
PHP 5 and later can
work with a MySQL database using:
- MySQLi extension.
- PDO [PHP Data Objects].
Difference Between MySQLi and PDO
- PDO works on 12 different database systems, whereas MySQLi works only with MySQL databases.
- Both PDO and MySQLi are object-oriented, but MySQLi also offers a procedural API.
- If at some point of development phase, the user or the development team wants to change the database then it is easy to that in PDO than MySQLi as PDO supports 12 different database systems.He would have to only change the connection string and a few queries. With MySQLi,he will need to rewrite the entire code including the queries.
There are three ways of working with MySQl and PHP
- MySQLi [object-oriented]
- MySQLi [procedural]
- PDO
Connecting to MySQL database using PHP
There are 3 ways in which we can
connect to MySQl from PHP as listed above and described below:
- Using MySQLi object-oriented procedure: We can use the MySQLi object-oriented procedure to establish a connection to MySQL database from a PHP script.
Syntax:
Output:
Explanation: We can
create an instance of the mysqli class providing all the necessary details required to establish the connection such as host, username, password etc. If the instance is created successfully then the connection is successful otherwise there is some error in establishing connection.
- Using MySQLi procedural procedure : There is also a procedural approach of MySQLi to establish a connection to MySQL database from a PHP script as described
below.
Syntax:
Output:
Explanation: In MySQLi procedural approach instead of creating an instance we can use the mysqli_connect[] function available in PHP to establish a connection. This function takes the information as arguments such as host, username , password , database name etc. This function returns MySQL link identifier on successful connection or FALSE when failed to establish a connection.
- Using PDO procedure: PDO stands for PHP Data Objects. That is, in this method we connect to the database using data objects in PHP as described below:
Syntax:
Output:
Explanation:The exception class in PDO is used to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch[]{ } block.
Closing A Connection
When we establish a connection to MySQL database from a PHP script , we should also disconnect or close the connection when our work is finished. Here we have described the syntax of closing the connection to a MySQL database in all 3 methods described above. We have assumed that the reference to the connection is stored in $conn variable.
- Using MySQLi object oriented procedure
Syntax
$conn->close[];
- Using MySQLi procedural procedure
Syntax
mysqli_close[$conn];
- Using PDO procedure
Syntax
$conn = null;