How to delete row from second table (Image_Question) Table

I want to delete a row from the database depending on what the file name of the image is. Lets say I have 2 tables below:

    Image Table:
   
    ImageId  ImageFile
   
    01       cat.png
    02       dog.png
    03       dog_2.png
   
    Image_Question Table:
   
    ImageId   SessionId   QuestionId
    01        AAA              4
    02        ABD              1
    03        RTD              11

Lets say in my application I delete the file image dog_2.png, then I want it to delete the row which states dog_2.png in the Image Table (This is working fine) and be able to delete the row from the Image_Question Table where the row contains the same ImageId associated with the ImageId and ImageFile name from the Image Table (This is not working).

So for the above example the 2 tables should now look like this after deletion:

    Image Table:
   
    ImageId  ImageFile
   
    01       cat.png
    02       dog.png
   
    Image_Question Table:
   
    ImageId   SessionId   QuestionId
    01        AAA              4
    02        ABD              1

But it does not delete the row from the Image_Question Table, how can I get this row to delete?

Below is the full code where it deletes the row from the Image Table and it contains most of the code which has been setup but not fully completed on deleting a row from the Image_Question Table:

   
 $image_file_name = $_GET["imagefilename"];
    $img = "ImageFiles/$image_file_name";
    
    echo "$image_file_name was Deleted";
    unlink("ImageFiles/$image_file_name");
    
    $imagedeletesql = "DELETE FROM Image WHERE ImageFile = ?";
    
    if (!$delete = $mysqli->prepare($imagedeletesql)) {
        // Handle errors with prepare operation here
    }
    
    //Don't pass data directly to bind_param; store it in a variable
    $delete->bind_param("s",$img);
    
    $delete->execute();
    
    if ($delete->errno) {
        // Handle query error here
    }
    
    $delete->close();
    
    $imagequestiondeletesql = "DELETE FROM Image_Question WHERE ImageId = ?";
        
    if (!$deleteimagequestion = $mysqli->prepare($imagequestiondeletesql)) {
        // Handle errors with prepare operation here
    }
        
    // Don't pass data directly to bind_param; store it in a variable
    $deleteimagequestion->bind_param("s",....);
    
    $deleteimagequestion->execute();
    
    if ($deleteimagequestion->errno) {
        // Handle query error here
    }
    
    $deleteimagequestion->close();  

Open in new window


Am I able to use ON DELETE CASCADE, if so does it need to go in my php code and if so how. Or does it simply go in the sql code, can you show me how to write the sql for ON DELETE CASCADE.
carlbrooks1995Asked:
Who is Participating?
 
GaryCommented:
I haven't tested this but it should work

DELETE image, Image_Question FROM image 
INNER JOIN Image_Question 
WHERE image.imageid= Image_Question.imageid 
AND image.ImageFile = 'myimagename';

Open in new window

0
 
carlbrooks1995Author Commented:
I want to use bind_params in mysqli because it is safer, does that mean the code is like this:

DELETE Image, Image_Question FROM Image
INNER JOIN Image_Question
WHERE Image.ImageId= ?
AND Image.ImageFile = ?;

$delete->bind_param("ss",Image_Question.ImageId ,$img);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.