How PHP Connect to MySQL Tutorial with Example

How PHP Connect to MySQL Database Tutorial with Example

Learn How php connect to mysql with example
PHP Connect to MySQL

PHP is a server-side scripting language that lets web developers insert instructions into your web pages that your web server (Apache, IIS, etc)  will execute before it sends back those pages to browsers (clients) that request them. This is how dynamic websites and web applications are created in PHP using MySQL database.

Today, PHP has become the first choice of web developers for developing from simple websites to enterprise level cloud-based web portals. So, it becomes necessary to understand how PHP connect to MySQL database server and perform common database operations (INSERT, SELECT QUERY, UPDATE, DELETE, etc) on the selected database.

This tutorial will acquaint you with complete steps of connecting to MySQL database using PHP code with example.

Introduction – PHP connect to MySQL Database

Creating dynamic websites is the need of every web developer. When talking about dynamic websites, the first thing comes to our mind is reading and writing to/from the databases.

PHP extends support for several databases.  MySQL is the most popular and widely used Relational Database Management System (RDBMS). PHP based websites make use of MySQL to develop dynamic web applications that have the capacity to scale up from medium to enterprise level software.

Hence, accessing the Database is a very important task of web development in PHP. PHP Connect to MySQL is the backbone of any dynamic website, e-commerce, web portal or any modern application.

MySQL is a robust, simple and optimized DB which can handle the huge volume of data without compromising on quality.

In this tutorial, We will explain and show how does PHP connect to MySQL database.

Let’s quickly understand the basics of MySQL database server.

What is MySQL?

MySQL is a database server. A database server is a program that can store a large volume of information in an organized format that’s easily accessible through programming languages like PHP.

MySQL database is a structured collection of records or data stored in a computer system and organized in
such a way that it can be quickly searched and information can be rapidly retrieved.

MySQL is a very simple plain English based interface for database developers. MySQL database consists of tables which further consists of records or rows. The columns are called fields that make rows.

Learn Laravel Tutorials:

If you are a beginner to Laravel Framework, then we recommend you to read the following topics:

Important Database Terms To Remember

While designing and developing dynamic PHP web applications using MySQL, remember the following fundamental terms:

Database – The overall container for a collection of MySQL data
Table – A sub-container within a database that stores the actual data
Row – A single record within a table, which may contain several fields
Column – The name of a field within a row

What databases does PHP support?

PHP supports the following list of databases:
1. MySQL.
2. PostgreSQL
3. SYBASE
4. ORACLE
5. IBM DB2

Also, read the following guides:

 

Which database is best for Web application?

As of now, MySQL is the most popular among web developers while developing web applications.

Different ways of Connecting to MySQL Database using PHP

To retrieve, query or insert the data from the MySQL database, PHP provides the following two ways to make database connections to MySQL:
Method1 – PHP Connect to MySQL Database using MySQLi (Here ‘i’ stands for improved version).
Method2 – PHP Connect to MySQL Database using PDO

Method1 – PHP Connect to MySQL using MySQLi

PHP MySQLi = PHP MySQL Improved! Using this method, you will learn how to fetch data from database in php using mysqli.

This is the most popular method of connecting to Mysql using PHP. The PHP MySQLi functions provide you access to MySQL database server.

The process of using MySQL with PHP is as follows:
1. Connect to MySQL and select the database to use.
2. Build a query string.
3. Perform the query.
4. Retrieve the results and output them to a web page.
5. Repeat steps 2 to 4 until all desired data has been retrieved.
6. Disconnect from MySQL.

PHP connect to MySQL Example using MySQLi 

First of all, make sure to create a database using the phpMyAdmin panel at localhost.
Refer these steps to create a database in phpMyAdmin

1. Creating PHP database Configuration file

Most of the websites developed with PHP contain multiple program files that will require access to MySQL and will thus need the login and password details. Therefore, it’s sensible to create a single PHP file to store these database configurations and then include that file wherever it’s needed.

Example – Database Connection in PHP with MySQL in xampp code

Example – PHP connect to MySQL – database.php

<?php // database.php
$host            = 'localhost';
$database    = 'myDatabase';
$username  = 'username';
$passsword = 'password';
?>

 

2. Connecting to a MySQL Database in PHP
Now that you have the database.php file saved, you can include it in any PHP files that will need to access the database by using the require_once statement.

Following code shows PHP connecting to a MySQL server with using MySQLi method:

<?php

require_once 'database.php';
$connection = new mysqli($host, $username, $password, $database);

if ($connection ->connect_error) die($connection ->connect_error); ?>

This example creates a new object called $conn by calling a new instance of the mysqli method which is called as PHP mysqli connect, passing all the values retrieved from the login.php file. Error checking is achieved by referencing the $connection ->connect_error property. php MySQL example. The $connection object is used in the following examples to access the MySQL database.

3. Building and executing a MySQL query in PHP
Sending a query to MySQL from PHP is as simple as issuing it using the query method of a connection object. 

Example Querying a MySQL database in PHP with mysqli

<?php

$query = "SELECT * FROM posts";
$result = $connection->query($query);
if (!$result) die($connection->error);
?>

4. Fetching MySQL Query Results
After you have an object returned in $result, you can use it to extract the data you want, one item at a time, using the fetch_assoc method of the object. Following is PHP code to retrieve data from a MySQL database and display in HTML forms.

Example php mysqli query

<?php // query.php
require_once 'database.php';
$connection = new mysqli($hostname, $username, $password, $database);
if ($connection->connect_error) die($connection->connect_error);
$query = "SELECT * FROM classics";
$result = $conn->query($query);
if (!$result) die($connection->error);
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$result->data_seek($j);
echo 'Author: ' . $result->fetch_assoc()['author'] . '<br>';
$result->data_seek($j);
echo 'Title: ' . $result->fetch_assoc()['title'] . '<br>';
$result->data_seek($j);
echo 'Category: ' . $result->fetch_assoc()['category'] . '<br>';
$result->data_seek($j);
echo 'Year: ' . $result->fetch_assoc()['year'] . '<br>';
$result->data_seek($j);
echo 'ISBN: ' . $result->fetch_assoc()['isbn'] . '<br><br>';
}
$result->close();
$conn->close();
?>

So, these are necessory steps for making php database connection example program

Fetching a single row at a time in PHP MySQL

<?php //fetchrow.php
require_once 'login.php';
$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
$query = "SELECT * FROM classics";
$result = $conn->query($query);
if (!$result) die($conn->error);
$rows = $result->num_rows;
for ($j = 0 ; $j < $rows ; ++$j)
{
$result->data_seek($j);
$row = $result->fetch_array(MYSQLI_ASSOC);
echo 'Author: ' . $row['author'] . '<br>';
echo 'Title: ' . $row['title'] . '<br>';
echo 'Category: ' . $row['category'] . '<br>';
echo 'Year: ' . $row['year'] . '<br>';
echo 'ISBN: ' . $row['isbn'] . '<br><br>';
}
$result->close();
$conn->close();
?>

PHP Close MySQL Connection
PHP will eventually return the memory it has allocated for objects after you have finished with the script, so in small scripts, you don’t usually need to worry about releasing memory yourself. However, if you’re allocating a lot of result objects or fetching large amounts of data, it can be a good idea to free the memory you have been using
to prevent problems later in your script.

$result->close(); $conn->close();

PHP 5 MySQLi Functions

mysqli_affected_rows() Returns the number of affected rows in the previous MySQL operation
mysqli_autocommit() Turns on or off auto-committing database modifications
mysqli_change_user() Changes the user of the specified database connection
mysqli_character_set_name() Returns the default character set for the database connection
mysqli_close() Closes a previously opened database connection
mysqli_commit() Commits the current transaction

PHP MySQli Error Functions

mysqli_errno() Returns the last error code for the most recent function call
mysqli_error_list() Returns a list of errors for the most recent function call
mysqli_error() Returns the last error description for the most recent function call

PHP MySQli Fetch Functions

mysqli_fetch_all() Fetches all result rows as an associative array, a numeric array, or both
mysqli_fetch_array() Fetches a result row as an associative, a numeric array, or both

mysqli_fetch_assoc() Fetches a result row as an associative array
mysqli_fetch_field_direct() Returns meta-data for a single field in the result set, as an object
mysqli_fetch_field() Returns the next field in the result set, as an object
mysqli_fetch_fields() Returns an array of objects that represent the fields in a result set
mysqli_fetch_lengths() Returns the lengths of the columns of the current row in the result set
mysqli_fetch_object() Returns the current row of a result set, as an object
mysqli_fetch_row() Fetches one row from a result-set and returns it as an enumerated array

PHP MySQli Connect Functions

mysqli_connect_errno() Returns the error code from the last connection error
mysqli_connect_error() Returns the error description from the last connection error
mysqli_connect() Opens a new connection to the MySQL server

mysqli_data_seek() Adjusts the result pointer to an arbitrary row in the result-set
mysqli_debug() Performs debugging operations
mysqli_dump_debug_info() Dumps debugging info into the log

PHP MySQli Field Functions

mysqli_field_count() Returns the number of columns for the most recent query
mysqli_field_seek() Sets the field cursor to the given field offset
mysqli_field_tell() Returns the position of the field cursor
mysqli_free_result() Frees the memory associated with a result

PHP MySQli Get Functions

mysqli_get_charset() Returns a character set object
mysqli_get_client_info() Returns the MySQL client library version
mysqli_get_client_stats() Returns statistics about client per-process
mysqli_get_client_version() Returns the MySQL client library version as an integer
mysqli_get_connection_stats() Returns statistics about the client connection
mysqli_get_host_info() Returns the MySQL server hostname and the connection type
mysqli_get_proto_info() Returns the MySQL protocol version
mysqli_get_server_info() Returns the MySQL server version
mysqli_get_server_version() Returns the MySQL server version as an integer

PHP MySQli Miscellaneous Functions
mysqli_info() Returns information about the most recently executed query
mysqli_init() Initializes MySQLi and returns a resource for use with mysqli_real_connect()
mysqli_insert_id() Returns the auto-generated id used in the last query
mysqli_kill() Asks the server to kill a MySQL thread
mysqli_more_results() Checks if there are more results from a multi query
mysqli_multi_query() Performs one or more queries on the database
mysqli_next_result() Prepares the next result set from mysqli_multi_query()
mysqli_num_fields() Returns the number of fields in a result set
mysqli_num_rows() Returns the number of rows in a result set
mysqli_options() Sets extra connect options and affect behavior for a connection
mysqli_ping() Pings a server connection, or tries to reconnect if the connection has gone down
mysqli_prepare() Prepares an SQL statement for execution
mysqli_query() Performs a query against the database
mysqli_real_connect() Opens a new connection to the MySQL server
mysqli_real_escape_string() Escapes special characters in a string for use in an SQL statement
mysqli_real_query() Executes an SQL query
mysqli_reap_async_query() Returns the result from async query
mysqli_refresh() Refreshes tables or caches, or resets the replication server information
mysqli_rollback() Rolls back the current transaction for the database
mysqli_select_db() Changes the default database for the connection

PHP MySQli Set Functions

mysqli_set_charset() Sets the default client character set
mysqli_set_local_infile_default() Unsets user defined handler for load local infile command
mysqli_set_local_infile_handler() Set callback function for LOAD DATA LOCAL INFILE command
mysqli_sqlstate() Returns the SQLSTATE error code for the last MySQL operation
mysqli_ssl_set() Used to establish secure connections using SSL
mysqli_stat() Returns the current system status
mysqli_stmt_init() Initializes a statement and returns an object for use with mysqli_stmt_prepare()
mysqli_store_result() Transfers a result set from the last query
mysqli_thread_id() Returns the thread ID for the current connection
mysqli_thread_safe() Returns whether the client library is compiled as thread-safe
mysqli_use_result() Initiates the retrieval of a result set from the last query executed using the mysqli_real_query()
mysqli_warning_count() Returns the number of warnings from the last query in the connection

PDO Method  – PHP connect to MySQL using Using PDO

PDO is a PHP extension for connecting mysql databases. Its another method to make connections with db, inseret data, retrieve data, etc. PDO stands for PHP Data Objects. PHP PDO is very clean and consistent way to access databases.

Read PHP Official for PDO.

In simple terms, PDO is a set of unified API’s which allows developers to connect to different databases with the same database access layer.

Enable PDO extension in php.ini
To be able to use PDO connections, you need to enable “php_pdo.dll and php_pdo_mysql.dll exist in the php/ext directory, un-comment or add the appropriate lines in php.ini, and restart the web server.”

Conclusion:

Now that we have learned how PHP Connect to MySQL database server. And, how web developers can make connections, read/write from the MySQL database to create a dynamic web application in PHP.

Leave a Reply

Your email address will not be published. Required fields are marked *