How to Connect PHP to MySQL Database

Want to connect PHP to MySQL? You're in the right place. In this blog we talk through why PHP and MySQL databases are a popular combination, how to connect PHP to MySQL, a script that can help with that process, and a breakdown of the security considerations teams need to make when connecting MySQL and PHP.

What Is MySQL?

MySQL is most popular open source option for a Relational Database Management System (RDBMS). It complies with SQL standards, and provides popular RDBMS functions such as triggers, joins, and views.

And for developers already familiar with RDBMS solutions such as Db2 and Oracle, MySQL is also easy to learn. MariaDB is a fork of MySQL. You can use the information is this blog to connect MariaDB to PHP as well.  

Why Connect PHP to MySQL?

The purpose of many PHP solutions is to provide web-based access to dynamic content that’s stored in a database. PHP supports many database management systems including MySQL, MariaDB, Db2, MongoDB, Oracle, PostgreSQL, and SQLite.

Need Help Modernizing Your PHP App?

Zend can help. Talk to an expert today to see how we can help support your modernization efforts.

Talk to an Expert

How to Connect PHP to MySQL Database

Here are two steps for connecting PHP to MySQL database.

1. Use Extensions to Connect MySQL Database in PHP

PHP provides three extensions that you can use to:

• Connect PHP applications with MySQL (and MariaDB).  
• Retrieve database server information.
• Manage errors generated from database calls
• Work with database records using the Create, Read, Update, and Delete (CRUD) functions.

The three extensions that PHP provides to connect with MySQL include mysqli, mysqlInd, and pdo_mysql.

mysqli Extension

mysqli in php supports MySQL 4.1 and newer. Mysqli is also referred to as MySQL improved.

mysqlnd Extension

Usually referred to as MySQL Native Driver, MysqlInd provides a PHP-native infrastructure for all MySQL extensions, and is a drop-in replacement for libmysqlclient. It’s important to note that mysqlnd does not provide an API. Read the documentation for more information.

pdo_mysql Extension

pdo_mysql provides a PHP Data Object (PDO) interface to MySQL databases which is a data-access abstraction layer.

2. Add SQL Statements to PHP Functions

By using MySQL extensions in PHP scripts, you can add the following SQL statements in PHP CRUD functions to work with MySQL database records:

• INSERT
• UPDATE
• DELETE

To specify which records will be involved, you can use WHERE clauses. Typically, the values that the SQL statements need will come from web-form values and be represented as variables in the PHP script. 

Sample Script to Connect MySQL Database and Make MySQL Queries in PHP

Here's is a simple example of a PHP script that uses calls provided by the mysqli extension to select records from a MySQL database:

Keep in mind that the DBMS will enforce the same constraints on the SQL statements being executed via PHP as it would any other interface into the database. Attempts to INSERT records with duplicate keys would be rejected. Proper code should include tests for error conditions on the database connection (shown above) as well as the query executions.  

As an example, the following code could have been inserted after the 'mysqli_connect' call to validate that a successful connection between PHP and MySQL was obtained (see more on How to Test your MySQL Connection here):

if (mysqli_connect_error()) {
   print("Connect failed: " . mysqli_connect_error());
   exit();
}

Similarly, the 'mysqli_query' call could be tested for a valid return and if not the 'mysqli_error()' call could be used to output the specific error.  The 'mysqli_error()' returns a string description of the last error while the 'mysql_errorno()' returns the error number.

Security Considerations When Using MySQL and PHP

You need to keep security at the forefront of both the application's design as well as implementation.

One way to ensure data security is through the 'filter' extension provided for PHP which provides for a number of filter types including 'validate', and 'sanitize'.

SQL Injection

SQL injection is exactly what the name implies it's injecting data/statements into an SQL statement. Consider the following SQL INSERT statement:

select * from dbtable where customer = $name;

Let's further assume that the value for $name is coming from a web-form (probably a save assumption since this is likely a PHP application. Without proper hygiene of the web-form or validation of the data a user could input the following for name:

John;truncate sales;

This would result in the following SQL statements:

select * from dbtable where customer = John; truncate sales;

Now, when this is executed in addition to the select statement being executed, the records from the sales table would be deleted – highly unlikely that that is the result we wanted. So how can we prevent this from occurring? One way is with prepared statements. With a prepared statement, instead of sending a raw query (like I've shown above) to the database engine we first tell the database the structure of the query that will be submitted.

How to Avoid SQL Injection

To avoid SQL injection, use a prepared query that defines placeholders for the parameters of the query statement and then binds values to those parameters. Let's take a look at another example, this time an SQL INSERT will be used:

INSERT into dbtable (name) VALUES ($name);

At this point it is still possible to have malicious statements injected via the data represented by the $name variable being passed to the database.  Let's change the above statement to instead send a placeholder to the database engine:

INSERT into dbtable (name) VALUES (?);

Now, injection isn't possible since no value (variable or literal) is being sent to the database engine.

The parameterized statement (sometimes referred to as a template) is sent to the database engine with the mysqli_prepare() function.

So how do we actually get the value itself to the database? That is done with the mysqli_stmt_bind_param() function and finally, the statement is executed with the mysqli_stmt_execute() function.

Let's put all this together in an example:

<?php
   $con = mysqli_connect('localhost', 'dbuser', 'userpass', 'db');
   $sql = "INSERT into dbtable (name) values(?)";
   $stmt = mysqli_prepare($con, $sql);
   mysql_stmt_bind_param($stmt, 's', $name);
   mysql_stmt_execute($stmt);
   mysqli_close($con);
?>

Since the bound variables are sent to the database engine separate from the query they cannot be interfered with. The database engine uses the values directly at the point of execution after the statement itself has been parsed.

Note that the second parameter to the 'mysql_stmt_bind_param()' function is a string to indicate the 'type(s)' for the values being passed. In this case only one value is being passed and that value has a type of string. The above code should be expanded to included error checking along the way at each database function execution.

If you are using Zend Server in your environment then you are aware that you have:

• Functions such as code-tracing.
• Z-Ray for application profiling.

Those functions can expose data from the application such as data query values. What you might not be aware of is that Zend Server also includes the ability to mask data for functions, identifiers, and keys — as well as values.

MySQL and PHP: Putting the MP in LAMP Stack

MySQL and PHP are integral parts of the ubiquitous LAMP stack which is a stack of components that afford the ability to both develop as well as deploy web-based applications with dynamic content. 

Popular productivity applications — such as WordPress, Drupal, Magento, ZenCart, and others — all take advantage of this stack. They use it to implement high-quality, enterprise-ready, productivity solutions in the Customer Relationship Management (CRM), eCommerce, Content Management System (CMS), and other spaces.

These applications are typically turn-key in their approach to installation/configuration and rely on a community approach both to develop as well as support. Enterprise-class support for these types of solutions can be obtained from a number of sources including the OpenLogic team at Perforce.

Note: The LAMP acronym refers to the Operating System (Linux), Web Server (Apache), DataBase Management System (DBMS), and Scripting Language (PHP). But the reality is that the same stack is available on a myriad of operating systems including Windows (WAMP) and IBM i (iAMP). The following diagram represents the stack:

Final Thoughts

Web applications, whether desktop or mobile, are powerful ways to get data into the hands of your employees, customers, or other stakeholders to support any number of requirements including commerce, resource planning, and information building and sharing.

PHP and MySQL are two valuable tools for bringing data to the web both through customer applications as well as existing Open Source solutions based on these technologies. 

Planning to Use the LAMP Stack?

Zend can help your team develop a strategy for implementation of PHP/MySQL solutions -- and provide support for solutions implemented on these technologies.

Contact our team today to see how Zend Professional Services can help you reach your development goals.

Contact Us