How To Use SQL ALIASES STATEMENT With Example In PHP

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.

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

How To Use SQL DELETE Statement In Query Using PHP
How To Use SQL MINIMUM and MAXIMUM Functions In PHP
How To Use SQL COUNT, AVG And SUM Functions In PHP
How To Use SQL LIKE Operator With Example Using PHP
How To Use SQL BETWEEN Operator With Example In PHP
How To Use SQL IN Operator In Query With Example In PHP
How To Use SQL INNER JOIN Query With Example In PHP
How To Use SQL LEFT JOIN Query With Example In PHP
How To Use SQL RIGHT JOIN Query With Example In PHP
How To Use SQL UNION OPERATOR With Example Using PHP
How To Use FULL OUTER JOIN Query With Example Using PHP
How To Use SQL GROUP BY STATEMENT With Example In PHP
How To Use SQL HAVING STATEMENT With Example In PHP
How To Use SQL ALTER STATEMENT With Example In PHP