MySQL - PHP Syntax



Various programming languages like PERL, C, C++, JAVA, PHP, etc. are used as client programs to request query executions on a MySQL Server. Out of these languages, PHP is the most popular one because of its web application development capabilities.

A PHP library is like a toolbox for web developers, providing pre-built functions and code snippets to simplify common tasks. It saves time and effort by offering ready-made solutions for tasks such as handling databases, processing forms, and managing files. Developers can easily include these libraries in their PHP projects to boost efficiency and create robust web applications.

PHP MySQLi Library

The MySQL PHP connector, often referred to as MySQLi, enables communication between PHP scripts and MySQL databases. It provides a set of functions and methods that allow PHP applications to connect, query, and manipulate data in MySQL databases, providing efficient and secure database interactions in PHP web development.

This tutorial focuses on using MySQL in a various environments. If you are interested in MySQL with PERL, then you can consider reading the PERL Tutorial.

How to Install MySQLi

To install MySQLi on Windows, you can follow these general steps −

Download PHP:

  • Download the latest version of PHP from the official PHP website (https://www.php.net/downloads.php).
  • Choose the Windows version that matches your system architecture (e.g., 32-bit or 64-bit).
  • Download the ZIP file for the Thread Safe or Non-Thread Safe version, depending on your needs.

Extract the ZIP File:

  • Extract the contents of the downloaded ZIP file to a location on your computer (e.g., C:\php).

Configure PHP:

  • In the extracted PHP folder, find the "php.ini-development" file.
  • Copy and rename it to "php.ini".
  • Open "php.ini" in a text editor (e.g., Notepad) and find the line: ";extension=mysqli". Remove the semicolon (;) at the beginning of the line to uncomment it: "extension=mysqli".
  • Save the php.ini file.

Set Environment Variables:

  • Add the PHP installation directory to the system's PATH environment variable. This allows you to run PHP from any command prompt.
  • To do this, right-click on "This PC" or "Computer" on your desktop or in File Explorer, select "Properties," and click on "Advanced system settings." Then, click on the "Environment Variables" button. In the "System variables" section, select the "Path" variable and click "Edit." Add the path to your PHP installation directory (e.g., C:\php).

Restart your Web Server:

  • If you are using a web server like Apache or Nginx, restart it to apply the changes.

Verify Installation:

  • Create a PHP file with the following content and save it in your web server's document root (e.g., C:\Apache24\htdocs for Apache):
<?php
phpinfo();
?>
  • Open the file in your web browser and search for "mysqli" to verify that the MySQLi extension is now enabled.
  • PHP Functions to Access MySQL

    PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database −

    S.No Function & Description
    1

    mysqli_connect()

    Establishes a connection to the MySQL server.

    2

    mysqli_query()

    Performs a query on the database.

    3

    mysqli_fetch_assoc()

    Fetches a result row as an associative array.

    4

    mysqli_fetch_array()

    Fetches a result row as an associative array, a numeric array, or both.

    5

    mysqli_close()

    Closes a previously opened database connection.

    6

    mysqli_num_rows()

    Gets the number of rows in a result.

    7

    mysqli_error()

    Returns a string description of the last error.

    8

    mysqli_prepare()

    Used for prepared statements to help prevent SQL injection.

    9

    mysqli_fetch_row()

    Fetches a result row as an enumerated array.

    10

    mysqli_insert_id()

    Gets the ID generated in the last query.

    Basic Example

    Following are the steps to connect to a MySQL database, execute a query, process the results, and close the connection using PHP and MySQLi −

    • Define the parameters needed to connect to your MySQL database, such as '$dbhost' (host name), '$dbuser' (username), '$dbpass' (password), and '$dbname' (database name).
    • Create a new instance of the 'mysqli' class to establish a connection to the MySQL database.
    • Use the 'query' method of the 'mysqli' object to execute a MySQL query.
    • Fetch and process the results returned by the query.
    • Close the connection to the MySQL database when you are done.

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

    <html>
       <head>
          <title>PHP with MySQL</title>
       </head>
       
       <body>
          <?php
             // Include database connection parameters
             $dbhost = "localhost";
             $dbuser = "your_username";
             $dbpass = "your_password";
             $dbname = "your_database";
    
             // Establish a connection to MySQL
             $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
             if ($mysqli->connect_error) {
                 die("Connection failed: " . $mysqli->connect_error);
             }
    
             // Execute a MySQL query
             $sql = "SELECT * FROM your_table";
             $result = $mysqli->query($sql);
    
             if (!$result) {
                 die("Error: " . $mysqli->error);
             }
    
             // Process the query results
             while ($row = $result->fetch_assoc()) {
                 // Process each row of data
                 echo "ID: " . $row["id"] . " Name: " . $row["name"] . "<br>";
             }
    
             // Close the database connection
             $mysqli->close();
          ?>
       </body>
    </html>
    
    Advertisements