PHP and MySQL database related functions

Source: Internet
Author: User
Tags dsn mysql client mysql query php and php and mysql php database php source code stmt

What is an API?
An application interface (application programming interface abbreviation) that defines the classes, methods, functions, variables, and so on everything in your application that needs to be called in order to complete a particular task. The API required for PHP applications to interact with the database is usually exposed via PHP extensions (called to the terminal PHP programmer).
The API can be either process-oriented or object-oriented. For process-oriented APIs, we perform tasks by calling functions, and for object-oriented APIs, we instantiate classes and invoke methods on objects that are obtained after instantiation. For both interfaces, the latter is usually preferred because it is more modern and provides us with a good code structure.
There are several APIs to choose from when building a PHP application that needs to be connected to the MySQL server. This document discusses these available APIs and discusses how to choose the best solution for your application.
What is a connector?
In the MySQL documentation, the term connector is interpreted as "a piece of software code that allows your app to connect to the MySQL database server." MySQL provides a number of language connectors, including PHP.
When your PHP application needs to interact with a database server, you need to write PHP code to complete a series of activities such as "Connect to the database server", "Query Database", and other database-related functions. Your PHP application will use the software that provides these APIs, or use some intermediate libraries when needed to handle the interaction between your application and the database server. This software is often considered a connector that allows your references to connect to the database server.
What is a driver?
A driver is a piece of software code designed to interact with a particular type of database server. The driver may call some libraries, such as the MySQL client library or the MySQL native driver library. These libraries implement the underlying protocol for interacting with the MySQL database server.
Through an example "style=" Color:rgb (0,0,153); The database abstraction layer for PDO (the abbreviation for PHP object) can be driven by a variety of specific databases. One driver is the PDO MySQL driver, which is the interface to the MySQL server.
Sometimes people will use the two terms of the connector and drive without distinction. The term "driver" in a MySQL-related document is used as a software code that provides a specific database section in a connector package.
What is an extension?
You will also find many other extensions in the PHP documentation. PHP code is made up of a core, and some optional extensions make up the core functionality. MySQL-related extensions for PHP, such as Mysqli,mysql, are implemented based on the PHP extension framework.
An extension of a typical function is to expose an API to a PHP programmer, allowing the extension of its own functionality to be used by programmers. Of course, there are also a subset of extensions that are developed based on the PHP extension framework that will not expose the API interface to PHP programmers.
For example, the PDO MySQL driver extension does not expose the API interface to a PHP programmer, but it provides an interface to the PDO layer on its upper level.
The term API and extension describe not the same thing, because the extension may not need to expose an API interface to programmers.
What are the main APIs available in PHP for MySQL?
When considering connecting to a MySQL database server, there are three main APIs to choose from:
MySQL Extension for PHP
PHP mysqli Extensions
PHP Data Objects (PDO)
All three have their own merits and demerits. The following discussion is to give a brief introduction to the key aspects of each API.
What is the MySQL extension for PHP?
This is a design development that allows PHP applications to interact with the MySQL database in an early extension. The MySQL extension provides a process-oriented interface and is designed for MySQL4.1.3 or earlier versions. Therefore, although this extension can interact with MySQL4.1.3 or the updated database server, it does not support some of the features provided by the late MySQL server.
Note: If you are using MySQL4.1.3 or an updated version of the server, it is strongly recommended that you use the mysqli extension instead.
The source code for the MySQL extension is under PHP extension directory ext/mysql.
For more information on MySQL extensions, see MySQL.
What is the php mysqli extension?
Mysqli extensions, which we sometimes call MySQL enhanced extensions, can be used for new advanced features in MySQL4.1.3 or later versions. The mysqli extension is included in PHP 5 and later.
The mysqli extension has a number of advantages, as compared to the MySQL extension, the main improvements are:
Object-oriented interface
Prepared statement support (see MySQL related documentation for prepare)
Multi-statement execution support
Transaction support
Enhanced debugging capabilities
Embedded service Support
Note: If you use MySQL4.1.3 or later, it is strongly recommended that you use this extension.
It provides a process-oriented interface while providing an object-oriented interface.
The mysqli extension is built using the PHP extension framework and its source code is in the ext/mysqli of the PHP source directory.
For more information about the mysqli extension, see mysqli.
What is PDO?
PDO is actually the abbreviation of PHP database objects, which is a database abstraction layer specification in PHP application. PDO provides a unified API interface that allows your PHP application to not care about the type of database server system you want to connect to. In other words, if you use the PDO API, you can seamlessly switch the database server whenever you need it, such as from Firebird to MySQL, just to modify very little PHP code.
Examples of other database abstraction layers include JDBC in Java applications and DBI in Perl.
Of course, PDO also has its own advancement, such as a clean, simple, portable API, and its main drawback is that you cannot use the late MySQL server to provide all the database advanced features. For example, PDO does not allow multi-statement execution with MySQL support.
PDO is based on the PHP extension framework implementation, its source code in the PHP source directory ext/pdo.
For more information about PDO, see PDO.
What is the PDO MySQL drive?
The MySQL driver for PDO is not a set of APIs, at least from a PHP programmer's point of view. In fact, the PDO MySQL driver is in the PDO's own lower layer, providing specific MySQL functionality. The programmer calls the PDO API directly, and PDO uses the PDO MySQL driver to complete the interaction with the MySQL server side.
The MySQL driver for PDO is one of the many PDO drivers. Other available PDO drivers include firebird,postgresql and more.
The MySQL driver for PDO is based on the PHP extension framework. Its source code in the PHP source directory under the Ext/pdo_mysql. It does not expose the API to PHP programmers.
For more information on the MySQL extension of PDO, see MySQL (PDO).
what is the MySQL Native driver for php?
To interact with the MySQL database server, MySQL extensions, mysqli extensions, and PDO MySQL drivers use the underlying libraries that implement the necessary protocols. Previously, the available libraries were only the MySQL client library and Libmysql.
However, Libmysql contains interfaces that are not optimized for interaction with PHP applications, and Libmysql was earlier designed for C applications. For this reason, MySQL native driver Mysqlnd, as a modified version of Libmysql for PHP applications, was developed.
Both the mysql,mysqli and PDO MySQL drivers can be configured to use Libmysql or Mysqlnd respectively. MYSQLND, a library designed for use in PHP systems, has a much higher memory and speed than libmysql. I really want you to try these boosts.
Note:mysql native drivers can only be used on the MySQL server version for 4.1.3 and later versions.

MySQL native drivers are implemented based on the PHP extension framework. The source code is located under the PHP source directory ext/mysqlnd. It does not expose the interface to PHP programmers.

The above summary, such as

Other Message
1. What is MYSQLND driver?
Description of the PHP manual:
MySQL Native Driver is a replacement for the MySQL Client Library (libmysql).
MySQL Native Driver is part of the official PHP sources as of PHP 5.3.0.
Mysqldnd is the MySQL native driver shorthand, which is the MySQL driver connection code provided by the PHP source. Its purpose is to replace the old libmysql driver.
In the traditional way of installing PHP, we usually need to specify the following when compiling PHP:
This is actually the use of the official MySQL Libmysql driver, which is older driver, PHP 5.3 has not been recommended to use it, and the recommended use of MYSQLND.
2. What is the relationship between PDO and Mysqlnd, Libmysql?
PDO is an application-level abstraction class that requires MySQL-driven support for the underlying and MySQL server connection interactions. That means you can use PDO regardless of the driver you're using.
PDO is a PHP application Layer API interface, while MYSQLND, Libmysql is responsible for network protocol interaction with MySQL server (it does not provide PHP application layer API function)
3. Why use the Mysqlnd driver?
The official PHP manual describes:
The A.libmysql driver was written by MySQL AB (now Oracle) and published under the MySQL license license, so it is disabled by default in PHP.
The MYSQLND is a driver developed by PHP and released under the PHP License license Agreement, thus circumventing the issue of license agreement and copyright.
B. Because Mysqlnd is built into PHP source code, you do not need to pre-install MySQL server when compiling and installing PHP or provide MySQL client API (mysql_connect, PDO, Mysqli), which will reduce some of the workload.
C. Mysqlnd is a driver specifically written for PHP optimization, which uses the features of PHP itself, and is more advantageous in memory management and performance than Libmysql. The official PHP test was: Libmysql saved two copies of each record in memory, while Mysqlnd saved only one copy of the
D. Some new or enhanced features
Enhanced persistent connections
Introducing a unique function mysqli_fetch_all ()
Introduce some performance statistics functions mysqli_get_cache_stats (), Mysqli_get_client_stats (),
Mysqli_get_connection_stats (),
Using the above function, it is easy to analyze the performance bottleneck of MySQL query!
SSL support (valid starting from PHP 5.3.3)
Compression protocol Support
Named pipe support (PHP 5.4.0 starting to work)
4. How to use the MYSQLND driver (hint: If you use MYSQLND, you do not need to install MySQL beforehand)
When compiling PHP, modify the following key parameters to
Verification: If the client API Version:mysqlnd is found in the Phpinfo output MySQL entry, the MYSQLND driver installation is successful.
In addition, a list of compiled parameters for the PHP that can be used in the production environment is attached
./configure--prefix=/usr/local/php \
--with-iconv-dir \
--with-freetype-dir \
--with-jpeg-dir \
--with-png-dir \
--with-zlib \
--with-libxml-dir \
--enable-xml \
--disable-rpath \
--enable-bcmath \
--enable-sysvsem \
--enable-inline-optimization \
--with-curl \
--with-mcrypt \
--with-curlwrappers \
--enable-mbregex \
--enable-mbstring \
--with-mhash \
--enable-sockets \
--enable-zip \


PHP connection MySQL related functions

Process oriented:

Mysql_connect ()-Establish a database connection

mysql_select_db ()-Select database

mysql_query ()-Query MySQL

Mysql_fetch_row ()-Get and display data

Mysql_fetch_array ()-Get and display data

MYSQL_FETCH_ASSOC ()-Get and display data

Mysql_fetch_object ()-Get and display data

Mysql_num_rows ()-Number of records selected (This command is valid only for SELECT statements)

Mysql_affected_rows ()-Gets the number of record rows that were affected by the previous MySQL operation (This command returns the number of record rows affected by the Insert,update or DELETE query)

Mysql_close ()-Close database connection

Mysql_pconnect ()-Establish a database connection



In fact, this method is very similar to the normal method, just replaced the corresponding function with the object-oriented method

$handle = new Mysqli (...)

$handle->query (...)

$handle->fetch_row ();

Pdo method:

This is a popular way to connect a database at present. The advantage of this is that the basic operation of the database is the same as long as the data source is provided correctly. That is, the same piece of code can either interact with MySQL, interact with SQLite3, and, of course, interact with PostgreSQL, provided you have the right data source. Here's a look at the code that connects MySQL:

$dsn = ' mysql:host= '. $dbhost. '; Dbname= '. $dbdatabase. '; '
$DBH =new PDO ($dsn, $username, $userpass);

If it is SQLite3, use the following code directly:

$dsn = ' sqlite3: ' C:\sqlite\user.db ';
$DBH =new PDO ($DSN);

After the successful connection with the database, the following is only necessary to get data from the database or insert update data, the instance code is as follows:

$stmt = $dbh->query (' SELECT id,name from user ');
$row = $stmt->fetch ();


PHP and MySQL database related functions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.