Databases In PHP

A Few Words First:

Since version 2, PHP can connect to databases. The two methods to connect to a database in PHP are PDO and MySQLi.

Possible Databases:

The main databases compatible with PHP are:
- MongoDB
- MS SQL
- PostgreSQL
- SQLite

There are more PHP compatible databases:
- CUBRID
- DB++ (Experimental)
- Firebird/InterBase
- IBM DB2/Cloudscape/Apache Derby
- MaxDB
- mSQL
- Oracle OCI8
- Paradox (Experimental)
- SQLSRV (MS SQL/SQL Azure)
- Sybase
- Tokyo Tyrant

There are also more databases, which are compatible through the PHP Extension Community Library - PECL:
- dBase
- filePro
- FrontBase
- Informix (IDS)/Universal Server
- Ingres/EDBC/Enterprise Access Gateways

PDO vs. MySQLi:

Common features to both PDO and MySQLi:
- Object-Orientated.
- Supports Charsets.
- Multiple Statements.
- Server-Side Prepared Statements.
- Stored Procedures.

Features unique to PDO:
- Supports Client-Side Prepared Statements.
- Connects to all the Supported Databases in PHP.

Features unique to MySQL:
- Can Use Procedural Calls.
- Connects to MySQL, MaxDB, MariaDB.

Keep in mind: If you plan on switching to a different database then PDO is more right for you, but if you plan to keep using MySQL with MaxDB/MariaDB then MySQLi is better for you. Also, PDO is much newer than the MySQLi, which has been around for a long time.

Setting Up MySQL In Linux:

First, we need to check if MySQL is already installed on our linux machine. In order to do so, we’ll type the following commands in the Linux Terminal:

sudo apt-get update
mysql -V

If a version number is returned, then MySQL is already installed. If nothing or an error about the command not found is returned, then MySQL isn’t installed.

In order to install MySQL, we’ll type the following commands in the Linux Terminal:

sudo apt-get install mysql-server
sudo apt-get install phpmyadmin

If you are asked to enter a password during the installation process, do so.

Also, during the phpmyadmin installation process you will be asked which kind of server you’d like to use - choose ‘Apache’. You will also be asked if you’d like a database installed and configured for phpmyadmin - choose ‘Yes’.

After the installation process has ended, we’ll be able to access phpmyadmin through our browser. In order to do so, we’ll type the following address:

localhost/phpmyadmin/

In the login form displayed on the screen, type the password you chose during the installation process of phpmyadmin, and you’ll be presented with the main page of phpmyadmin.

Setting Up MySQL In Windows:

In order to install MySQL on Windows, we’ll navigate to the following address:

https://www.apachefriends.org/

Download the installer for windows and run it. Click ‘OK’ button in the warning window, and keep clicking the ‘Next’ button (without changing any settings) until you reach a window with one checkbox (about ‘Bitnami for XAMPP’). Uncheck that box, and click the ‘Next’ button until the installation process starts. Once the installation process has finished, click the ‘Finish’ button (without unchecking the box above it), and the application will start.

If the services don’t start automatically, then you have a port issue, which requires you to change Apache’s operating port. In order to do so, click the Apache’s ‘Config’ button and choose ‘Apache (httpd.conf)’. In the text file that just opened, search for the word ‘port’, and change the ‘80’ right after the word ‘Listen’ to another number (81 is recommended). Now, look for the number ‘80’ until you find a line with that number which starts with the word ‘ServerName’ - change the number after the ‘localhost:’ into the same number you chose before.

Now start the Apache and MySQL services - allow for Windows Firewall, if prompted.

Now just update your IDE to run off of the php from XAMPP instead of the previously installed  ‘php.exe’.

Creating A Database And Table With phpMyAdmin:

First, we need to navigate to our phpmyadmin main page.

In linux, just navigate to the following address:

localhost/phpmyadmin/

In windows, make sure both the ‘Apache’ and the ‘MySQL’ services are running in XAMPP, and click the MySQL’s ‘Admin’ button, and a web page will be opened. If you changed MySQL’s operating port as previously described, you will first receive a ‘HTTP Error 404’ message. In order to access phpmyadmin’s main page, you’ll have to manually add the chosen port to the address:

localhost:ChosenPort/phpmyadmin/

For example:

localhost:81/phpmyadmin/

Now phpmyadmin’s main page will be displayed properly. If you get the login screen, simply login to phpmyadmin.

Now we need to create a new user. Click on the ‘User accounts’ tab and click on ‘Add user account’. Fill the desired user name and password, and in the ‘Host name’ section choose ‘Local’ (and the ‘localhost’ will be auto-inserted). In the privileges section, grant any privilege you feel right to give. After all configurations for the new user are done, click the ‘Go’ button at the bottom of the page.

Next, we need to create a database, we first click on the ‘new’ link on the left side of the page. In the page that has just opened, insert the desired name for the new database, and then click the “Create” button.

To create a table, first we’ll click on our new database’s link on the left side of the page. In the page just opened, insert the desired table name and number of columns and click the ‘Go’ button. In the page just opened, fill the boxes with the desired information and configure the columns as you please, and click the ‘Save’ button to save all the changes just made.

Connecting To Database:

Before we start coding, we first need to know a new command - ‘exit()’. This commands terminates the script and prints the content inside the parentheses. Another thing we should know is that we can create an instance to a MySQLi database as if it were a class. This method can take up to six parameters, which are all optional. It is done in the following syntax:
$VariableName = new mysqli($HostNameString, $UserNameString, $PasswordString, $DBNameString, $PortString, $SocketString);. Please note that the last two parameters are optional. There are also the ‘connect_errno’ and the ‘connect_error’ parameters, which are relevant in case of a failure in the connection to the database. The former stores the error number of the connection error, and the latter stores the verbal description of the error.

Here’s a code example that includes all the new stuff above:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

print_r($Connection);

?>

If the connection to the database works well, then the output will be something like this:

mysqli Object

(

    [affected_rows] => 0

    [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $

    [client_version] => 50011

    [connect_errno] => 0

    [connect_error] =>

    [errno] => 0

    [error] =>

    [error_list] => Array

       (

       )

    [field_count] => 0

    [host_info] => localhost via TCP/IP

    [info] =>

    [insert_id] => 0

    [server_info] => 5.5.5-10.1.13-MariaDB

    [server_version] => 50505

    [stat] => Uptime: 4879  Threads: 1  Questions: 720  Slow queries: 0  Opens: 8  Flush tables: 1  Open tables: 19  Queries per second avg: 0.147

    [sqlstate] => 00000

    [protocol_version] => 10

    [thread_id] => 46

    [warning_count] => 0
)

And if something is wrong with the connection to the database, then the output will be something like this:

Warning: mysqli::mysqli(): (HY000/1049): Unknown database 'DBName' in C:\xampp\htdocs\PhpProject1\index.php on line 9

Database Connection Failed. Reason: Unknown database 'DBName'PHP Warning:  mysqli::mysqli(): (HY000/1049): Unknown database 'yoav' in C:\xampp\htdocs\PhpProject1\index.php on line 9

Close Database Connection:

In PHP, all connections to a database are closed automatically, with the completion of the script. But, it is also good to do it manually. Closing the connection to a database in PHP is done in the following syntax: $VariableName->close();. For example:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Connection->close();

?>

Execute A Query - Delete, Update, Insert:

In order to execute any of those three queries, we first need to set the desired query as a variable’s value, and then use that variable with the database’s instance. It is done this way:

$QueryVariable = “DesiredQuery”;
$DatabaseInstance->query($QueryVariable);

The ‘Insert’ query:

The ‘Insert’ query is meant for adding new rows in a table. The ‘Insert’ query is written in the following syntax:
$VariableName = “INSERT INTO DesiredTable (Column1, Column2, Column3) VALUES (‘Value1’, ‘Value2’, ‘Value3’)”;. For example:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = “INSERT INTO singers (First_Name, Last_Name, Stage_Name) VALUES (‘Ian’, ‘Gillan’, NULL)”;
$Connection->query($Query);

$Connection->close();

?>

The ‘Update’ query:

The ‘Update’ query is meant for changing specific existing values with new ones, with needing to delete and reinsert them. The ‘Update’ query is written in the following syntax:
$VariableName = “UPDATE DesiredTable SET DesiredColumn = NewValue WHERE id = DesiredRow”; .For example:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = “UPDATE singers SET Last_Name = ‘Bailey’ WHERE id = 2”;
$Connection->query($Query);

$Connection->close();

?>

The ‘Delete’ query:

The ‘Delete’ query is meant for removing a specific existing line from a table. The ‘Delete’ query is written in the following syntax:
$VariableName = “DELETE FROM DesiredTable WHERE id = DesiredRow”;. For example:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = “DELETE FROM singers WHERE id = 3”;
$Connection->query($Query);

$Connection->close();

?>

Grabbing Inserted ID:

PHP lets us see the ID of the last inserted row in a table. It is done by printing the ‘$ConnectionVariableName->insert_id’ parameter. For example:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = “INSERT INTO singers (First_Name, Last_Name, Stage_Name) VALUES (‘Ian’, ‘Gillan’, NULL)”;
$Connection->query($Query);

echo “Newly Created Singer ID: “.$Connection->insert_id;

$Connection->close();

?>

And the output will be:

7

Select Data From Database:

The ‘Select’ query is meant for grabbing certain data pieces from a table. The ‘Select’ query is written in the following syntax:
$VariableName = “SELECT Column1, Column2, Column3 FROM DesiredTable”;, and followed by a ‘While’ loop inside an ‘If’ statement, which are dedicated for processing the data only in cases there is any data to process. Also, before closing the connection to the database and/or ending the script, we need to close/free the result object. It is done in the following syntax:
$VariableName = close();
Or:
$VariableName = free();
It doesn’t matter which one you use, because they both do the same thing.

Here’s an example script which contains everything in the paragraph above:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = "SELECT First_Name, Last_Name, Stage_Name FROM singers ORDER BY First_Name";
$ResultObject = $Connection->query($Query);

if($ResultObject->num_rows > 0)
{
    while($SingleRowFromQuery = $ResultObject->fetch_assoc())
    {
            print_r($SingleRowFromQuery);
    }
}

$ResultObject->close();
$Connection->close();

?>

And the output will be:

Array

(

    [First_Name] => Farrokh

    [Last_Name] => Bulsara

    [Stage_Name] => Freddie Mercury

)

Array

(

    [First_Name] => Gordon Matthew Thomas

    [Last_Name] => Summer

    [Stage_Name] => Sting

)

Array

(

    [First_Name] => Ian

    [Last_Name] => Gillan

    [Stage_Name] =>

)

Array

(

    [First_Name] => Michael Joseph

    [Last_Name] => Jackson

    [Stage_Name] => Michael Jackson

)

Array

(

    [First_Name] => Michael Philip

    [Last_Name] => Jagger

    [Stage_Name] => Mick Jagger

)

Array

(

    [First_Name] => Paul David

    [Last_Name] => Hewson

    [Stage_Name] => Bono

)

Array

(

    [First_Name] => William Bruce

    [Last_Name] => Rose

    [Stage_Name] => Axl Rose

)

We can also replace the ‘print_r()’ command with an ‘echo’ command. For exapmle:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    exit ("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$Query = "SELECT First_Name, Last_Name, Stage_Name FROM singers ORDER BY First_Name";
$ResultObject = $Connection->query($Query);

if($ResultObject->num_rows > 0)
{
    while($SingleRowFromQuery = $ResultObject->fetch_assoc())
    {
            echo "Singer: ".$SingleRowFromQuery['First_Name'].PHP_EOL;
    }
}

$ResultObject->free();
$Connection->close();

?>

And the output will be:

Singer: Farrokh

Singer: Gordon Matthew Thomas

Singer: Ian

Singer: Michael Joseph

Singer: Michael Philip

Singer: Paul David

Singer: William Bruce

Prepared Statement Example:

Prepared statements enable the user’s input to be safely used inside of a query, without risking in vulnerability to SQL Injection attacks.

Here’s an example code which includes prepared statements:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$Connection = new mysqli($DBServer, $DBUserName, $DBPassword, $DBName);

if($Connection->connect_errno)
{
    die("Database Connection Failed. Reason: ".$Connection->connect_error);
}

$TempFirstName = 'Farrokh';

$Query = "SELECT First_Name, Last_Name, Stage_Name FROM singers WHERE First_Name = ?";
$StatementObject = $Connection->prepare($Query);

$StatementObject->bind_param("s", $TempFirstName);
$StatementObject->execute();

$StatementObject->bind_result($FirstName, $LastName, $StageName);
$StatementObject->store_result();

if($StatementObject->num_rows > 0)
{
    while($StatementObject->fetch())
    {
        echo $FirstName." ".$LastName." (".$StageName.")".PHP_EOL;
    }
}

$StatementObject->close();
$Connection->close();

?>

And the output will be:

Farrokh Bulsara (Freddie Mercury)

PDO Example:

PDO and MySQLi are written in a very similar way, but there are still a few differences. In PDO, the command for connecting to the database is written in the following syntax:

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$VariableName = new PDO(‘mysql:host=’.$HostNameString.’;dbname=’.$DBNameString, $UserNameString, $PasswordString);

Please note that the ‘mysql’ is a keyword for specifically accessing a MySQL database, and that there will be a different keyword instead of it if the database you are trying to access is of another kind.

Another difference between MySQLi and PDO is the loop inside the ‘If’ statement that is used with each one of them - the loop used with PDO is the ‘Foreach’ loop. Also the condition is the ‘If’ statement itself is different between the two.

Here is an example for the ‘If’ statement’s syntac for PDO:

if ($ResultObject->rowCount() > 0)
{
    foreach($ResultObject as $SingleRowFromQuery)
    {
        echo “Singer: ”.$SingleRowFromQuery[‘First_Name’].PHP_EOL;
    }
}

One more difference between MySQLi and PDO is the closing method. In PDO, we free the result object and close the connection to the database by setting their value to ‘NULL’:

$ResultObject = null;
$Connection = null;

Here is an example for a PDO script with all the stuff just mentioned:

<?php

$DBPassword = "YourPassword";
$DBUserName = "YourUserName";
$DBServer = "HostOfDB";
$DBName = "DBName";

$VariableName = new PDO(‘mysql:host=’.$HostNameString.’;dbname=’.$DBNameString, $UserNameString, $PasswordString);

$Query = “SELECT First_Name, Last_Name, Stage_Name FROM singers ORDER BY First_Name”;
$ResultObject = $Connection->query($Query);

if ($ResultObject->rowCount() > 0)
{
    foreach($ResultObject as $SingleRowFromQuery)
    {
        echo “Singer: ”.$SingleRowFromQuery[‘First_Name’].PHP_EOL;
    }
}

$ResultObject = null;
$Connection = null;

?>

And the output will be:

Singer: Farrokh

Singer: Gordon Matthew Thomas

Singer: Ian

Singer: Michael Joseph

Singer: Michael Philip

Singer: Paul David

Singer: William Bruce