MGA Logo

Web Development Tutorials



  
  

MySQL Functions

The MySQL database has become the world's most popular open source database because of its consistent fast performance, high reliability and ease of use. It's used in more than 6 million installations ranging from large corporations to specialized embedded applications on every continent in the world. PHP provides support for MySQL through an array of functions that can be used to manipulate MySQL data. The purpose of this tutorial is to introduce those functions commonly used in PHP data-driven applications for retrieving, updating, inserting, and deleting data.

The common PHP MySQL functions are described below:

  • mysqli_connect(MySQL server name,username,password) - opens a connection to a MySQL server.
  • mysqli_query(sql query) - sends a query to the currently active database.
  • mysqli_insert_id(connection id) – returns the auto increment value from the last query
  • mysqli_fetch_array(recordset id) - Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.
  • mysqli_num_rows(recordset id) - determines the number of rows contained in a recordset returned by the previous SQL SELECT operation. The function returns a value of FALSE if the operation fails.
  • mysqli_affected_rows(connection id) - determines the number of rows affected by the previous SQL INSERT, DELETE, or UPDATE operation. The function returns a value of -1 if the operation fails.
  • mysqli_multi_query(connection id, sql query) – sends multiple queries to the currently active database. Each query must be separated by a semicolon.
  • mysqli_more_results(connection id) –
  • mysqli_next_result(connection id) – retrieves the next record in a recordset
  • mysqli_begin_transaction(connection id) – starts a transaction
  • mysqli_commit(connection id) – commits the current transaction for the active database connection
  • mysqli_rollback(connection id) – rolls back the current transaction for the active database connection
  • mysqli_prepare(connection id, prepared statement) – prepares an SQL prepared statement for execution
  • mysqli_stmt_bind_param(prepared statement, bound value types, bound variables) - Binds variables to a prepared statement as parameters
  • mysqli_stmt_execute(prepared statement) – executes a prepared statement
  • mysqli_stmt_affected_rows(prepared statement) – returns the number of rows affected by an INSERT, UPDATE, or DELETE prepared statement
  • mysqli_stmt_num_rows(prepared statement) – returns the number of rows returned by a SELECT prepared statement
  • mysqli_stmt_close(prepared statement) – closes a prepared statement
  • mysqli_close(connection id) - closes MySQL connection.

These functions will be demonstrated throughout the tutorials in this section.

Before you can use these functions to create data-driven applications using MySQL, you will need appropriate access to a MySQL server. This includes a user account and password with permissions to the database and tables containing your data, and the MySQL server's host name or IP address.

One popular, free development tool that allows you to host MySQL and run your PHP applications locally on your computer is XAMPP. XAMPP provides all-in-one access to Apache webserver, the PHP engine, and the MySQL database. XAMPP also provides access to FTP, and email. XAMPP can be downloaded at Download XAMPP.

When working with MySQL server, it is necessary to use a GUI management tool, which provides an easy to use interface to the data. Popular tools include: PhpMyAdmin Download PhpMyAdmin and the MySQL Workbench Download MySQL Workbench. The examples provided in this tutorial will use the Workbench client tool.

Establishing a Connection to MySQL in PHP

The first step in creating a data-driven PHP application is to establish a connection to the MySQL server within the PHP application. This is accomplished using the mysqli_connection() function. While the mysqli_connection() function can be coded directly in any PHP page, in larger applications it is considered a best practice to code the connection within a stand-alone PHP page. This connection page can then be referenced by multiple PHP pages using the require() or include() functions. In the example below, a PHP page named mysqli_connect.php is created and contains the mysqli_connect() function needed to establish the connection to a MySQL database.

Mysqli_connect.php

<?php

DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'password');
DEFINE ('DB_HOST', 'database_hostname');
DEFINE ('DB_NAME', 'database_name');

//Establish a connection to the MySQL Database

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

?>

The mysqli_connect() function requires four parameters – MySQL host name, user name, password, and database name. In the example above, constants are defined and assigned the parameter values. The constants are then passed to the mysqli_connect() function. Once the connection is established, a reference variable, $conn, is used to store a reference to the connection. Unlike scalar and array variables, reference variables are not directly used within a program, but are often used as parameters for other functions. The $conn variable will be referenced later by other mysqli functions. If the connection fails, the function will return a connection error which can be referenced by mysqli_connect_error() function.

Once a connection is established with the MySQL database, the Structured Query Language (SQL) along with the mysqli functions can be used to peform operations such as data retrieval, inserts, deletes, and updates.


TOP | NEXT: Inserting Records