Pagination to handle a deleted table entry...

[5kyy8lu3]

Hi.  I wrote a simple picture hosting web-application. (think photobucket)  I add a row to the table that has the filename and description of the picture that is uploaded.  The file is named the number of rows in that table +1.  (basically they're numbered for easy "use")

 

I have pagination system that works great right now.  The "gallery" shows the thumbnails 5 across and it prints them down the page.  When you click a picture it takes you to my pic.php page that shows a full size picture.  It has a previous and next button.  I use explode to take the number off of the filename of the current full sized picture and add 1 or subtract 1 and add the extension to it and use that to link to the next and previous picture(s) since they're all numbered.

 

Here's the problem: I want to now create a way to "delete" a picture.  I don't care if the picture is actually gone or not, I just need a way to "skip" that picture in both the gallery and full size picture page.  This poses a problem though, because I use the name of the picture's number as navigation to the next or previous picture.  How should I go about attacking this?  I've tried a few things, like writing if statements into my pages that would skip to the next page if the "status" column had the value "deleted" but I kept getting a "mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given" error and couldn't figure it out for the life of me.

 

Any ideas are welcome, just keep in mind I'm very new to php/mysql.

 

Here's a link to my site if you wanna see the system in action, login: test  password: test.  www.kloudz.com

 

thanks ahead of time

 

EDIT: by the way my webhost sucks and the mysql connection drops often so you might have to throw in a browser refresh or two, and my site is not 100% air tight when it comes to security, at least for the "upload" bit, the login forms are detagged and escaped though, i'm trying to get everything else done before i go through and thoroughly tidy up the security.  this will never be anything commercial, it's just for two things, A. practice coding php/mysql, and B. for friends/family to use

[[email protected]]

Ok, I'm gonna suggest that instead of using explode to get the next ID of the file, I suggest you use $_GET to get it first? Use that again, and to determine the next image, you should SQL query the table to see the next ID you need to get.

I'm gonna give an example...

<?php
// Let's say we use $_GET['id'] for the ID of the image. The URL would be [...]/img.php?id=7 for our example.
$id = $_GET['id'];
$result = mysql_query("SELECT * FROM `pictures` WHERE `picture_id` = '$id'");
// We'll use mysql_fetch_row to get the current image...
$image = mysql_fetch_row($result);
// Insert the code to fetch the code you want from the array
// And as for the next and previous image...
$next = mysql_query("SELECT `picture_id` FROM `pictures` WHERE `picture_id` > '$id' LIMIT 1"); // LIMIT 1 will tell it we only want to get 1 ID that is bigger than the current one (meaning, the next one will automatically be the next ID in the table)
$nextlink = mysql_fetch_row($next);
// Next link should link to pic.php?id=$nextlink[0]
// Same for previous, only with the opposite comparison
$prev = mysql_query("SELECT `picture_id` FROM `pictures` WHERE `picture_id` < '$id' LIMIT 1");
$prevlink = mysql_fetch_row($prev);
// Link to $prevlink[0]
?>

There's probably a better way to do this, but that's the best I can come up with right now =P sorry.

[5kyy8lu3]

Ok, I'm gonna suggest that instead of using explode to get the next ID of the file, I suggest you use $_GET to get it first? Use that again, and to determine the next image, you should SQL query the table to see the next ID you need to get.

I'm gonna give an example...

<?php
// Let's say we use $_GET['id'] for the ID of the image. The URL would be [...]/img.php?id=7 for our example.
$id = $_GET['id'];
$result = mysql_query("SELECT * FROM `pictures` WHERE `picture_id` = '$id'");
// We'll use mysql_fetch_row to get the current image...
$image = mysql_fetch_row($result);
// Insert the code to fetch the code you want from the array
// And as for the next and previous image...
$next = mysql_query("SELECT `picture_id` FROM `pictures` WHERE `picture_id` > '$id' LIMIT 1"); // LIMIT 1 will tell it we only want to get 1 ID that is bigger than the current one (meaning, the next one will automatically be the next ID in the table)
$nextlink = mysql_fetch_row($next);
// Next link should link to pic.php?id=$nextlink[0]
// Same for previous, only with the opposite comparison
$prev = mysql_query("SELECT `picture_id` FROM `pictures` WHERE `picture_id` < '$id' LIMIT 1");
$prevlink = mysql_fetch_row($prev);
// Link to $prevlink[0]
?>

There's probably a better way to do this, but that's the best I can come up with right now =P sorry.

that seems like it would work for this, but for it to work I'd have to just delete the entire row from the table, is that possible? and if so, mind sharing the code to do so? thanks 

[[email protected]]

Sure, it should be...

<?php mysql_query("DELETE FROM `pictures` WHERE `picture_id` = 'ID' LIMIT 1");

Don't forget to replace ID with the actual ID or variable that points to it.

[5kyy8lu3]

Sure, it should be...

<?php mysql_query("DELETE FROM `pictures` WHERE `picture_id` = 'ID' LIMIT 1");

Don't forget to replace ID with the actual ID or variable that points to it.

so i'd have to be using a mysqli account with delete permissions, that kinda scares me lol, i guess i'll have to resort to this until i figure out a better way of tackling this obstacle, thanks for your help

[[email protected]]

You could always just make sure no one you don't want to gets permission to do this, so you just need to make sure they're logged in and have the right permissions before letting the delete script actually run. I suggest cookies or session, check if they have the right power to delete, like if they have a certain permission (like moderator) or are the owners of the picture. Or do you mean you have no delete permissions at all?

[5kyy8lu3]

You could always just make sure no one you don't want to gets permission to do this, so you just need to make sure they're logged in and have the right permissions before letting the delete script actually run. I suggest cookies or session, check if they have the right power to delete, like if they have a certain permission (like moderator) or are the owners of the picture. Or do you mean you have no delete permissions at all?

 

naw I can do delete, and i do use login sessions with different levels of "permissions" depending on the login, i was just wanting to stay away from giving the login i use for mysqli on all my pages to have delete rights, but it's no biggie i suppose =)

[[email protected]]

Then give out an account with less permissions? =]