[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to delete row from second table (Image_Question) Table

Posted on 2012-09-20
2
Medium Priority
?
445 Views
Last Modified: 2012-09-20
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.
0
Comment
Question by:carlbrooks1995
2 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 38420273
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
 

Author Comment

by:carlbrooks1995
ID: 38420290
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question