Stupid Database.... :(

[3raser]

(This is PhpMyAdmin)

 

Why is it when I set a field like ID, (you know, the one that automatically updates itself if new values are added, like when a new user registers, they get their own id) and you delete one, the next ID always starts off as 1 above the one you deleted.

 

Example:

 

id name

1 John

2 Justin

3 Hailey

4 Madison

 

Say if you delete Madison, and then insert "newvalue", it'll be:

 

id name

1 John

2 Justin

3 Hailey

5 newvalue

 

Why isn't it 4?

[Mchl]

Because that could create corrupted foreign key values in other tables. This is correct behaviour and you should not worry about it.

[3raser]

But IT DOES bother me, and I worry about it.

 

I have a image site, and this is what happens: (Example)

 

Image one

Image two

Image three

 

And a user clicks the "Next" button to change images. Say I delete image two and three, but I add three new images. It'll be:

 

Image one

Image four

Image five

Image six

 

When an image doesn't exist, they get the error message that one doesn't exist. So a user will have to keep clicking next just to get to image four, because two and three were deleted.

[teynon]

That's not a problem with your database, its a problem with your programming. ID is a unique ID used for managing images. If you want to sort entries, you should rely on other values.

[Mchl]

Instead of 'Next' fetching an image using WHERE ID = $currentID + 1 you should do something like WHERE ID > $currentID ORDER BY ID ASC LIMIT 1. This way you will always get next image from database (unless you reach last ID, which is yet another thing to deal with)

[teynon]

Ok, if you want pagination, you need to be doing it like such:

 

$itemsPerPage=5; // # of images per page.

$currentPage=1; // Set default page.

if ((isset($_GET['currentPage']))&&(ctype_digit($_GET['currentPage']))) {

  $currentPage=$_GET['currentPage'];

}

 

$start=($currentPage-1)*$itemsPerPage;

 

$sql="SELECT * FROM table ORDER BY ID ASC LIMIT {$start},{$itemsPerPage}";

[3raser]

Instead of 'Next' fetching an image using WHERE ID = $currentID + 1 you should do something like WHERE ID > $currentID ORDER BY ID ASC LIMIT 1. This way you will always get next image from database (unless you reach last ID, which is yet another thing to deal with)

 

So basically, how would that work within my mysql_query string? Instead of

 

$extract = mysql_query("SELECT * FROM photos WHERE photoid='$image'");

 

It would be

 

$extract = mysql_query("SELECT * FROM photos WHERE ID > '$image' ORDER BY ASC");

 

???

 

You sure that would work? And I could easily fix the last one.

[teynon]

Ok, i'm going to go ahead and say that I completely disagree with that method of achieving the results. However, if you use that method, you will need to put a LIMIT 1 on that or you will get all of the remaining images.

[Mchl]

$extract = mysql_query("SELECT * FROM photos WHERE photoid > '$image' ORDER BY ASC LIMIT 1");

 

And just like teynon says, it's not the best way to do it, but should work and give you something to think about.

[siva.katir]

You should assume two things:

 

1) You don't know the next image id #

2) The next image id # is not sequential.

 

If you order your db and and grab the line right after the current id you'll get the next image. Which is basically what all the advice before me has said. For an image site you can always build in a safety net too so you can't build a page with a broken image by simply checking if anything was returned in a query. If you do a count() and it's 0 you may as well not run an image output routine (I do this explicitly for search functions where return 0 is a good possibility).

 

Just making the query more flexible is by far the best place to start.

[3raser]

You should assume two things:

 

1) You don't know the next image id #

 

Yes I do. I have something already built that tells me the next ID.

[Mchl]

Chances are, that sooner or later you will not want to display your images in order in which they were added to database, but by some other ranking like 'user score' or 'top viewed'.

[siva.katir]

Yes I do. I have something already built that tells me the next ID.

 

I'm confused then, I thought that was the issue, that you were calling an image and it wouldn't be there because the next (incremental) number had been deleted?