The most commonly used database system in PHP is MySQL. PHP allows the user to connect to this database and others as well after the installation of the appropriate one. However because MySQL is proprietary PHP has introduced MySQL Lite (MySQLi) which essentially is same as MySQL but smaller, PHP optimized database. There are other databases that can be installed and made working with the PHP language.

Following two structures are important to know:

  • MySQL It is a web database system that runs on a server. It is free to download and uses standard SQL. MySQL is a database that stores tables and uses functionality to fetch and manipulate data values. MySQLi is an improved version of MySQL 4.1 and above.
  • PDO is a PHP Data Object (PDO) is an interface to access a database in PHP. PDO drivers are available to access a database server by using functions and database specific features. PDO works with twelve different database systems while MySQLi works with MySQL only. 

Database interface

PHP allows the user to access databases by creating connection. It can connect to multiple servers and manipulate data values. There are many functions to manipulate these data values including update a value, delete a value, and insert a value and so on. Following examples illustrates how to access the data using MySQLi.

Example how to establish connection with MySQLi database server

<?php
    $server = "server IP address";
    $uname = "username";
    $passw = "password";
    $conn = mysqli_connect($server, $uname, $passw); // create connection
    if (!$conn) die ("Connection failed:" . mysqli_connect_error()); // error if not connected
?>

Example how to create MySQLi database with PHP

<?php
    // create connection
    $server = "server IP address";
    $uname = "username";
    $passw = "password";
    $conn = mysqli_connect($server, $uname, $passw); 
    if (!$conn) die ("Connection failed:" . mysqli_connect_error()); // error if not connected

    // create database
    $sql = "CREATE DATABASE database_name";
    if (mySQLi_query($conn, $sql)) {
        echo "Database created successfully...";
    }
    else {
        echo "Problems creating the database!";
    }
?>  

Example how to create MySQLi table with PHP

<?php
    // create connection
    $server = "server IP address";
    $uname = "username";
    $passw = "password";
    $conn = mysqli_connect($server, $uname, $passw); 
    if (!$conn) die ("Connection failed:" . mysqli_connect_error()); // error if not connected

    // create table
    $sql = "CREATE TABLE table_name (
    id INT(6) UNSIGNED AUTOINCREMENT PRIMARY KEY,
    firstname VARCHAR(64) NOT NULL,
    lastname VARCHAR(64) NOT NULL";
    if (mySQLi_query($conn, $sql)) {
        echo "Table created successfully...";
    }
    else {
        echo "Problems creating the table!";
    }
?>

Example how to insert, update and delete records in MySQLi with PHP

<?php
    // create connection
    $server = "server IP address";
    $uname = "username";
    $passw = "password";
    $conn = mysqli_connect($server, $uname, $passw); 
    if (!$conn) die ("Connection failed:" . mysqli_connect_error()); // error if not connected

    // insert data
    $sql = "INSERT INTO table_name (firstname, lastname) VALUES ('Albert', 'Eintstein')";
    if (mySQLi_query($conn, $sql)) {
        echo "Record inserted successfully...";
    }
    else {
        echo "Problems inserting!";
    }
    
    // update data
    $sql = "UPDATE table_name SET lastname='Frankenstein' WHERE id=1";
    if (mySQLi_query($conn, $sql)) {
        echo "Record updated successfully...";
    }
    else {
        echo "Problems updating!";
    }
    
     // delete data
    $sql = "DELETE FROM table_name WHERE id=1";
    if (mySQLi_query($conn, $sql)) {
        echo "Record deleted successfully...";
    }
    else {
        echo "Problems deleting!";
    }
?>

 

Detailed MySQL tutorial can be found here...

 

›› go to examples ››