John Davidson

Message:

I want to link when these two tables which app_lect column in appointment table's Chai is the same as the value Chai in the lecturer table.

Then I want to display out the appointment time, date, student name which is same row with the app_lect (value Chai).

This first table is about the appointment form data insert into the database

        CREATE TABLE `appointment` (

      `stud_name` varchar(100) NOT NULL,

      `stud_num` varchar(20) NOT NULL,

      `app_course` enum('A','B','C','D','E') NOT NULL,

      `app_lect` enum('Chai','Lim','Diana','Siti','Joseph') NOT NULL,

      `app_date` date NOT NULL,

      `app_time` time(6) NOT NULL,

      `app_reason` varchar(255) NOT NULL,

      `app_approve` enum('pending','approve','reject') NOT NULL,

      `app_id` int(11) NOT NULL

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This is the data insert into appointment table.

        INSERT INTO `appointment` (`stud_name`, `stud_num`, `app_course`, `app_lect`, `app_date`, `app_time`, `app_reason`, `app_approve`, `app_id`) VALUES

    ('Chong Jun Xuan', 'A1111', 'A', 'Chai', '2021-04-15', '12:50:00.000000', 'Discussion', 'pending', 19);

Table structure for table lecturer

     CREATE TABLE `lecturer` (

   `user_id` int(11) NOT NULL,

   `staff_ID` varchar(20) NOT NULL,

   `lect_name` varchar(50) NOT NULL,

   `lect_email` varchar(50) NOT NULL,

   `lect_password` varchar(50) NOT NULL,

   `lect_phonenum` varchar(20) NOT NULL,

   `lect_course` enum('A','B','C','D','E') NOT NULL,

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Data insert into lecturer table

        INSERT INTO `lecturer` (`user_id`, `staff_ID`, `lect_name`, `lect_email`, `lect_password`, `lect_course`) VALUES

    (1, 'A1001', 'Chai', '[email protected]', 'chai', 'A'),

    (2, 'A1002', 'Lim', '[email protected]', 'lim', 'B'),

    (3, 'A1003', 'Diana', '[email protected]', 'diana', 'B'),

    (4, 'A1004', 'Siti', '[email protected]', 'siti', 'C'),

    (5, 'A1005', 'Josept', '[email protected]', 'josept', 'D');

    

alter table

        ALTER TABLE `appointment`

      ADD PRIMARY KEY (`app_id`);

        ALTER TABLE `lecturer`

      ADD PRIMARY KEY (`user_id`);

        ALTER TABLE `appointment`

      MODIFY `app_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

        ALTER TABLE `lecturer`

      MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

    

Coding for displaying the details from the appointment table.

            <?php 

            $sql= "SELECT * FROM appointment,lecturer WHERE appointment.app_lect= lecturer.lect_name order by app_id";

            $result = mysqli_query($con,$sql)or die( mysqli_error($con));

    

            

             echo    "<table>

            <tr>

            <th>Appointment ID</th>

            <th>Student name</th>

                <th>Student Number</th>

                <th>Appointment date</th>

                <th>Appointment time</th>

                <th>Appointment reason</th>

            </tr>";

          while($row=mysqli_fetch_array($result)){ 

               echo "<tr>";

                                    echo "<td>".$row['app_id']."</td>";

                                    echo "<td>".$row['stud_name']."</td>";

                                    echo "<td>".$row['stud_num']."</td>";

                                    echo "<td>".$row['app_date']."</td>";

                                    echo "<td>".$row['app_time']."</td>";

                                    echo "<td>".$row['app_reason']."</td>";

                                    echo "</tr>";

           }

                 echo "</table>";     

                 

            ?>

I would like to know the following:

• SQL query code for displaying the data from the appointment table


• link this two tables as when appointment.app_lect=lecturer.lect_name so display the data in row with the condition

---

My Answer:
@gmail.com', 'password123', 'A');__To display the data row in the appointment table where the app_lect column value is equal to 'Chai' in the lecturer table, you can use a SQL query with a JOIN statement. Here's an example query:

sql
SELECT a.app_date, a.app_time, a.stud_name
FROM appointment a
JOIN lecturer l ON a.app_lect = l.lect_name
WHERE l.lect_name = 'Chai';


This query will retrieve the appointment date, time, and student name from the appointment table where the app_lect column value matches 'Chai' in the lecturer table.