PHP & MySQL - Quick Guide

PHP & MySQL - Overview

MySQL works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.

PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database. You would require to call the PHP functions in the same way you call any other PHP function.

The PHP functions for use with MySQL have the following general format −

mysql_function(value,value,...);

The second part of the function name is specific to the function, usually a word that describes what the function does. The following are two of the functions, which we will use in our tutorial −

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

The following example shows a generic syntax of PHP to call any MySQL function.

<html>
   <head>
      <title>PHP with MySQL</title>
   </head>
   
   <body>
      <?php
         $retval = mysql_function(value, [value,...]);
         if( !$retval ) {
            die ( "Error: a related error message" );
         }
         // Otherwise MySQL or PHP Statements
      ?>
   </body>
</html>

Starting from the next chapter, we will see all the important MySQL functionality along with PHP.

PHP & MySQL - Environment Setup

In order to develop and run PHP Web pages, three vital components need to be installed on your computer system.

  • Web Server − PHP works with virtually all Web Server software, including Microsoft's Internet Information Server (IIS) but most often used is Apache Server. Download Apache for free here − https://httpd.apache.org/download.cgi

  • Database − PHP works with virtually all database software, including Oracle and Sybase but most commonly used is MySQL database. Download MySQL for free here − https://www.mysql.com/downloads/

  • PHP Parser − In order to process PHP script instructions, a parser must be installed to generate HTML output that can be sent to the Web Browser. This tutorial will guide you how to install PHP parser on your computer.

PHP Parser Installation

Before you proceed, it is important to make sure that you have proper environment setup on your machine to develop your web programs using PHP. Store the following php file in Apache's htdocs folder.

phpinfo.php

<?php
   phpinfo();
?>

Type the following address into your browser's address box.

http://127.0.0.1/phpinfo.php

If this displays a page showing your PHP installation related information, then it means you have PHP and Webserver installed properly. Otherwise, you have to follow the given procedure to install PHP on your computer.

This section will guide you to install and configure PHP over the following four platforms −

Apache Configuration

If you are using Apache as a Web Server, then this section will guide you to edit Apache Configuration Files.

Check here − PHP Configuration in Apache Server

PHP.INI File Configuration

The PHP configuration file, php.ini, is the final and immediate way to affect PHP's functionality.

Check here − PHP.INI File Configuration

Windows IIS Configuration

To configure IIS on your Windows machine, you can refer your IIS Reference Manual shipped along with IIS.

Install MySQL Database

The most important thing you will need, of course is an actual running database with a table that you can query and modify.

  • MySQL DB − MySQL is an open source database. You can download it from MySQL Official Site. We recommend downloading the full Windows installation.

  • In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools that will make your development much easier.

  • Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-5.1.8.

  • Accordingly, set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation.

Set Database Credential

When we install MySQL database, its administrator ID is set to root and it gives provision to set a password of your choice.

Using root ID and password you can either create another user ID and password, or you can use root ID and password for your JDBC application.

There are various database operations like database creation and deletion, which would need administrator ID and password.

For rest of the JDBC tutorial, we would use MySQL Database with guest as ID and guest123 as password.

If you do not have sufficient privilege to create new users, then you can ask your Database Administrator (DBA) to create a user ID and password for you.

Create Database

To create the TUTORIALSPOINT database, use the following steps −

Step 1

Open a Command Prompt and change to the installation directory as follows −

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Note − The path to mysqld.exe may vary depending on the install location of MySQL on your system. You can also check documentation on how to start and stop your database server.

Step 2

Start the database server by executing the following command, if it is already not running.

C:\Program Files\MySQL\bin>mysqld
C:\Program Files\MySQL\bin>

Step 3

Create the TUTORIALSPOINT database by executing the following command −

C:\Program Files\MySQL\bin> mysqladmin create TUTORIALSPOINT -u guest -p
Enter password: ********
C:\Program Files\MySQL\bin>

Create Table

To create the Employees table in TUTORIALSPOINT database, use the following steps −

Step 1

Open a Command Prompt and change to the installation directory as follows −

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2

Login to the database as follows −

C:\Program Files\MySQL\bin>mysql -u guest -p
Enter password: ********
mysql>

Step 3

Create the table Employees as follows −

mysql> use TUTORIALSPOINT;
mysql> create table Employees
   -> (
   -> id int not null,
   -> age int not null,
   -> first varchar (255),
   -> last varchar (255)
   -> );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

Finally you create few records in Employee table as follows −

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)

mysql>

For a complete understanding on MySQL database, study the MySQL Tutorial.

Now you are ready to start experimenting with PHP.

PHP & MySQL - Connect Database Example

PHP provides mysqli contruct or mysqli_connect() function to open a database connection. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.

Syntax

$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

Sr.No. Parameter & Description
1

$host

Optional − The host name running the database server. If not specified, then the default value will be localhost:3306.
2

$username

Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process.
3

$passwd

Optional − The password of the user accessing the database. If not specified, then the default will be an empty password.
4

$dbName

Optional − database name on which query is to be performed.
5

$port

Optional − the port number to attempt to connect to the MySQL server..
6

$socket

Optional − socket or named pipe that should be used.

You can disconnect from the MySQL database anytime using another PHP function close().

Syntax

$mysqli->close();

Example

Try the following example to connect to a MySQL server −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.

PHP & MySQL - Create Database Example

PHP uses mysqli query() or mysql_query() function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to create a MySQL database.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to create a database −

Copy and paste the following example as mysql_example.php −

<html>
   <head><title>Creating MySQL Database</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query("CREATE DATABASE TUTORIALS")) {
            printf("Database TUTORIALS created successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not create database: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Database TUTORIALS created successfully.

PHP & MySQL - Drop Database Example

PHP uses mysqli query() or mysql_query() function to drop a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to drop a MySQL database.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to drop a database −

Copy and paste the following example as mysql_example.php −

<html>
   <head><title>Dropping MySQL Database</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query("Drop DATABASE TUTORIALS")) {
            printf("Database TUTORIALS dropped successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not drop database: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Database TUTORIALS dropped successfully.

PHP & MySQL - Select Database Example

PHP uses mysqli_select_db function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

mysqli_select_db ( mysqli $link , string $dbname ) : bool

Sr.No. Parameter & Description
1

$link

Required - A link identifier returned by mysqli_connect() or mysqli_init().
2

$dbname

Required - Name of the database to be connected.

Example

Try the following example to select a database −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die('Could not connect: ' . mysqli_error($conn));
         }
         echo 'Connected successfully<br />';
         $retval = mysqli_select_db( $conn, 'TUTORIALS' );

         if(! $retval ) {
            die('Could not select database: ' . mysqli_error($conn));
         }
         echo "Database TUTORIALS selected successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Database TUTORIALS selected successfully

PHP & MySQL - Create Table

PHP uses mysqli query() or mysql_query() function to create a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to create a MySQL table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to create a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         if ($mysqli->query($sql)) {
            printf("Table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not create table: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Table tutorials_tbl created successfully.

PHP & MySQL - Drop Table Example

PHP uses mysqli query() or mysql_query() function to drop a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to drop a table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to drop a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head><title>Dropping MySQL Table</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query("Drop Table tutorials_tbl")) {
            printf("Table tutorials_tbl dropped successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not drop table: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Table tutorials_tbl dropped successfully.

PHP & MySQL - Insert Records Example

PHP uses mysqli_query function to insert records in table. This function takes three parameters and returns TRUE on success or FALSE on failure.

Syntax

mysqli_query ( mysqli $link, string $query, int $resultmode = MYSQLI_STORE_RESULT ) : mixed

Sr.No. Parameter & Description
1

$link

Required - A link identifier returned by mysqli_connect() or mysqli_init().
2

$query

Required - SQL query to drop a table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

This example will take three parameters from the user and will insert them into the MySQL table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Add New Record in MySQL Database</title>
   </head>
   <body>
      <?php
         if(isset($_POST['add'])) {
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'root@123';
            $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
            if(! $conn ) {
               die('Could not connect: ' . mysqli_error($conn));
            }
            if(! get_magic_quotes_gpc() ) {
               $tutorial_title = addslashes ($_POST['tutorial_title']);
               $tutorial_author = addslashes ($_POST['tutorial_author']);
            } else {
               $tutorial_title = $_POST['tutorial_title'];
               $tutorial_author = $_POST['tutorial_author'];
            }
            $submission_date = $_POST['submission_date'];
            $sql = "INSERT INTO tutorials_tbl ".
               "(tutorial_title,tutorial_author, submission_date) "."VALUES ".
               "('$tutorial_title','$tutorial_author','$submission_date')";
            mysqli_select_db( $conn, 'TUTORIALS' );
            $retval = mysqli_query( $conn, $sql );
         
            if(! $retval ) {
               die('Could not enter data: ' . mysqli_error($conn));
            }
            echo "Entered data successfully\n";
            mysqli_close($conn);
         } else {
      ?>  
      <form method = "post" action = "<?php $_PHP_SELF ?>">
         <table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
            <tr>
               <td width = "250">Tutorial Title</td>
               <td><input name = "tutorial_title" type = "text" id = "tutorial_title"></td>
            </tr>         
            <tr>
               <td width = "250">Tutorial Author</td>
               <td><input name = "tutorial_author" type = "text" id = "tutorial_author"></td>
            </tr>         
            <tr>
               <td width = "250">Submission Date [   yyyy-mm-dd ]</td>
               <td><input name = "submission_date" type = "text" id = "submission_date"></td>
            </tr>      
            <tr>
               <td width = "250"> </td>
               <td></td>
            </tr>         
            <tr>
               <td width = "250"> </td>
               <td><input name = "add" type = "submit" id = "add"  value = "Add Tutorial"></td>
            </tr>
         </table>
      </form>
   <?php
      }
   ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.

Entered data successfully

While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.

You can put many validations around to check if the entered data is correct or not and can take the appropriate action.

PHP & MySQL - Select Records Example

You can use the same SQL SELECT command into a PHP function mysql_query(). This function is used to execute the SQL command and then later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns the row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

The following program is a simple example which will show how to fetch / display records from the tutorials_tbl table.

Example

The following code block will display all the records from the tutorials_tbl table.

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Selecting Records</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die('Could not connect: ' . mysqli_error($conn));
         }
         echo 'Connected successfully<br />';
         
         mysqli_select_db( $conn, 'TUTORIALS' );
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
         $retval = mysqli_query( $conn, $sql );
         if(! $retval ) {
            die('Could not get data: ' . mysqli_error($conn));
         }
         
         while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) {
            echo "Tutorial ID :{$row['tutorial_id']}  ".
               "Title: {$row['tutorial_title']} ".
               "Author: {$row['tutorial_author']} ".
               "Submission Date : {$row['submission_date']} ".
               "--------------------------------";
         } 
         echo "Fetched data successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.

Entered data successfully

While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.

You can put many validations around to check if the entered data is correct or not and can take the appropriate action.

PHP & MySQL - Update Records Example

PHP uses mysqli query() or mysql_query() function to update records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to update records in a MySQL table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to update a record in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Updating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query('UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 4')) {
            printf("Table tutorials_tbl updated successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not update table: %s<br />", $mysqli->error);
         }
   
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
		 
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we've entered multiple records in the table before running the select script.

Connected successfully.
Table tutorials_tbl updated successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

PHP & MySQL - Delete Records Example

PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to delete records in a MySQL table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to delete a record in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Deleting MySQL Table record</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         if ($mysqli->query('DELETE FROM tutorials_tbl where tutorial_id = 4')) {
            printf("Table tutorials_tbl record deleted successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not delete record from table: %s<br />", $mysqli->error);
         }
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
		 
         $result = $mysqli->query($sql);
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we've entered multiple records in the table before running the select script.

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

PHP & MySQL - Where Clause Example

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using where clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to select records in a MySQL table using Where Clause.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using where clause in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Where Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author = "Mahesh"';
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we've entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

PHP & MySQL - Like Clause Example

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using Like clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to select records in a MySQL table using Like Clause.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using like clause in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Like Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author like "Mah%"';
         $result = $mysqli->query($sql);
         
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we've entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

PHP & MySQL - Sorting Data Example

PHP uses mysqli query() or mysql_query() function to get sorted records from a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to get sorted records from a table.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to get sorted records from a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Sorting MySQL Table records</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl order by tutorial_title asc";
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

PHP & MySQL - Using Joins Example

PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No. Parameter & Description
1

$sql

Required - SQL query to get records from multiple tables using Join.
2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

First create a table in MySQL using following script and insert two records.

create table tcount_tbl(
   tutorial_author VARCHAR(40) NOT NULL,
   tutorial_count int
);

insert into tcount_tbl values('Mahesh', 3);
insert into tcount_tbl values('Suresh', 1);

Example

Try the following example to get records from a two tables using Join. −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using joins on MySQL Tables</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         
         $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
				FROM tutorials_tbl a, tcount_tbl b
				WHERE a.tutorial_author = b.tutorial_author';
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Author: %s, Count: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_author"], 
                  $row["tutorial_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Id: 1, Author: Mahesh, Count: 3
Id: 2, Author: Mahesh, Count: 3
Id: 3, Author: Mahesh, Count: 3
Id: 5, Author: Suresh, Count: 1

PHP & MySQL - Handling NULL Example

You can use the if...else condition to prepare a query based on the NULL value.

The following example takes the tutorial_count from outside and then compares it with the value available in the table.

Example

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Handling NULL</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         if( isset($tutorial_count )) {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count = ' + $tutorial_count;
         } else {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count IS NULL';
         }
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Author: %s, Count: %d <br />",
                  $row["tutorial_author"], 
                  $row["tutorial_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
No record found.

PHP & MySQL - Database Info Example

There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.

Sr.No. Command & Description
1

SELECT VERSION( )

Server version string
2

SELECT DATABASE( )

Current database name (empty if none)
3

SELECT USER( )

Current username
4

SHOW STATUS

Server status indicators
5

SHOW VARIABLES

Server configuration variables

Example

Try the following example to get database info −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
		 
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Default database is tutorials
Advertisements