step by step

Uncategorized

phpMyAdmin

PHP Tutorial

Control Statement

PHP Functions

PHP Arrays

PHP Strings

State Management

PHP File

Upload Download

PHP OOPs Concepts

PHP MySQLi

PHP Topics

PHP Mail

PHP Mail

phpMyAdmin is an open-source software tool introduced on September 9, 1998, which is written in PHP. Basically, it is a third-party tool to manage the tables and data inside the database. phpMyAdmin supports various type of operations on MariaDB and MySQL. The main purpose of phpMyAdmin is to handle the administration of MySQL over the web.

It is the most popular application for MySQL database management. We can create, update, drop, alter, delete, import, and export MySQL database tables by using this software. phpMyAdmin also supports a wide range of operation like managing databases, relations, tables, columns, indexes, permissions, and users, etc., on MySQL and MariaDB. These operations can be performed via user interface, while we still have the ability to execute any SQL statement.

phpMyAdmin is translated into 72 languages and also supports both RTL and LTR languages so that the wide range of people can easily use this software. We can run MySQL queries, repair, optimized, check tables, and also execute other database management commands. phpMyAdmin can also be used to perform administrative tasks such as database creation, query execution.

phpMyAdmin is a GUI-based application which is used to manage MySQL database. We can manually create database and table and execute the query on them. It provides a web-based interface and can run on any server. Since it is web-based, so we can access it from any computer.

Features of phpMyAdmin

phpMyAdmin supports several features that are given below:

phpMyAdmin can create, alter, browse, and drop databases, views, tables, columns, and indexes.
It can display multiple results sets through queries and stored procedures.
phpMyAdmin use stored procedure and queries to display multiple results sets.
It supports foreign keys and InnoDB tables.
phpMyAdmin can track the changes done on databases, views, and tables.
We can also create PDF graphics of our database layout.
phpMyAdmin can be exported into various formats such as XML, CSV, PDF, ISO/IEC 26300 – OpenDocument Text and Spreadsheet.
It supports mysqli, which is the improved MySQL extension.
phpMyAdmin can interact with 80 different languages.
phpMyAdmin can edit, execute, and bookmark any SQL-statements and even batch-queries.
By using a set of pre-defined functions, it can transform stored data into any format. For example – BLOB-data as image or download-link.
It provides the facility to backup the database into different forms.

Advantage of phpMyAdmin

phpMyAdmin can run on any server or any OS as it has a web browser.
We can easily create, delete, and edit the database and can manage all elements using the graphical interface of phpMyAdmin, which is much easier than MySQL command-line editor.
phpMyAdmin helps us to control the user’s permission and operate several servers at the same time.
We can also backup our database and export the data into different formats like XML, CSV, SQL, PDF, OpenDocument Text, Excel, Word, and Spreadsheet, etc.
We can execute complex SQL statements and queries, create and edit functions, triggers, and events using the graphical interface of phpMyAdmin.

Disadvantage of phpMyAdmin

phpMyAdmin is a simple interface, but quite tough for a beginner to learn.
phpMyAdmin is difficult to install as it needs three more software tools before installation, which is- Apache server, PHP, and MySQL.
We have to install all these software tools individually, whereas XAMPP already contains them in a single package. XAMPP is the easiest way to get phpMyAdmin.
It has no schema visualization.
phpMyAdmin is a web-based software tool which runs only on the browser, so It completely depends on browsers.
It does not have auto-compilation capability.

Data Backup problem with phpMyAdmin

phpMyAdmin lacks a lot of features in import/export functionality. There are some backup problems with phpMyAdmin that are given below:

Scheduling – There is no way to export the data of the database in phpMyAdmin automatically.
Storage media support – As we have discussed earlier, phpMyAdmin is web-based software, so it runs only on the browser. We can take backups only to local drives of our system.
Compression, Encryption, and other option – The files which are exported with phpMyAdmin are saved as common text files, with any additional processing. Whereas storing these files in the original form usually takes a lot of disk storage.

Prerequisite

Web server – Apache, Nginx, IIS
PHP
Database – MySQL, MariaDB
Web Browser

Web server – phpMyAdmin’s interface is based on our web browser, we need a web server to keep phpMyAdmin’s files inside it. Apache and IIS are popular web servers. We can download Apache web server from here http://mirrors.estointernet.in/apache//httpd/.

PHP – We also need to install PHP 5.3 or upper version to support different functionalities. It contains different extensions to provide support for these functionalities. For example –

Session support -SPL (Standard PHP Library) extension
Uploading of ZIP files support -PHP zip extension
Cookie authentication – mcrypt extension
Open Document Spreadsheet and XML importing support –libxml extension

We can download PHP from here. https://www.php.net/downloads.php.

Database – phpMyAdmin supports databases, i.e.

MySQL 5.5 or latest versions
MariaDB 5.5 or latest versions

Download the MySQL database from here https://dev.mysql.com/downloads/file/?id=486088 or MariaDB database from here https://mariadb.org/download/.

Web Browser – Web browser is required to access phpMyAdmin with enabled cookie and JavaScript. It can be Chrome, Internet Explorer, etc.

Difference between phpMyAdmin and MySQL Console

phpMyAdmin provides a user interface through which we can execute query within SQL. We can also paste the query into SQL to test our output, whereas on MySQL Console we cannot copy and paste queries. We have to write query every time to execute on MySQL console.
MySQL is a database server, on the other hand, phpMyAdmin is a server tool to access the database on MySQL server.
We have to learn database query to create, delete, alter, update, and drop to execute on MySQL console, whereas in phpMyAdmin we can do it using graphical user interface which automatically executes these queries in background.

Note: In simple words, MySQL is a database, and phpMyAdmin is a software tool to access data from that database.

How to install phpMyAdmin?

Here we are going to discuss that how to download phpMyAdmin on Windows operating system. Below are the steps-

Step 1:

Download the latest version of phpMyAdmin software tool from here https://www.phpmyadmin.net/ as per the following instruction. Click on the download button to start downloading.

Step 2:

A popup window will open. Click on Close button and move to the next step.

Step 3:

Extract the downloaded file by right-clicking on the file and select Extract here, and rename the folder name with name phpmyadmin for easy access on the browser.

Step 4:

Once all the files of phpMyAdmin extracted successfully, move the extracted folder of phpMyAdmin from downloaded location to C:\Apache\htdocs. We can also extract files directly in the htdocs folder of apache.

Step 5:

Go inside the conf folder of apache in c drive and open httpd file.

Step 6:

Press Ctrl+F key and find index.html written in the file. Now, replace .html extension with .php and save the file.

Step 7:

Now check the status that apache server is running or not from the services of your computer system? If it is not running, then we need to run the apache server to open phpMyAdmin interface on the browser.

Step 8:

Now open the browser and type http://localhost/phpmyadmin/. phpMyAdmin will start running in the browser.

How to work with phpMyAdmin?

Click on New (1) to create a database and enter the database name in Create database (2) field and then click on Create (3) button. We can create any number of databases.

Enter the table name, number of columns, and click on Go. A message will show that the table is created successfully.

Now enter the field name, type, their size, and any constraint here and save it.

The table is created successfully. We can make changes in the table from here.

Database creation using coding with phpMyAdmin

We can create the database and tables using phpMyAdmin graphical user interface as well as with coding in php.

<?php  
    $servername = "localhost";  
    $username = "root";  //default user name is root  
    $password = "";     //default password is blank  
    $conn = mysqli_connect($servername, $username, $password);  
    if(!$conn)  
        die("Connection failed".mysqli_connect_error());  
    else      
        //echo "Successfully connected with database";  
    $query = "CREATE DATABASE newDB";  
    if (mysqli_query($conn, $query)) {  
        echo "Database created successfully with the name newDB";  
    } else {  
        echo "Error creating database: " . mysqli_error($conn);  
    }  
    mysqli_close($conn);  
?>

Output:

Database created successfully with the name newDB.

Database connectivity with phpMyAdmin

In phpMyAdmin, we create a database using a graphical user interface as well as by running queries.

<?php  
    $dbhost="localhost";  
    $dbName="newDB";  
    $user="root";  
    $pass="";  
    $conn = new mysqli("mysql:host=$dbhost;dbname=$dbName",$user,$pass);  
    try{  
        echo "Successfully connected with newdb database";  
    }  
    catch(Exception $e){  
            die("Connection failed".$e->getMessage());  
    }  
?>

Output:

Successfully connected with the database.

< prev

next >

Uncategorized

Introduction to PHP PDO

PHP Tutorial

Control Statement

PHP Functions

PHP Arrays

PHP Strings

PHP Math

PHP Math Functions

PHP Form

PHP Form: Get Post

PHP Include

PHP include & require

State Management

PHP File

Upload Download

PHP OOPs Concepts

PHP MySQLi

PHP Topics

PHP Mail

PHP Mail

PHP is an open-source general-purpose scripting language, which is widely used for creating dynamic and interactive web pages. PHP can access a large range of relational database management systems such as MYSQL, SQLite, and PostgreSQL. The PHP 5.1 version offered a new database connection abstraction library, which is PHP Data Objects (PDO).

What is PDO?

PDO refers to PHP Data Object, which is a PHP extension that defines a lightweight and consistent interface for accessing a database in PHP. It is a set of PHP extensions which provide a core PDO class and database-specific driver. Each database driver can expose database-specific features as a regular extension function that implements the PDO interface.

Note: We cannot perform any type of database function by using the PDO extension itself. To access a database server, we must use a database-specific PDO driver.

PDO mainly focuses on data access abstraction rather than database abstraction. It provides data-access abstraction layer, which means, regardless of which database we are using, we have to use the same functions provided by that database to issue queries and fetch data. PDO does not provide data abstraction, as it does not rewrite the SQL or emulate missing features.

Advantage of PDO

PDO provides various ways to work with objects and retrieves prepared statements that make work much easier. It is a database access tool in PHP through which we enable uniform access across several databases.

PDO allows comparatively seamless switching between different databases and platforms, which can be easily done by changing the connection string. It does not support database-specific syntaxes.

There are some advantages of PDO as follows:

Database support
The PDO extension can access any database which is written for PDO driver. There are several PDO drivers available which are used for FreeTDS, Microsoft SQL Server, Sybase, IBM DB2, Oracle Call Interface, Firebird/Interbase 6, and PostgreSQL databases, among many more.
The drivers are not available in every system automatically, so we have to find our available drivers and add ones when we need them.
Database connecting
There are different syntaxes available to establish the database connection. These syntaxes depend on specific databases. While using PDO, operations must be wrapped in try/catch blocks and utilize the exception technique.
Usually, only a single connection needs to create, and these connections are closed by programming the database to set as a null.
Error handling
PDO permits to use exceptions for error handling. To produce an exception, PDO can be forced into a relevant error mode attribute.
There are three error modes, i.e., Silent (default), Warning, and Exception. Warning and Exception are more useful in DRY programming.
1. Silent – It is a default error mode.
2. Warning – It is useful for debugging.
3. Exception – This mode allows graceful error handling while hiding data that a person might use to exploit your system.
Insert and Update
PDO reduces the commonly used insert and update database operation into a two-step process, i.e.
Prepare >> [Bind] >> Execute.
Through this method, we can take full advantage of PDO’s prepared statements, which protect against malicious attacks through SQL injection.
Prepared statements are pre-compiled SQL statements that can be executed multiple times by sending this data to the server. This data, which is used within the placeholder, is automatically protected from the SQL injection attack.

Benefits of using PDO

PDO is the native database driver. There are some benefits of using PDO that are given below:

Usability – It contains many helper functions to operate automatic routine operations.
Reusability – It offers the unified API to access multiple databases.
Security – It uses a prepared statement which protects from SQL injection. A prepared statement is a pre-compiled SQL statement that separates the instruction of the SQL statement from the data.

PDO Classes

There are three PDO classes, which are given below:

PDO – It represents a connection between PHP and the database.
PDOStatement – It represents the prepared statement and after the execution of the statement, sets an associated result.
PDOException – It represents errors raised by PDO.

Databases supported by PDO

MySQL
PostgreSQL
Oracle
Firebird
MS SQL Server
Sybase
Informix
IBM
FreeTDS
SQLite
Cubrid
4D

Comparison between PDO and MySQLi

To access the database by using PHP, we have mainly two options – MySQLi and PDO (PHP Data Object). MySQLi is a native for PHP that provides faster performance, whereas, most of the experienced developers prefer to work with PDO as it supports a wide variety of database drivers. There are some differences between PDO and MySQLi listed below based on their features.

Features	PDO	MySQLi
DB Support	12 different drivers	MySQL only
Connection	Easy	Easy
API	OOP	OOP + Procedure
Performance	Fast	Fast
Named Parameter	Yes	No
Object mapping	Yes	Yes
Stored Procedure	Yes	Yes
Client-Side Prepared Statements	Yes	No
Security	More secure than MySQLi.	Secure but not more than PDO.

Which should be preferred between PDO or MySQLi?

Both PDO and MySQLi have their own advantages:

As we have seen earlier that PDO works on 12 different database systems, whereas MySQL can work only with MySQL database. So, if we want to switch our project to another database, PDO makes it easy. In MySQLi, we have to rewrite the entire code.
PDO and MySQLi both are Object-Oriented, but MySQLi also offers procedural API. Both support Prepared Statements. Prepared Statements are important for web application security, as it protects from SQL injection.

Requirement

There is no need of external libraries to build this extension.

Installation Process

Step 1: Download the latest XAMPP server from here https://www.apachefriends.org/download.html for different platforms like Windows, Linux, and MacOS.

Note: Here we will discuss the installation process for Windows OS only.

Step 2: Install the XAMPP Server on your system by following these steps.

Step 3: Select the components which you want to install and click on the Next button.

Step 4: Create the new folder with the name xampp at the location where you want to install XAMPP.

Step 5: Click on Next here and move forward. Installation of the XAMPP server will start from here.

Step 6: XAMPP is installed successfully. Click on Finish button.

Step 7: Select the preferred Language.

Step 8: Run the Apache server and MySQL from here (as per the given screenshot).

Step 9: Now, open php.ini from C:/xampp/php/php.ini (where you have installed your XAMPP) and uncomment the extension “php_pdo_mysql.dll” and “php_pdo.dll” (if you are working with MySQL database), or “php_pdo_oci.dll” (if you are working with Oracle database). Now, start working with the database. In PHP 5.1 upper version, it is already set.

Working with PDO

Firstly we need to create a database, so create a database by name myDB from here.

Database Connection

A database connection is always required to interact with the database. So, we need to know the identifier to access database, i.e., location of the database, database name, username, and password.

Now, create the database connection program using PDO in any text editor such as notepad or notepad++ and save it by coonection.php name. Run it on XAMPP server using localhost/80.

Example

<?php  
    $dbHost="localhost";  
    $dbName="myDB";  
    $dbUser="root";      //by default root is user name.  
    $dbPassword="";     //password is blank by default  
    try{  
        $dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);  
        Echo "Successfully connected with myDB database";  
    } catch(Exception $e){  
    Echo "Connection failed" . $e->getMessage();  
    }  
?>

Output

Run it on the server using following URL localhost/Xampp/pdoexample/connection.php/ or where you have saved your program.

Handling Connection Error

A PDOException object will be thrown if there is any connection error. We may catch the exception if we want to handle the error condition, or we can also leave it to global exception handler which can be set up by set_exception_handler() function.

Example

In this example, dbUser(user-id) is wrong, so it will throw an exception, as we can see in the output.

<?php  
    $dbHost="localhost";  
    $dbName="myDB";  
    $dbUser="rroot";          
    $dbPassword="";       
    try{  
        $dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);  
          
        //Set the PDO error mode to exception.  
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
        Echo "Successfully connected with myDB database";  
    } catch(Exception $e){  
    Echo "Connection failed" . $e->getMessage();  
    }  
?>

Closing the database connection

<?php  
    $dbHost="localhost";  
    $dbName="myDB";  
    $dbUser="root";      //by default root is user name.  
    $dbPassword="";     //password is blank by default  
    try{  
        $dbConn= new PDO("mysql:host=$dbHost;dbname=$dbName",$dbUser,$dbPassword);  
        Echo "Successfully connected with myDB database";  
    } catch(Exception $e){  
    Echo "Connection failed" . $e->getMessage();  
    }  
// this command close the connection.  
    $dbConn = null;   
?>

Output

< prev

next >