How To Use SQL ALIASES STATEMENT With Example In PHP

admin_img Posted By Bajarangi soft , Posted On 18-09-2020

SQL ALIASES used to give temporary names for columns or tables and its readable. ALIASES its used in JOIN operations or aggregate functions. ALIASES temporary names,only used for duration of select query.

SQL Aliases

Syntax for an Alias Column

SELECT column_name AS temporary_name
FROM table_name;


Syntax for an Alias Table

SELECT column_name list
FROM table_name AS temporary_name;
 

Lets Learn.
1.Create database and  table.
Student 
table.

StudentID StudentName ParentsContact Address Country Section Branch Percentage Fee CourseID
1 Liam 10101010 23 Andrew road USA A ISE 66 66000 5
2 Andrew 20202020 2415 Anthony  market str Germany A ISE     80 65000 6
4 Ryan 40404040 18 Christopher sq. INDIA B EE 67 70000 5
5 Charles 50505050 878 Eli Berguvsvägen 8 UK C ECE 52 75000 8
8 Anikth 10101010 23 Andrew road AFRICA A CSE 66 66000 3
9 Andrew 20202020 2415 Anthony  market str Germany A ISE 80 65000 2
10 Bhavya 40404040 18 Christopher sq. INDIA B CE 67 7000 2


Course table.
CourseID CourseName CourseCode
1 Mechanical ME20
2 Chemical C20
3 Civil CL20
4 Electrical E20

We use student table and course table for SQL Aliases.

Example(1)
SELECT `StudentID` AS ID, `StudentName` AS Name FROM student;
selecting studentID and assigning  temporary names(alias-name) as ID and  studentName and assigning  temporary names(alias-name) as  Name from student table.

Example(2)
SELECT c.CourseID, c.CourseName, s.StudentName FROM student AS s, course AS c WHERE s.StudentName='Andrew' AND s.CourseID=c.CourseID

selecting courseid and coursename from course table using temporary names (alias-name) and student name from student table using temporary name(alias-name). where student name is Andrew.

2.Complete code of SQL Aliases in PHP script
<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL Aliases</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="text-center">
        <h1>SQL Aliases</h1>
    </div>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "school_management");

        if ($connect->connect_error) {
            die("Connection failed: " . $connect->connect_error);
        }

        $sql_1 = "SELECT `StudentID` AS ID, `StudentName` AS Name FROM student;";
        $sql_2 = "SELECT c.CourseID, c.CourseName, s.StudentName FROM student AS s, course AS c WHERE s.StudentName='Andrew' AND                                                                                                                          
                                  s.CourseID=c.CourseID";
        echo "<h4 class='text-danger'>Example(1)</h4>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>ID</th>
                     <th>Name</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['ID'] /*student table*/ ."</td> 
                   <td>".$row['Name']  ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h4 class='text-danger'>Example(2) </h4>";
        if($result_1 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>CourseID</th>
                     <th>CourseName</th>
                     <th>StudentName</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['CourseID'] /*student table*/ ."</td> 
                   <td>".$row['CourseName'] ."</td>
                   <td>".$row['StudentName'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
       ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post