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.
Rate this post
5 of 5 based on 9674 votesComments