Issue deleting data via PHP/MySQL

Question

mexabet 49 Good Learner

8 Years Ago

I'm experiencing an issue deleting data via PHP/MySQL query.
The following is the error message:

Delete product category 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

This is the PHP code that I'm using:

<?php

if (isset($_GET['delete']) && isset($_GET['album']) && isset($_GET['imgId'])) {
    // get the image file name so we
    // can delete it from the server 
    $sql = "SELECT im_image, im_thumbnail
            FROM tbl_image
            WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
    if (mysql_num_rows($result) == 1) {
        $row = mysql_fetch_assoc($result);

        // remove the image and the thumbnail from the server
        unlink(GALLERY_IMG_DIR . $row['im_image']);
        unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);

        // and then remove the database entry
        $sql = "DELETE FROM tbl_image
                WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
        mysql_query($sql) or die('Delete product category failed. ' . mysql_error());       

    }   
}

$imagePerPage = 10;

$album = isset($_GET['album']) ? $_GET['album'] : '';
$pageNumber  = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;

$offset = ($pageNumber - 1) * $imagePerPage;
$serial = $offset + 1;

// get album list
$sql = "SELECT al_id, al_name
        FROM tbl_album
        ORDER BY al_name";
$result = mysql_query($sql) or die('Error, get product category list failed : ' . mysql_error());                    

$albumList = '';
while ($row = mysql_fetch_assoc($result)) {
    $albumList .= '<option value="' . $row['al_id'] . '"' ;

    if ($row['al_id'] == $album) {
        $albumList .= ' selected';
    }

    $albumList .= '>' . $row['al_name'] . '</option>';   
}
?>

The line 3 of the code is this:

if (isset($_GET['delete']) && isset($_GET['album']) && isset($_GET['imgId'])) {

Your help will always be appreciated.

javascript mysql php

3 Contributors
28 Replies
767 Views
5 Days Discussion Span
Latest Post 8 Years Ago Latest Post by cereal

All 28 Replies

diafol

8 Years Ago

SELECT im_image, im_thumbnail
        FROM tbl_image
        WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}

This is BIG MASSIVE SQL INJECTION waiting to happen. Never, ever, place raw user input into an SQL statement. See DW Tutorial: Common Issues with MySQL and PHP for the relevant sections on this.

Can't remember if you're the guy who says he has to use mysql instead of mysqli/PDO, but if you are, then use sanitize methods and/or sprintf().

Edited 8 Years Ago by diafol

mexabet commented: Nice tips +3

diafol

8 Years Ago

Well, the problem you say comes from the statement itself. Echo the statement to the screen so you can see what it gives you. See if this statament works in phpMyAdmin.

diafol

8 Years Ago

You can set a conditional statement to account for the missing im_album:

if(isset($_GET['album'] && trim($_GET['album']) && ...other fields...)

However, filter_input() or even filter_input_array() would be better IMO.

http://php.net/manual/en/function.filter-input.php
http://php.net/manual/en/function.filter-input-array.php

BTW you're problem is that the input field 'album' if hidden / text etc is ALWAYS set, even if "empty", unlike checkboxes / radiobuttons which are not sent if unchecked.

Edited 8 Years Ago by diafol

cereal 1,524 Nearly a Senior Poster

8 Years Ago

Replace code from line 2 to 24 with:

$error = FALSE;

// check if index key exists and trim, else set it to NULL
$delete  = array_key_exists('delete', $_GET) ? trim($_GET['delete']) : NULL;
$albumId = array_key_exists('album', $_GET) ? trim($_GET['album']) : NULL;
$imgId   = array_key_exists('imgId', $_GET) ? trim($_GET['imgIde']) : NULL;

if( ! empty($delete) && ! empty($albumId) && ! empty($imgId))
{
    // get the image file name so we
    // can delete it from the server 
    $sql = sprintf(
            "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '%s' AND im_album_id = '%s'",
            mysql_real_escape_string($imgId),
            mysql_real_escape_string($albumId)
        );

    $result = mysql_query($sql);

    if($result && mysql_num_rows($result) == 1)
    {
        $row = mysql_fetch_assoc($result);

        // and then remove the database entry
        $sql = sprintf(
                "DELETE FROM tbl_image WHERE im_id = '%s' AND im_album_id = '%s'",
                mysql_real_escape_string($imgId),
                mysql_real_escape_string($albumId)
                );

        // remove the image and the thumbnail from the server
        // only if DELETE query is successful
        if(mysql_query($sql) && mysql_affected_rows() > 0)
        {
            unlink(GALLERY_IMG_DIR . $row['im_image']);
            unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);  
        }

        else
            $error = 'Delete product category failed. ' . mysql_error();
    }

    else
        $error = 'Select product category failed. ' . mysql_error();
}

In practice we initialize some variables ($delete, $albumId & $imgId) by trimming the contents of $_GET, then we refer to these variables for the rest of the script: this is an important point, if you call $_GET['album'] inside the conditional statement you can insert an untrimmed value, clear?

I'm using empty() to check the contents of the variables, please refer to the documentation to check what is considered empty:

http://php.net/empty

if you expect to set any of those values then my suggested conditional statement will fail and your query will not work. So, if any of these are expected let us know so we can suggest an appropriate solution.

Now, mysql_query() returns TRUE on success, FALSE on failure, but it will return TRUE even if it does not find rows and in production is not good to use mysql_query(...) OR die(mysql_error()); because it will expose errors to the users.

Usually I see a lot of people placing an error control operator @ in front of mysql_query() but this does not help you or the users to get a better experience.

So instead of writing:

$query = mysql_query($sql) or die(mysql_error());  
if(mysql_num_rows($query) == 1)

Do:

$result = mysql_query($sql);
if($result && mysql_num_rows($query) == 1)

By adding $result to the IF statement you check is TRUE and avoid mysql_num_rows() from sending:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

with the INSERT, UPDATE & DELETE statements you have to do something similar, but instead of mysql_num_rows() you have to use mysql_affected_rows():

if(mysql_query($sql) && mysql_affected_rows() > 0)

Documentation:

Best solution would be to know which kind of input you intend to allow and use the filter functions as suggested by diafol in his previous post.

To finish, the ELSE statements here are now printing the errors without stopping the execution of the script, so the page can render and show something to the end user. What you can do here, is to replace the original error message sent from the function, with something generic, just to let know the user that something went wrong.

On your side, instead, you could use error_log() to get appropriate information. As example, you could replace the actual ELSE statements with:

else
{
    error_log('[MySQL ERROR] ' . mysql_error(), 0);
    $error = 'An error occurred, we are aware of it and will fix it ASAP. Thank you.';
}

And then set something like this in the body of the page:

if($error)
    echo "<div>{$error}</div>";

With the above setting the error_log() function will send the errors to the system error log file:

http://php.net/manual/en/function.error-log.php

In general, try not to nest PHP code inside template files, it makes it difficult to read and six months from now it will be a nightmare to maintain.

cereal 1,524 Nearly a Senior Poster

8 Years Ago

I think I've understood where is the issue (finally :D): $_GET['album'] is set but it does not return any value. At line 28 of your last code version you had:

$album = isset($_GET['album']) ? $_GET['album'] : '';

Which, will initialize the variable but it will not return nothing when opening the script for the first time because, from what I see, the page will not have a query string with this value. In other words you probably can open the page like this:

http://site.tld/script.php

and/or:

http://site.tld/script.php?album=123

So the value of $album in the first case be empty. Later, in the while loop, you have the javascript function with:

javascript:deleteImage(<?php echo "'$album', '$im_id'"; ?>);

Which should get the value of album from $row rather than the $album variable previously set. The extract() function will initialize $im_id, it will not initialize $album because it DOES NOT exists in the columns returned by $row. So:

you don't get a notice for undefined variable $album;
the $album value arriving here is the one set at line 28 which, as said, can be empty.

I think you can solve the issue by changing the SELECT query at line 61 to return the im_album_id column:

SELECT im_id, im_album_id, im_title, im_thumbnail, DATE_FORMAT(im_date, '%d-%m-%Y') AS im_date FROM tbl_image

And change the javascript code to:

javascript:deleteImage(<?php echo "'$im_album_id', '$im_id'"; ?>);

After these changes the IF conditional statement should work. And var_dump() should return the expected values.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mexabet 49 Good Learner · Answer 1 · 2015-12-14T12:28:13+00:00

@diafol,
Thanks for pointing out the potential security flaw. I'm going through that linked article, to close that security hole.

Yes, I'm the same person who stated that I had a plausible reason to use MySQL instead of MySQLi.

Now any idea how to successfully delete data from the database?

mexabet 49 Good Learner · Answer 2 · 2015-12-14T16:38:36+00:00

I echoed the MySQL query and got the following error message:

SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = 1 AND im_album_id = Delete product category 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

It also didn't work in PHPMyAdmin:

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 'LIMIT 0, 30' at line 2

Still PHPMyAdmin:

SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id =1
AND im_album_id =
LIMIT 0 , 30

I guess the problem is coming from im_album_id that is empty. If that is the case, how do I fix it?

mexabet 49 Good Learner · Answer 3 · 2015-12-14T18:24:09+00:00

Thanks for your continued support. I'll follow your instruction to see if I can get it to work. And I'll update this post afterwards.

mexabet 49 Good Learner · Answer 4 · 2015-12-15T08:39:41+00:00

I changed that statement in line 3 to the following, and the error message stopped, but the file is not deleted:

if (isset($_GET['delete']) && trim($_GET['delete']) && isset($_GET['album']) && trim($_GET['album']) && isset($_GET['imgId']) && trim($_GET['imgId']))

You mentioned that the input field, 'album', if hidden is always set. Somewhere on the page I have this code:

<a href="javascript:deleteImage(<?php echo "'$album', $im_id"; ?>);">Delete</a>

When I hover my mouse on the "Delete" link, the tooltip is something like this, if 1 is the imgID:

javascript:deleteImage(", 1);

The issue is that I don't know how to fix it. Can you please, tell me how to fix it?

diafol · Answer 5 · 2015-12-15T09:58:41+00:00

No idea. This sounds as though you are loading the album id into the link on page load - nothing to do with your ajax.

mexabet 49 Good Learner · Answer 6 · 2015-12-15T14:29:05+00:00

I'm still not able to fix the issue. Is there a better way to write this select querry?

$sql = "SELECT im_image, im_thumbnail
            FROM tbl_image
            WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error()

I've changed the code to the following, but it didn't work:

$albumId = mysql_real_escape_string($_GET['album']);
    $imgId = mysql_real_escape_string($_GET['imgId']);

    // get the image file name so we
    // can delete it from the server 
    echo ($sql = "SELECT im_image, im_thumbnail
            FROM tbl_image
            WHERE im_id = $imgId AND im_album_id = $albumId");
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());

diafol · Answer 7 · 2015-12-15T23:53:58+00:00

WHat's not working? Getting the php to echo the album id on page load or the ajax code?
I have no idea why your SQL doesn't work sorry. You've seen the SQL echoed out to the screen? Are the vars being passed to the ajax script properly?

mexabet 49 Good Learner · Answer 8 · 2015-12-16T15:15:38+00:00

@diafol,
I meant that the first select query in the file generates syntax error, when I run the SQL query on PHPMyAdmin.
Both queries given below, which I've tried give similar error messages:

$sql = "SELECT im_image, im_thumbnail
                FROM tbl_image
                WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
        $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());

And this variation too:

$sql = "SELECT im_image, im_thumbnail
            FROM tbl_image
            WHERE im_id = $imgId AND im_album_id = $albumId");
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());

Please, can you write the correct query statement for replacement?

mexabet 49 Good Learner · Answer 9 · 2015-12-16T16:22:13+00:00

When I dumped the $sql var, I got this error message:

Error
SQL query: Documentation
$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId'  ";

MySQL said: Documentation
#1064 - 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 '$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND ' at line 1

When I inspected the source code of the "Delete" link for one image, I noticed that only its im_id was present where 13 is the im_id, while im_album_id was empty:

<a href="javascript:deleteImage('', '13');">Delete</a>

I expect that both im_id and im_album_id must not be empty like the following illustration:

<a href="javascript:deleteImage('8', '13');">Delete</a>

8 is the im_album_id and 13 is the im_id.

Any help to modify the MySQL query to populate both the im_album_id and im_id would be much appreciated.

diafol · Answer 10 · 2015-12-17T00:02:28+00:00

Sorry, taking time off from DW. Anybody else?

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 11 · 2015-12-17T02:14:34+00:00

Let's try. The error you are reporting:

MySQL said: Documentation
1064 - 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 '$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND ' at line 1

is a bit strange, because MySQL will show the statement starting from the failing point, so you have to look what is set before $sql = "SELECT im_image, ... not after. How this is happening makes me think that, this query, is nested in a previous string or something like that... If you could share your current code, it could help a bit.

Also, running trim() in the IF statement will not affect the original variable, for example:

# value is " hello"
if(trim($_GET['id'])) var_dump($_GET['id']);

You expect string(5) "hello" instead you will get string(6) " hello". Do:

$get['id'] = trim($_GET['id']);

Starting from PHP 5.4 isset() will give different results from what expected in previous versions, see example #2 at http://php.net/isset

In any case isset() will evaluate an empty string to true, so if $_GET['album'] is simply set:

http://link.tld/script.php?album

without adding any value, the function will return it true, for example:

$_GET['album'] = NULL;
var_dump(isset($_GET['album']) ? :FALSE);

$_GET['album'] = '';
var_dump(isset($_GET['album']) ? :FALSE);

will return:

bool(false)
bool(true)

And your query can return unexpected results.

So, can you show your updated code? I'm not sure which version are you currently trying.

mexabet 49 Good Learner · Answer 12 · 2015-12-17T08:38:05+00:00

@diafol, youve been of great help. Enjoy your time off.

@cereal, thanks for taking time to explain. As follows is the updated code:

<?php
if (isset($_GET['delete']) && trim($_GET['delete']) && isset($_GET['album']) && trim($_GET['album']) && isset($_GET['imgId']) && trim($_GET['imgId']))
{

    $albumId = mysql_real_escape_string($_GET['album']);
    $imgId = mysql_real_escape_string($_GET['imgId']);

    // get the image file name so we
    // can delete it from the server 
    $sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId'  ";
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
    if (mysql_num_rows($result) == 1) {
        $row = mysql_fetch_assoc($result);

        // remove the image and the thumbnail from the server
        unlink(GALLERY_IMG_DIR . $row['im_image']);
        unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);

        // and then remove the database entry
        $sql = "DELETE FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId' ";
        mysql_query($sql) or die('Delete product category failed. ' . mysql_error());        

    }    
}

$imagePerPage = 10;

$album = isset($_GET['album']) ? $_GET['album'] : '';
$pageNumber  = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;

$offset = ($pageNumber - 1) * $imagePerPage;
$serial = $offset + 1;

// get album list
$sql = "SELECT al_id, al_name
        FROM tbl_album
        ORDER BY al_name";
$result = mysql_query($sql) or die('Error, get product category list failed : ' . mysql_error());                    

$albumList = '';
while ($row = mysql_fetch_assoc($result)) {
    $albumList .= '<option value="' . $row['al_id'] . '"' ;

    if ($row['al_id'] == $album) {
        $albumList .= ' selected';
    }

    $albumList .= '>' . $row['al_name'] . '</option>';   
}
?>

<table width="100%" border="0" align="center" cellpadding="2" cellspacing="1">
    <tr> 
        <td align="right">Product Category : 
    <select name="cboAlbum" id="cboAlbum" onChange="viewImage(this.value)">
        <option value="">-- All Categories --</option>
        <?php echo $albumList; ?> 
    </select></td>
    </tr></table>
<?php
$sql  = "SELECT im_id, im_title, im_thumbnail, DATE_FORMAT(im_date, '%d-%m-%Y') AS im_date
         FROM  tbl_image ";

if ($album != '') {
    $sql .= "WHERE im_album_id = $album ";
}

$sql .= "ORDER BY im_title ";

$result = mysql_query($sql . "LIMIT $offset, $imagePerPage") or die('Error, displaying product failed. ' . mysql_error());
?>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="1" class="table_grey">
    <tr> 
        <th width="30" align="center">#</th>
        <th align="center">Products</th>
        <th width="120" align="center"> Date</th>
        <th width="60" align="center">&nbsp;</th>
        <th width="60" align="center">&nbsp;</th>
    </tr>
    <?php 
if (mysql_num_rows($result) == 0) {
?>
    <tr bgcolor="#FFFFFF"> 
        <td colspan="5">No product in this Product Category</td>
    </tr>
    <?php
} else {
    while ($row = mysql_fetch_assoc($result)) {
        extract($row);  
?>
    <tr bgcolor="#FFFFFF"> 
        <td width="30" align="center"><?php echo $serial++; ?></td>
        <td align="center"><a href="../admin/?page=image-detail&amp;imgId=<?php echo $im_id; ?>"><img src="../products/viewImage.php?type=glthumbnail&amp;name=<?php echo $row['im_thumbnail']; ?>" border="0"><br>
      <?php echo $row['im_title']; ?></a></td>
        <td width="120" align="center"><?php echo $im_date; ?></td>
        <td width="60" align="center"><a href="../admin/?page=modify-image&amp;imgId=<?php echo $im_id; ?>">Modify</a></td>
        <td width="60" align="center"><a href="javascript:deleteImage(<?php echo "'$album', '$im_id'"; ?>);">Delete</a></td>
    </tr>
    <?php
    } // end while
}
?>
    <tr bgcolor="#FFFFFF"> 
        <td colspan="5" align="center"> <?php 
$result = mysql_query($sql);
$totalResults = mysql_num_rows($result);    

   echo getPagingLink($totalResults, $pageNumber, $imagePerPage, "page=list-image&album=$album");
   ?>&nbsp;</td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td colspan="5" align="right"><input type="button" name="btnAdd" value="Add Image" onclick="window.location.href='index.php?page=add-image&album=<?php echo $album; ?>';" /></td>
    </tr>
</table>

mexabet 49 Good Learner · Answer 13 · 2015-12-17T15:23:16+00:00

@cereal, I'm overwhelmed by your devotion and patience in enlightening me. I so much appreciate that.

Strangely enough, any attempt to delete any image in the desired database row doesn't work. This issue is too confusing to me. I just don't know why your code shouldn't work - it is cleaner and more professional.

if you expect to set any of those values then my suggested conditional statement will fail and your query will not work. So, if any of these are expected let us know so we can suggest an appropriate solution.

I'm not sure if any of those values should be set. I simply want to click on the "Delete" link next to any chosen image and the database row containing the image would be deleted.

The following is the JavaScript function I have in the index.php file that effects the deletion - Please, look into it too:

function deleteImage(albumId, imgId) 
    {
        if (confirm('Delete this product?')) {
            window.location.href = 'index.php?page=list-image&delete&album=' + albumId + '&imgId=' + imgId;
        }
    }

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 14 · 2015-12-17T16:06:24+00:00

Ok, then $_GET['delete'] will be set but it will be empty, by consequence the IF statement will fail. Try to change this line:

$delete  = array_key_exists('delete', $_GET) ? trim($_GET['delete']) : NULL;

To:

$delete  = array_key_exists('delete', $_GET);

Now we get a boolean and we don't need anymore to check the variable value. The IF statement changes from:

if( ! empty($delete) && ! empty($albumId) && ! empty($imgId))

To:

if($delete && ! empty($albumId) && ! empty($imgId))

Or more explicitly:

if($delete === TRUE && ! empty($albumId) && ! empty($imgId))

At this point the conditional statement should work fine.

mexabet 49 Good Learner · Answer 15 · 2015-12-17T16:17:22+00:00

mexabet 49 Good Learner

8 Years Ago

@cereal, unfortunately, it still doesn't work.

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 16 · 2015-12-17T17:25:33+00:00

Set:

echo "<pre>";
var_dump($_GET);
echo "</pre>";

in top of the script, then press the delete button and paste results here.

mexabet 49 Good Learner · Answer 17 · 2015-12-18T10:46:41+00:00

When I navigate to the page, I see the following code:

array(1) {
  ["page"]=>
  string(10) "list-image"
}

After pressing on the "Delete" link, the following code appeared:

array(4) {
  ["page"]=>
  string(10) "list-image"
  ["delete"]=>
  string(0) ""
  ["album"]=>
  string(0) ""
  ["imgId"]=>
  string(2) "19"
}

mexabet 49 Good Learner · Answer 18 · 2015-12-18T12:54:02+00:00

Thanks for your continued support. As follows is what the latest var dump() returned, after clicking the "Delete" button:

array(4) {
  ["page"]=>
  string(10) "list-image"
  ["delete"]=>
  string(0) ""
  ["album"]=>
  string(1) "8"
  ["imgId"]=>
  string(2) "19"
}

The var dump() returned the correct album ID and image ID, but nothing was deleted. Any idea why this is still happening?

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 19 · 2015-12-18T17:16:42+00:00

At this point I would check the contents of the $error variable, if any, and set few die() to check which IF statement is working and which fails. I'm supposing im_id and im_album_id are defined as a multiple-column unique index:

http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

Otherwise the first select query could return more than one row and this IF statement will fail, To start set:

if($result && mysql_num_rows($result) == 1)
{
    die('First select works');

And try the DELETE action, if you reach it, remove it and after the delete query set:

if(mysql_query($sql) && mysql_affected_rows() > 0)
{
    die('Delete query works');

And try again. If you reach it, we know both queries are working fine, at least in a case, and so there could be an issue with the unlink() function, which could be read/write permissions or wrong path.

The value of GALLERY_IMG_DIR is a internal path, not an HTTP link, correct?

mexabet 49 Good Learner · Answer 20 · 2015-12-19T11:03:12+00:00

Thanks for the updated code. The following is what I did, and the deletion is still not successful:

$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId'  ";
    $result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
        if($result && mysql_num_rows($result) == 1)
    {
        die('First select works');

        // remove the image and the thumbnail from the server
        unlink(GALLERY_IMG_DIR . $row['im_image']);
        unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);

        // and then remove the database entry
        $sql = "DELETE FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId' ";
        mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
            if(mysql_query($sql) && mysql_affected_rows() > 0)
    {
        die('Delete query works');
    }

If I understand you correctly, yes, the value of the GALLERY_IMG_DIR is an internal path and not located on an external server.

mexabet 49 Good Learner · Answer 21 · 2015-12-19T11:05:44+00:00

Those "die" statements didn't have any effect - no error message was printed on the screen.

mexabet 49 Good Learner · Answer 22 · 2015-12-19T13:54:59+00:00

Finally, I've been able to get the script to work. As follows is the working code:

<?php

if (isset($_GET['delete']) && isset($_GET['album']) && isset($_GET['imgId'])) {

    //$albumId = mysql_real_escape_string($_GET['album']);
    //$imgId = mysql_real_escape_string($_GET['imgId']);
    $imgId = (int)$_GET['imgId'];

    // Get the data to unlink:
    $qry = sprintf('SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = %d', $imgId);
    $result = mysql_query($qry) or die('Unable to retrieve image information ' . mysql_error());

    if ($row = mysql_fetch_assoc($result))
    {
        // Since im_id is a primary key, you can only ever have zero or one records with the query above
        unlink(GALLERY_IMG_DIR . $row['im_image']);
        unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);
        $delQry = sprintf('DELETE FROM tbl_image WHERE im_id = %d', $imgId); // no need for albumid as an img_id is unique
        $delResult = mysql_query($delQry) or die('Unable to delete record: ' . mysql_error());
    }   
}

$imagePerPage = 10;

$album = isset($_GET['album']) ? $_GET['album'] : '';
$pageNumber  = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;

$offset = ($pageNumber - 1) * $imagePerPage;
$serial = $offset + 1;

// get album list
$sql = "SELECT al_id, al_name
        FROM tbl_album
        ORDER BY al_name";
$result = mysql_query($sql) or die('Error, get product category list failed : ' . mysql_error());                    

$albumList = '';
while ($row = mysql_fetch_assoc($result)) {
    $albumList .= '<option value="' . $row['al_id'] . '"' ;

    if ($row['al_id'] == $album) {
        $albumList .= ' selected';
    }

    $albumList .= '>' . $row['al_name'] . '</option>';    
}
?>

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 23 · 2015-12-19T14:14:10+00:00

cereal 1,524 Nearly a Senior Poster

8 Years Ago

Good for you, did you understood what caused the issue?

Issue deleting data via PHP/MySQL

Recommended Answers Collapse Answers

All 28 Replies

Recommended Answers