MySQL from PHP
April 28, 2020

Connecting to MySQL from PHP

PHP Development

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.  

Connecting to MySQL from PHP

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.

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.

When you use Zend Server or ZendPHP Enterprise, these extensions are bundled and configured as part of the installation. You can learn more about the extensions that come with Zend Server, here. When you use community PHP, you will need to manually install these extensions and add them to your PHP configuration.

Extensions to Connect MySQL Database in PHP

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. See here 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.

SQL Statements You Can Add in 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 Makes 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:

mysql extension

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. As an example, 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:

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.
A blog doesn't afford us the opportunity to go in-depth on each function; however, as with all things related to the PHP language, php.net is an excellent site for gaining knowledge and understanding of the language and it's features. In the case of the MySQL functions (specifically the functions provided in the MySQL Improved extension) look here

Data Security with PHP

When developing data-centric applications it is important 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'. Click here for more information on the filter extension.
For this blog I want to spend a few minutes talking about SQL injection and how to avoid it in PHP.    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.

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, and Z-Ray for application profiling and that 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.

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 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.

One thing that is important to note that while the LAMP acronym refers to the Operating System (Linux), Web Server (Apache), DataBase Management System (DBMS), and Scripting Language (PHP) – 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:

PHP Software stack

Next Steps

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. 

Zend by Perforce has resources both in the PHP space that can help you develop a strategy for implementation of PHP/MySQL solutions as well as provide support for solutions implemented on these technologies. Try Zend Server for free! Download your free trial of the Zend Server web application development platform, and see how you can improve agility and service levels.

TRY ZEND FREE