I have created a chat system where students and academic staff can communicate between them. I have developed a form were I can get the name of the club were the conversation exists, and the value of the staff_id for another reason.
The problem is that I can not delete the records from 3 tables. I know that first I have to check if a specific id exists in staff_table or stu_table but I cannot figure out the query that I have to use.
The first table is the staff_message (columns: staff_id, m_id), the second is the stu_message (columns: stu_id, mid) and the third is the message table (columns: id, text, date, type).
Here you can see my code:
<?php
include_once('connect.php');
$name=mysqli_real_escape_string($con,$_POST['clubname']);
$staff_id=mysqli_real_escape_string($con,$_POST['staff_id']);
if(empty($name)) {
header("Location: ../options.php?index==empty");
exit();
}
else {
$quer = mysqli_query($con,"SELECT id FROM message WHERE type='$name'");
while($row = mysqli_fetch_array($quer)) {
$message_id = $row['id'];
}
$query4 = mysqli_query($con,"SELECT * FROM staff_message WHERE m_id='$message_id'");
if(mysqli_num_rows($query4) >= 1)
{
$query5 = mysqli_query($con,"DELETE FROM staff_message WHERE m_id='$message_id'");
} else {
echo "not here staff"; //see if finds the value
}
$query8 = mysqli_query($con,"SELECT * FROM stu_message WHERE mid='$message_id'");
if(mysqli_num_rows($query8) >= 1)
{
$query5 = mysqli_query($con,"DELETE FROM stu_message WHERE mid='$message_id'");
} else {
echo "not here stu";//see if finds the value
}
$query= mysqli_query($con,"DELETE FROM message WHERE type = '$name'");
header("Location: ../options.php?delete==success");
}
?>
Thank you in advance!
EDIT: I tested each code separately and I saw that they both work. What I mean is that when there is only one row of student message then the message is deleted as well as the row in reference table (stu_message). That also applies for the academic staff table.
