Getting around multiple SQL queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Hendeca
Forum Commoner
Posts: 29
Joined: Tue Nov 18, 2008 1:27 pm

Getting around multiple SQL queries

Post by Hendeca »

Hello,

I was having a problem with the unlink() function but thanks to everyone on these forums, I realize it was actually an SQL problem. Here's the code:

Code: Select all

  
    if (isset($_GET['photo_id']) && !$_POST) {
        //prepare SQL query
        $sql = 'SELECT photo_id, photo_name, file_name, thumb_name
                FROM wallsanddoors WHERE photo_id = ?';
        //initialize statement
        $stmt = $conn->stmt_init();
        if ($stmt->prepare($sql)) {
            //bind the query parameters
            $stmt->bind_param('i', $_GET['photo_id']);
           //bind the results to variables
           $stmt->bind_result($photo_id, $photo_name, $file_name, $thumb_name);
           //execute the query and fetch the result
           $OK = $stmt->execute();
           $stmt->fetch();
           }
       }
   // if confirm deletion button has been clicked, delete record
   if (array_key_exists('delete', $_POST)) {
     unlink(DELETE_DIR.$file_name);
     unlink(THUMBS_DIR.$thumb_name);
     $sql = 'DELETE FROM wallsanddoors WHERE photo_id = ?';
     $stmt = $conn->stmt_init();
     if ($stmt->prepare($sql)) {
       $stmt->bind_param('i', $_POST['photo_id']);
       $deleted = $stmt->execute();
       }
     }
 
So this is from a photo delete page. The photo itself isn't kept in the database, but the filename that links to the photo file and photo info is kept in the database. The first SQL query gets the photo info so that it is displayed next to the "confirm deletion" button. After the deletion button is pressed, the record is deleted. The problem is that the $file_name and $thumb_name variables used in the unlink() functions no longer contain info after the delete button has been clicked. If I put the unlink() function in the first if statement, then the photos will be deleted before the confirm deletion button is pressed. If I put them in the second if statment (like they are now), then they are using empty variables and the proper file path isn't shown. What I need to do is to both delete a record and query the database to get the file info. I've tried creating a constant from the first SQL query but that didn't seem to work. I also tried making the second if statement query the database for the $file_name variable, and a third that deletes the record. This didn't work either, but maybe I didn't execute it correctly?

Does anyone know how I can achieve this goal? I'm having a very hard time getting this to work. Thanks so much for your help!
Oxidiser
Forum Newbie
Posts: 15
Joined: Wed Feb 18, 2009 5:00 am
Location: The Netherlands

Re: Getting around multiple SQL queries

Post by Oxidiser »

That is because you only execute the fetching query when no post has been set. You should pass those variables with your post request, or do the fetch query twice.

Code: Select all

if (isset($_GET['photo_id']) && !$_POST) {
So essentially use $_POST['photo_id'], $_POST['file_name'] and $_POST['thumb_name'] in the Second part.
Post Reply