Not sure this is the best solution,
Definitely not.
Removing a primary key and recreating it will break
the database referential integrity.
BTW reusing the gaps in the primary key, as the OP wants,
is not the smartest move either. I know a couple of methods
to achieve that, but I also know half a dozen reasons not to
do it. Therefore I'll keep the method for myself.
In the meantime, I will just suggest leaving the primary key alone.
| [reply] |
| [reply] |
the reason that I use the id field is that the client wants a slide show with next,prev and exit buttons. the only way that I could figure out how to do this is passing the id for an image in an hidden field and incrementing or decrementing the id field to point to a previous or next image to be displayed. if there is a break in the id number sequence the code barfs. if I could pass id numbers that don't exist I could make the delete image option that the client wants. | [reply] |
How do you deal with first and last image then?
I insist on my belief that a primary key should be doing
only one task, i.e. identifying the record uniquely. If you
need a display id, you have two choices:
- Adding a field to your table for display purposes,
but then you would have two further problems.
- filling the gaps when you delete a record.
- Always displaying the records in the same order.
No chance of diplaying with different criteria.
- Creating a display number when you select the records.
And you can do it either in (at least) two ways:
- with a MySQL variable.
SELECT @count := @count+1 as sequence, id, name, filename from mytable
- In your Perl script.
my $query = "SELECT id, name, filename from mytable";
my $sth=$dbh->prepare($query);
$sth->execute();
my @results;
my $count =0;
while (my $row = $sth->fetchrow_arrayref) {
push @results, [ $count++ , @$row ]
}
Now your @results have a counter that you can use for displaying purposes.
Modifying your query with a different WHERE or ORDER BY will change the display order.
| [reply]
[d/l]
[select] |
select ... from the_table
where id =
(select min(id) from the_table where id > $last_id)
An alternative, with MySQL, is to use the LIMIT keyword. IIRC this should work (note - I'm not a MySQL specialist, so check for correct syntax):
select ... from the_table where id > $last_id order by id LIMIT 1
As you are limiting the result set to a single row, and will get next row in the sequence even if there are gaps.
Michael | [reply]
[d/l]
[select] |
| [reply] |