[RESOLVED] Delete album and items related to the album HELP

[lisa007]

i do think is a small mistake i making can u please have a look if u can
ok here's the problem i am trying to delete an album within the album should also delete the photos related to that album this what i tried gives this error

Delete image failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

my tables are
table albums fields album_id, album_name, album_owner, sub_album
table photos fields, photo_id, photo_name, photo_extension, photo_proper photo_owner, photo_date, photo_comments, photo_size, album_id

photo_proper is the name image stored in folder

<?php

define('ROOT_DIR', './');

define('PROPER', TRUE);

/**

* include common files

*/

include_once(ROOT_DIR. 'includes/common.inc.php');





// No album id has been selected

if (isset($_GET['albums']))

   // get the album name since we need to display

   // a message that album 'foo' is deleted

   $result = mysql_query("SELECT album_id, album_name, album_owner, sub_album

                      FROM albums

                      WHERE album_id = $album_id")

         or die('Delete image failed. ' . mysql_error());

   if (mysql_num_rows($result) == 1) {

  $row        = mysql_fetch_assoc($result);

  $album_id  = $row['album_id'];

  $album_name = $row['album_name'];



  // get the image filenames first so we can delete them

  // from the server

  $result = mysql_query("SELECT photo_id, photo_id

                         FROM photos

                         WHERE album_id = $album_id")

            or die(mysql_error());

  while ($row = mysql_fetch_assoc($result)) {



define("GALLERY_IMG_DIR", "./photos/");





  unlink(GALLERY_IMG_DIR . $row['photo_proper']);

  unlink(GALLERY_IMG_DIR . 'thumbs/' . $row['photo_proper']);



  }



  $result = mysql_query("DELETE FROM photos

                         WHERE album_id = $album_id")

            or die('Delete image failed. ' . mysql_error());

  $result = mysql_query("DELETE FROM album

                         WHERE album_id = $album_id")

            or die('Delete album failed. ' . mysql_error());



  // album deleted successfully, let the user know about it

  echo "<p align=center>Album '$album_name' deleted.</p>";

   } else {

  echo "<p align=center>Cannot delete a non-existent album.</p>";

   }







?>

second method error line 5

<?php

define('ROOT_DIR', './');

define('PROPER', TRUE);

/**

* include common files

*/

include_once(ROOT_DIR. 'includes/common.inc.php');





// No album id has been selected

if (isset($_GET['albums']))

   // get the album name since we need to display

   // a message that album 'foo' is deleted

   $result = mysql_query("SELECT album_id, album_name, album_owner, sub_album

                      FROM albums

                      WHERE album_id = $album_id")

         or die('Delete image failed. ' . mysql_error());

   if (mysql_num_rows($result) == 1) {

  $row        = mysql_fetch_assoc($result);

  $album_id  = $row['album_id'];

  $album_name = $row['album_name'];



  // get the image filenames first so we can delete them

  // from the server

  $result = mysql_query("SELECT photo_id, photo_id

                         FROM photos

                         WHERE album_id = $album_id")

            or die(mysql_error());

  while ($row = mysql_fetch_assoc($result)) {



define("GALLERY_IMG_DIR", "./photos/");





  unlink(GALLERY_IMG_DIR . $row['photo_proper']);

  unlink(GALLERY_IMG_DIR . 'thumbs/' . $row['photo_proper']);



  }



  $result = mysql_query("DELETE FROM photos

                         WHERE album_id = $album_id")

            or die('Delete image failed. ' . mysql_error());

  $result = mysql_query("DELETE FROM album

                         WHERE album_id = $album_id")

            or die('Delete album failed. ' . mysql_error());



  // album deleted successfully, let the user know about it

  echo "<p align=center>Album '$album_name' deleted.</p>";

   } else {

  echo "<p align=center>Cannot delete a non-existent album.</p>";

   }







?>

[Krik]

Ok there is couple issues here.

In the first query

$result = mysql_query("SELECT album_id, album_name, album_owner, sub_album

           FROM albums

           WHERE album_id = $album_id"

"$album_id" is not defined anywhere and as that is line 3 that may be your problem.

One thing you could do to check which query is failing is to make unique "die" messages for each query.

or die('My unique fail message for my very special query' . mysql_error());

Next you are trying to get the "photo_proper" columns value but none of your queries are set to retrieve that column. "SELECT" defines which columns are retrieved. Using the star (*) retrieves all columns, otherwise you have to specifically state the columns you are retrieving in the "SELECT"

[cretaceous]

you should also always put your $album_id variable in single quotes within your SQL:

$result = mysql_query("SELECT album_id, album_name, album_owner, sub_album FROM albums WHERE album_id = '$album_id' " 

if $album_id is not set you will get an error - surrounding it in quotes will stop that error (though that may or may may not be the problem here)