Quick Contact

    Relational Database Concept and technology

    A Relational Database Management System (RDBMS) is a server that manages data for you. The data is structured into tables, where each table has some number of columns, each of which has a name and a type. For example, to keep track of James Bond movies, we might have a “movies” table that records the title (a string), year of release (a number), and the actor who played Bond in each movie (an index into a table of Bond actors).

    Tables are grouped together into databases, so a James Bond database might have tables for movies, actors playing Bond, and villains. An RDBMS usually has its own user system, which controls access rights for databases (e.g., “user Fred can update database Bond”).

    PHP communicates with relational databases such as MySQL and Oracle using the Structured Query Language (SQL). You can use SQL to create, modify, and query relational databases.

    The syntax for SQL is divided into two parts. The first, Data Manipulation Language, or DML, is used to retrieve and modify data in an existing database. DML is remarkably compact, consisting of only four verbs: select, insert, update, and delete. The set of SQL commands, used to create and modify the database structures that hold the data, is known as Data Definition Language, or DDL. The syntax for DDL is not as standardized as that for DML, but as PHP just sends any SQL commands you give it to the database, you can use any SQL commands your database supports.

    Web Database Design

    A web database is a wide term for managing data online.A web database gives you the ability to build your own databases/data storage without you being a database guru or even a technical person.

    Website operators can manage this collection of data and present analytical results based on the data in the Web database application. Databases first appeared in the 1990s, and have been an asset for businesses, allowing the collection of seemingly infinite amounts of data from infinite amounts of customers.

    Web SQL Database is a web page API for storing data in databases that can be queried using a variant of SQL.Databases are everywhere, including everywhere in the world of web development. Everything from the simplest blogs and directories and to robust user-oriented websites use databases. No matter how complex or simple the website and corresponding database may be though, each takes careful planning in order to run efficiently and also securely.

    What Functionality is Needed from the Database?

    The first method for planning for a database is to simply brainstorm, on paper or otherwise, concerning what the database will need to store, and what the site will need out of it. Try not to think of the individual fields or tables that will be needed at this point – all that specific planning can take place later. The goal is to start with a general and complete view and narrow down. It can often times be more difficult to add in items later, rather than get it right the first time.

    Think outside the database. Try to think about what the website will need to do. For example, if a membership website is needed, the first instinct may be to begin thinking of all the data each user will need to store. Forget it, that’s for later. Rather, write down that users and their information will need to be stored in the database, and what else? What will those members need to do on the site? Will they make posts, upload files or photos, or send messages? Then the database will need a place for files/photos, posts, and messages.

    What information will they need to derive from the site? Will they need to search for their favorite recipe, be able to access member-only content, or need to look up products and their recently purchased or viewed products? Then the database will need a place to hold those recipes, a place for content that is defined as members-only or not or hold all products and create a method to link certain products to a specific member.

    Determining Tables and Fields

    The next phase would be to begin determining exactly what tables and fields one would need in the database. This is the core of database design, and the most difficult part. Using correct methods for linking tables together, sorting the data within each table correctly, and grouping it or keeping it separate are all arising problems when it comes to database design. At this point, list out what tables and fields are clear at this point, trying to be as specific as possible. Through the process, items can be rearranged or reorganized to improve the database’s efficiency and security.

    Use a Data Modeling Tool

    Now that you know what the site will need to do, it’s time to organize what exact information needs to be stored. A great database design tool can be helpful for this; specifically, one that can help set up visual database models, such as MySQL Workbench (for MySQL databases only) or DBDesigner4. Cliffy is also a great free online application for creating flowcharts and database models. An example of output of data modelling tool is:

    Database Design Using Data Modelling Tools

    Become familiar with the common icons and standard visual elements necessary to create database models and begin planning via flowcharts and diagrams ahead of time. This can sort out logical errors before any actual databases are created.

    Relational Databases

    Almost all databases are relational databases. This means that the tables in the database are related to each other in some way. For example, if a there is a member on an ecommerce website, that member may be related to certain products based on what they ordered last, or what they have expressed they are interested in. For a blog database, authors would have to be somehow related to the posts they wrote and logged in users could be related to any comments they’ve left.

    By using the techniques for relational databases, we can store plenty of information in an organized fashion within separate tables: one table for members, one for posts, another for comments, and yet another for products. Then, we can link the data between different tables together via unique keys.

    The below figure shows the table present in relational database:

    Table Structure in Relational Database

    Every entry in every table needs a unique primary key. This is the “social security number” or “bar code” for each entry. It is unique to each entry, and no other entry can have the same ID in the same table. Having unique usernames or product names in a database table is not enough. It is far more efficient, and best practice as well, to use unique primary keys. Even with other types of unique fields, a database is still vulnerable to duplicate records, which can later break code within the website.

    To relate two tables, we use a foreign key, which is just a number ID that references a unique key in another table, usually our primary key. As an example, below we can see that our first table for authors has three authors with their own unique ID. In the separate articles table, we link each article to an author via that ID. We can now look up the author for the first article, and vice versa, see that Tom has two articles, Mary has one, and Jane has none yet.

    Relationship Diagram

    This is a simple one-to-one relationship model. There are also models for one-to-many and many-to-many relationships.

    Grouping or Separating Data info Fields

    Within fields, it’s also important to know when to group certain pieces of data together, and when to keep them separate. A good way to determine which information should be in the same field or otherwise is to think about what it would take to change that piece of information if necessary. For example, would it be necessary to place a full address in separate fields, based on 1) street address, 2) city, 3) state, 4) zip code, and then 5) country?

    Is it essential for the functionality of the site (perhaps users or admins would need to search addresses by state only), or is it just a waste of fields and database space? If it’s not essential, just to change an address the database would have to update five separate fields, when it could just update one field in string form. To keep such a field organized, one could take in the information via an HTML form with these fields separated, but then concatenate them into one single string before placing the address into the database.

    This is just one example, but always keep in mind the most efficient ways to organize table fields, and when to combine them, or when to keep them separate for the sake of the website’s functionality.

    Database Normalization

    Database normalization is a set of guidelines created by the community for organizing data in a database efficiently. We’ve mentioned a few of the most important and basic practices already, which are included in some of the most standard normalization forms. There are five normal forms to follow, and it’s a good idea to learn about these five forms in order to conform any database’s design to their best practices.

    Database normalization is a large topic, but just understanding the basics can help tremendously.

    Web Database Architecture

    A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

    A Database Management system is not always directly available for users and applications to access and store data in it. A Database Management system can be centralised(all the data stored at one location), decentralised(multiple copies of database at different locations) or hierarchical, depending upon its architecture.

    1-tier DBMS architecture also exist, this is when the database is directly available to the user for using it to store data. Generally, such a setup is used for local application development, where programmers communicate directly with the database for quick response.

    Database Architecture is logically of two types:

      1. 2-tier DBMS architecture
      2. 3-tier DBMS architecture
    2-tier DBMS Architecture

    2-tier DBMS Architecture

    An application interface known as ODBC(Open Database Connectivity) provides an API that allow client side program to call the DBMS. Most DBMS vendors provide ODBC drivers for their DBMS.

    2-tier DBMS Architecture

    Such an architecture provides the DBMS extra security as it is not exposed to the End User directly. Also, security can be improved by adding security and authentication checks in the Application layer too.

    3-tier DBMS Architecture

    3-tier DBMS architecture is the most commonly used architecture for web applications.

    3-tier DBMS Architecture

    It is an extension of the 2-tier architecture. In the 2-tier architecture, we have an application layer which can be accessed programmatically to perform various operations on the DBMS. The application generally understands the Database Access Language and processes end users requests to the DBMS.

    In 3-tier architecture, an additional Presentation or GUI Layer is added, which provides a graphical user interface for the End user to interact with the DBMS.

    For the end user, the GUI layer is the Database System, and the end user has no idea about the application layer and the DBMS system.

    If you have used MySQL, then you must have seen PHPMyAdmin, it is the best example of a 3-tier DBMS architecture.

    Creating a MySQL Database

    To create and delete a database you should have admin privilege. Its very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

    Syntax
    bool mysql_query( sql, connection );
    Sr.No Parameter & Description
    1

    sql

    Required – SQL query to create a database

    2

    connection

    Optional – if not specified then last opend connection by mysql_connect will be used.

    Example

    The following example to create a database using PHP.

    <?php
       $dbhost = 'localhost:3036';
       $dbuser = 'root';
       $dbpass = 'rootpassword';
       $conn = mysql_connect($dbhost, $dbuser, $dbpass);
       if(! $conn ) {
          die('Could not connect: ' . mysql_error());
       }
       echo 'Connected successfully';
       $sql = 'CREATE Database test_db';
       $retval = mysql_query( $sql, $conn );
       if(! $retval ) {
          die('Could not create database: ' . mysql_error());
       }
       echo "Database test_db created successfully\n";
       mysql_close($conn);
    ?>
    
    Selecting a Database

    Once you establish a connection with a database server then it is required to select a particular database where your all the tables are associated.

    This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

    PHP provides function mysql_select_db to select a database. It returns TRUE on success or FALSE on failure.

    Syntax
    bool mysql_select_db( db_name, connection );
    Sr.No Parameter & Description
    1

    db_name

    Required: Database name to be selected

    2

    Connection

    Optional: if not specified then last opend connection by mysql_connect will be used.

    Example

    Here is the example showing you how to select a database.

    <?php
       $dbhost = 'localhost:3036';
       $dbuser = 'guest';
       $dbpass = 'guest123';
       $conn = mysql_connect($dbhost, $dbuser, $dbpass);
       if(! $conn ) { 
          die('Could not connect: ' . mysql_error());
       }
       echo 'Connected successfully';   
       mysql_select_db( 'test_db' );
       mysql_close($conn);   
    ?>
    
    Creating Database Tables

    To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

    Example

    The following example is used to create a table:

    <?php   
       $dbhost = 'localhost:3036';
       $dbuser = 'root';
       $dbpass = 'rootpassword';
       $conn = mysql_connect($dbhost, $dbuser, $dbpass);   
       if(! $conn ) {
          die('Could not connect: ' . mysql_error());
       }
       echo 'Connected successfully';   
       $sql = 'CREATE TABLE employee( '.
          'emp_id INT NOT NULL AUTO_INCREMENT, '.
          'emp_name VARCHAR(20) NOT NULL, '.
          'emp_address  VARCHAR(20) NOT NULL, '.
          'emp_salary   INT NOT NULL, '.
          'join_date    timestamp(14) NOT NULL, '.
          'primary key ( emp_id ))';
       mysql_select_db('test_db');
       $retval = mysql_query( $sql, $conn );   
       if(! $retval ) {
          die('Could not create table: ' . mysql_error());
       }   
       echo "Table employee created successfully\n";
       mysql_close($conn);
    ?>
    

    In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

    Consider the following content in sql_query.txt file

    CREATE TABLE employee(
       emp_id INT NOT NULL AUTO_INCREMENT,
       emp_name VARCHAR(20) NOT NULL,
       emp_address  VARCHAR(20) NOT NULL,
       emp_salary   INT NOT NULL,
       join_date    timestamp(14) NOT NULL,
       primary key ( emp_id ));
    <?php
       $dbhost = 'localhost:3036';
       $dbuser = 'root';
       $dbpass = 'rootpassword';
       $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    
       if(! $conn ) {
          die('Could not connect: ' . mysql_error());
       }
    
       $query_file = 'sql_query.txt';   
       $fp = fopen($query_file, 'r');
       $sql = fread($fp, filesize($query_file));
       fclose($fp);    
       mysql_select_db('test_db');
       $retval = mysql_query( $sql, $conn );   
       if(! $retval ) {
          die('Could not create table: ' . mysql_error());
       }   
       echo "Table employee created successfully\n";
       mysql_close($conn);
    ?>
    
    Column Data Types

    Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use. For example, do not define a field 10 characters wide, if you know you are only going to use 2 characters. These type of fields (or columns) are also referred to as data types, after the type of data you will be storing in those fields.

    MySQL uses many different data types broken into three categories:

    • Numeric
    • Date and Time
    • String Types.

    Let us now discuss them in detail.

    Numeric Data Types

    MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL from a different database system, these definitions will look familiar to you. The following list shows the common numeric data types and their descriptions:

    INT: A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.

    TINYINT: A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.

    SMALLINT: A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.

    MEDIUMINT: A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

    BIGINT: A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

    FLOAT(M,D): A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.

    DOUBLE(M,D): A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.

    DECIMAL(M,D): An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

    Date and Time Types

    The MySQL date and time datatypes are as follows:

    DATE: A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.

    DATETIME: A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.

    TIMESTAMP: A timestamp between midnight, January 1st, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).

    TIME: Stores the time in a HH:MM:SS format.

    YEAR(M): Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

    String Types

    Although the numeric and date types are fun, most data you’ll store will be in a string format. This list describes the common string datatypes in MySQL.

    CHAR(M): A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.

    VARCHAR(M): A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.

    BLOB or TEXT: A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data. The difference between the two is that the sorts and comparisons on the stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

    TINYBLOB or TINYTEXT: A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.

    MEDIUMBLOB or MEDIUMTEXT: A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

    LONGBLOB or LONGTEXT: A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

    ENUM: An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain “A” or “B” or “C”, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’) and only those values (or NULL) could ever populate that field.

    CRUD stands for create, read, update and delete. Create means inserting data into database using INSERT SQL statement. Read means reading data from database using SELECT SQL statement. Update means updating records using UPDATE SQL query. Finally, delete means deleting data from database using DELETE SQL statements.

    PHP MySQL Insert

    PHP mysql_query() function is used to insert record in a table. We can use one of the 2 alternatives.

    • mysqli_query()
    • PDO::__query()

    An example of insert query is:

    <?php  
    $host = 'localhost:3306';  
    $user = '';  
    $pass = '';  
    $dbname = 'test';    
    $conn = mysqli_connect($host, $user, $pass,$dbname);  
    if(!$conn){  
      die('Could not connect: '.mysqli_connect_error());  
    }  
    echo 'Connected successfully<br/>';    
    $sql = 'INSERT INTO emp4(name,salary) VALUES ("sonoo", 9000)';  
    if(mysqli_query($conn, $sql)){  
     echo "Record inserted successfully";  
    }else{  
    echo "Could not insert record: ". mysqli_error($conn);  
    }  
    mysqli_close($conn);  
    ?>
    

    The output is

    Connected successfully
    Record inserted successfully
    
    PHP MySQL Delete

    PHP mysql_query() function is used to delete record in a table. We can delete a data by using delete database operation inside PHP.

    An example of deleting data is given below:

    <?php $host = ‘localhost:3306’; $user = ”; $pass = ”; $dbname = ‘test’; $conn = mysqli_connect($host, $user, $pass,$dbname); if(!$conn){ die(‘Could not connect: ‘.mysqli_connect_error()); } echo ‘Connected successfully<br/>’; $id=2; $sql = “delete from emp4 where id=$id”; if(mysqli_query($conn, $sql)){ echo “Record deleted successfully”; }else{ echo “Could not deleted record: “. mysqli_error($conn); } mysqli_close($conn); ?>

    <?php  
    $host = 'localhost:3306';  
    $user = '';  
    $pass = '';  
    $dbname = 'test';    
    $conn = mysqli_connect($host, $user, $pass,$dbname);  
    if(!$conn){  
      die('Could not connect: '.mysqli_connect_error());  
    }  
    echo 'Connected successfully<br/>';    
    $id=2;  
    $sql = "delete from emp4 where id=$id";  
    if(mysqli_query($conn, $sql)){  
     echo "Record deleted successfully";  
    }else{  
    echo "Could not deleted record: ". mysqli_error($conn);  
    }    
    mysqli_close($conn);  
    ?>
    
    Output:
    Connected successfully
    Record deleted successfully
    
    PHP MySQL Update

    We can also update a specific data in database using PHP. An example is shown below:

    <?php  
    $host = 'localhost:3306';  
    $user = '';  
    $pass = '';  
    $dbname = 'test';    
    $conn = mysqli_connect($host, $user, $pass,$dbname);  
    if(!$conn){  
      die('Could not connect: '.mysqli_connect_error());  
    }  
    echo 'Connected successfully<br/>';    
    $id=2;  
    $name="Rahul";  
    $salary=80000;  
    $sql = "update emp4 set name=\"$name\", salary=$salary where id=$id";  
    if(mysqli_query($conn, $sql)){  
     echo "Record updated successfully";  
    }else{  
    echo "Could not update record: ". mysqli_error($conn);  
    }    
    mysqli_close($conn);  
    ?>
    
    Output:
    Connected successfully
    Record updated successfully
    
    PHP MySQL Update

    PHP mysql_query() function is used to execute select query. Along with this function there are two other MySQLi functions used in select query.

    • mysqli_num_rows(mysqli_result $result): returns number of rows.
    • mysqli_fetch_assoc(mysqli_result $result): returns row as an associative array. Each key of the array represents the column name of the table. It returns NULL if there are no more rows.

    An example of update command is:

    <?php  
    $host = 'localhost:3306';  
    $user = '';  
    $pass = '';  
    $dbname = 'test';  
    $conn = mysqli_connect($host, $user, $pass,$dbname);  
    if(!$conn){  
      die('Could not connect: '.mysqli_connect_error());  
    }  
    echo 'Connected successfully<br/>';  
    $sql = 'SELECT * FROM emp4';  
    $retval=mysqli_query($conn, $sql);  
    
    if(mysqli_num_rows($retval) > 0){  
     while($row = mysqli_fetch_assoc($retval)){  
        echo "EMP ID :{$row['id']}  <br> ".  
             "EMP NAME : {$row['name']} <br> ".  
             "EMP SALARY : {$row['salary']} <br> ".  
             "--------------------------------<br>";  
     } //end of while  
    }else{  
    echo "0 results";  
    }  
    mysqli_close($conn);  
    ?>
    
    Output:
    Connected successfully
    EMP ID :1 
    EMP NAME : ratan 
    EMP SALARY : 9000 
    --------------------------------
    EMP ID :2 
    EMP NAME : karan 
    EMP SALARY : 40000 
    --------------------------------
    EMP ID :3 
    EMP NAME : jai 
    EMP SALARY : 90000 
    --------------------------------
    
    Aggregate Functions

    Aggregate functions in MySQL are a group of functions that are used to operate on a set of values. These functions ignore NULL values unless specified. Functions like AVG(), MIN(), MAX(), COUNT() etc. fall under this category. As they operate on a set of values, if no Group by clause is used, it applies to all rows.

    The below table describes group (aggregate) functions that operate on sets of values.

    Name Description
    AVG() Return the average value of the argument
    BIT_AND() Return bitwise AND
    BIT_OR() Return bitwise OR
    BIT_XOR() Return bitwise XOR
    COUNT() Return a count of the number of rows returned
    COUNT(DISTINCT) Return the count of a number of different values
    GROUP_CONCAT() Return a concatenated string
    JSON_ARRAYAGG() Return result set as a single JSON array
    JSON_OBJECTAGG() Return result set as a single JSON object
    MAX() Return the maximum value
    MIN() Return the minimum value
    STD() Return the population standard deviation
    STDDEV() Return the population standard deviation
    STDDEV_POP() Return the population standard deviation
    STDDEV_SAMP() Return the sample standard deviation
    SUM() Return the sum
    VAR_POP() Return the population standard variance
    VAR_SAMP() Return the sample variance
    VARIANCE() Return the population standard variance

    The above functions are described as:

    For the purposes of demonstrating the aggregate functions in action we will use a table with the following data:

    Database Table Formation
    MySQL AVG() Function

    The AVG() function adds together all the values for a specified column in a SELECT statement and divides it by the number of rows to arrive at an average value. The result can then be assigned to an alias using the AS clause. For example, to find the average price of the products in our database, and assign the result to an alias named price_avg:

    We can also be selective about the rows used in the average calculation by using the WHERE clause:

    MySQL COUNT() Function

    The MySQL COUNT() function adds the number of rows that match the filter criteria specified in a SELECT statement. For example, to count the number of rows with a price in our sample table:

    Similarly, we can restrict our criteria to list the number of products beneath a specific price threshold:

    MySQL MAX() Function

    The MAX() function returns data from the row in which the specified column contains the highest value. For example we can find the most expensive product in our database table:

    MySQL MIN() Function

    The MIN() function performs the opposite task to the MAX() function in that it returns data from the row containing the lowest value in the specified column. For example, to find the least expensive item in our table:

    SUM() Function

    The SUM() function returns the total of all the values in a specified column. Therefore, to get the total value of every item in the table:

    Multiple Aggregate Functions

    SELECT statements are not restricted to a single aggregate function. It is perfectly valid to include calls to multiple functions, for example:

    Having and Group By Clause

    The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. We can use same function inside PHP file to execute the statement.

    HAVING Syntax
    SELECT column_name(s)
    FROM table_name
    WHERE condition
    GROUP BY column_name(s)
    HAVING condition
    ORDER BY column_name(s);
    
    Consider a database:
    CustomerID CustomerName ContactName Address City PostalCode Country
    1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
    2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
    3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
    4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
    5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
    SQL GROUP

    The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

    Example
    SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    HAVING COUNT(CustomerID) > 5;
    

    The output will be:

    COUNT(CustomerID) Country
    9 Brazil
    11 France
    11 Germany
    7 UK
    13 USA

    The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

    Example
    SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    HAVING COUNT(CustomerID) > 5
    ORDER BY COUNT(CustomerID) DESC;
    

    The output will be:

    COUNT(CustomerID) Country
    13 USA
    11 France
    11 Germany
    9 Brazil
    7 UK
    Joining Table

    A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

    Let’s look at aselection from the “Orders” table:

    OrderID CustomerID OrderDate
    10308 2 1996-09-18
    10309 37 1996-09-19
    10310 77 1996-09-20

    Then, look at a selection from the “Customers” table:

    CustomerID CustomerName ContactName Country
    1 Alfreds Futterkiste Maria Anders Germany
    2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
    3 Antonio Moreno Taquería Antonio Moreno Mexico

    Notice that the CustomerID column in the “Orders” table refers to the CustomerID in the “Customers” table. The relationship between the two tables above is the CustomerID column.

    Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

    Example
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
    

    and it will produce something like this:

    OrderID CustomerName OrderDate
    10308 Ana Trujillo Emparedados y helados 9/18/1996
    10365 Antonio Moreno Taquería 11/27/1996
    10383 Around the Horn 12/16/1996
    10355 Around the Horn 11/15/1996
    10278 Berglunds snabbköp 8/12/1996
    Different Types of SQL JOINs

    Here are the different types of the JOINs in SQL:

    • (INNER) JOIN: Returns records that have matching values in both tables
    • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
    • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
    • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
    SQL INNER JOIN Keyword

    The INNER JOIN keyword selects records that have matching values in both tables.

    INNER JOIN Syntax
    SELECT column_name(s)
    FROM table1
    INNER JOIN table2 ON table1.column_name = table2.column_name;
    

    SQL LEFT JOIN Keyword

    The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

    Left JOIN Syntax
    SELECT column_name(s)
    FROM table1
    LEFT JOIN table2 ON table1.column_name = table2.column_name;
    

    SQL RIGHT JOIN Keyword

    The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

    RIGHT JOIN Syntax
    SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2 ON table1.column_name = table2.column_name;
    

    SQL FULL JOIN Keyword

    The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.

    Note: FULL OUTER JOIN can potentially return very large result-sets!

    FULL JOIN Syntax
    SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
    

    Implementing Primary key, Unique Key, Composite key, foreign key, default constraint etc.
    Primary Key

    The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values and cannot contain NULL values.

    A table can have only one primary key, which may consist of single or multiple fields.

    The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:

    MySQL:
    CREATE TABLE Persons (
        ID int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Age int,
        PRIMARY KEY (ID)
    );
    To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
    
    MySQL / SQL Server / Oracle / MS Access:
    CREATE TABLE Persons (
        ID int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Age int,
        CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
    );
    

    Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

    DROP a PRIMARY KEY Constraint

    To drop a PRIMARY KEY constraint, use the following SQL:

    MySQL:
    ALTER TABLE Persons
    DROP PRIMARY KEY;
    
    Unique Key

    The UNIQUE constraint ensures that all values in a column are different.Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

    A PRIMARY KEY constraint automatically has a UNIQUE constraint.However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

    The following SQL creates a UNIQUE constraint on the “ID” column when the “Persons” table is created:

    CREATE TABLE Persons (
        ID int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Age int,
        UNIQUE (ID)
    );
    
    Composite Key

    Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.

    SQL Syntax to specify composite key:
    CREATE TABLE TABLE_NAME  
    (COLUMN_1, DATA_TYPE_1,  
    COLUMN_2, DATA_TYPE_2,  
    ???  
    PRIMARY KEY (COLUMN_1, COLUMN_2, ...));
    
    Foreign Key

    In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.In simple words you can say that, a foreign key in one table used to point primary key in another table.

    Consider an example:

    First table:
    S_Id LastName FirstName CITY
    1 MAURYA AJEET ALLAHABAD
    2 JAISWAL RATAN GHAZIABAD
    3 ARORA SAUMYA MODINAGAR
    Second table:
    O_Id OrderNo S_Id
    1 99586465 2
    2 78466588 2
    3 22354846 3
    4 57698656 1
    • The “S_Id” column in the “Students” table is the PRIMARY KEY in the “Students” table.
    • The “S_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.

    To create a foreign key on the “S_Id” column when the “Orders” table is created:

    CREATE TABLE orders  
    (  
    O_Id int NOT NULL,  
    Order_No  int NOT NULL,  
    S_Id int,  
    PRIMAY KEY (O_Id),  
    FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)  
    )  
    

    If you want to drop a FOREIGN KEY constraint, use the following syntax:

    ALTER TABLE Orders  
    ROP FOREIGN KEY fk_PerOrders  
    
    Default Constraint

    The DEFAULT constraint is used to provide a default value for a column.The default value will be added to all new records IF no other value is specified.

    SQL DEFAULT on CREATE TABLE

    The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:

    CREATE TABLE Persons (
        ID int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Age int,
        City varchar(255) DEFAULT 'Sandnes'
    );
    

    The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

    CREATE TABLE Orders (
        ID int NOT NULL,
        OrderNumber int NOT NULL,
        OrderDate date DEFAULT GETDATE()
    );
    

    To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:

    ALTER TABLE Persons
    ALTER City SET DEFAULT 'Sandnes';
    

    To drop a DEFAULT constraint, use the following SQL:

    ALTER TABLE Persons
    ALTER City DROP DEFAULT;
    
    Dropping Tables and Databases
    DROP TABLE

    The DROP TABLE statement is used to drop an existing table in a database.

    Syntax
    DROP TABLE table_name;

    An example is:

     DROP TABLE Shippers;

    The Output will be:

    You have made changes to the database.
    TRUNCATE TABLE

    The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

    Syntax
    TRUNCATE TABLE table_name;
    DROP DATABASE

    The DROP DATABASE statement is used to drop an existing SQL database.

    Syntax
    DROP DATABASE databasename;

    An Example of drop database is given below:

    DROP DATABASE testDB;
    Introduction to Phpmyadmin

    PhpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still can directly execute any SQL statement.

    PhpMyAdmin comes with a wide range of documentation and users are welcome to update our wiki pages to share ideas for various operations. The phpMyAdmin team will try to help you if you face any problem; you can use a variety of support channels to get help.

    PhpMyAdmin is also very deeply documented in a book written by one of the developers – Mastering phpMyAdmin for Effective MySQL Management, which is available in English and Spanish.

    To ease usage to a wide range of people, phpMyAdmin is being translated into 72 languages and supports both LTR and RTL languages.

    To access phpMyAdmin, click on the phpMyAdmin link, which can be found under the databases section of cPanel Home.

    You will see a dashboard similar to this.

    On the left-hand pane, there is a list of databases, which can be expended to see the table in each database. On the right-hand pane, there is general information of Database server.

    Execute an SQL Query

    To execute an SQL Query on a database, we will need to perform the following steps:

    Step 1: Select the database from left pane by clicking over the name of the database. Once the database is selected, it will show you the structure of the database.

    Step 2: Click on SQL from top bar to open SQL editor.

    Step 3 – Write your query into the SQL editor. You can also enter multiple queries, separated by semicolon (;).

    Step 4 − Click Go button to execute the query. If the query is executed successfully, you will get a success message.

    Mysqli connectivity

    You can establish MySQLi database using mysql binary at command prompt.

    Example

    Here is a simple example to connect to MySQL server to establish mysqli database from command prompt:

    [root@host]# mysql -u root -p
    Enter password:******
    

    This will give you mysql> command prompt where you will be able to execute any SQL command. Following is the result of above command:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2854760 to server version: 5.0.9
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    In above example, we have used root as a user, but you can use any other user. Any user will be able to perform all the SQL operations, which are allowed to that user.

    You can disconnect from MySQL database any time using exit command at mysql> prompt.

    mysql> exit
    Bye
    
    MySQLi Connection using PHP Script

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

    Syntax
    connection mysqli_connect(server,user,passwd,new_link,client_flag);

    Here,

    • Server: The host name running database server. If not specified, then default value is localhost:3306.
    • User: The username accessing the database. If not specified, then default is the name of the user that owns the server process.
    • Passwd: The password of the user accessing the database. If not specified, then default is an empty password.
    • new_link: If a second call is made to mysqli_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.
    • client_flags: A combination of the following constants:
      • MYSQLI_CLIENT_SSL: Use SSL encryption
      • MYSQLI_CLIENT_COMPRESS: Use compression protocol
      • MYSQLI_CLIENT_IGNORE_SPACE: Allow space after function names
      • MYSQLI_CLIENT_INTERACTIVE: Allow interactive timeout seconds of inactivity before closing the connection

    You can disconnect from MySQLi database anytime using another PHP function mysqli_close(). This function takes a single parameter, which is a connection returned by mysqli_connect() function.

    Syntax
    bool mysqli_close ( resource $link_identifier );

    If a resource is not specified then last opened database is closed. This function returns true if it closes connection successfully otherwise it returns false.

    Example

    Try out the following example to connect to a MySQL server:

    <html>
    <head>
    <title>Connecting MySQLi Server</title>
    </head>
    
    <body>
    <?php
             $dbhost = 'localhost:3306';
             $dbuser = 'guest';
             $dbpass = 'guest123';
             $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
             if(! $conn ){
                die('Could not connect: ' . mysqli_error());
             }
             echo 'Connected successfully';
             mysqli_close($conn);
          ?>
    </body>
    </html>
    

    If above example is connected with MySQLi, then the output should be like this on your browser

    Connected successfully
    Summary

    The following below points summarize the topics discussed above:

    • A Relational Database Management System (RDBMS) has data is structured into tables, where each table has some number of columns, each of which has a name and a type.
    • A web database gives you the ability to build your own databases/data storage without you being a database guru or even a technical person.
    • A Database Management system can be centralised, decentralised or hierarchical, depending upon its architecture.
    • Database Architecture is logically of two types of architecture. They are 2-tier DBMS architecture and 3-tier DBMS architecture.
    • PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.
    • After creating database we can use mysql_query() function to create table.
    • MySQL uses many different data types broken into three categories i.e. Numeric, Date and Time and String Types.
    • Aggregate functions in MySQL are a group of functions that are used to operate on a set of values.
    • The HAVING and GROUP clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
    • A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
    • JOIN are of following types: Inner Join, Left Join, Right Join and Full Join.
    • Primary key, Unique Key, Composite key, foreign key and other default constraint can define relation and properties between data members.
    • Database can be dropped using DROP keyword.
    • Tables can be dropped and truncated using DROP and TRUNCATE keyword simultaneously.
    • PhpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web.
    • MySQLi is a Relational SQL database management system that allows you to access MySQL database servers.

    Enrolled Yourself – PHP Training

    Copyright 1999- Ducat Creative, All rights reserved.