Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

How to find the highest number in a mysql table

by Anonymous Monk
on May 10, 2008 at 00:48 UTC ( [id://685804]=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm starting a very basic image gallery as a personal project for all my photos I've taken. I have the auto id increment set up on my mysql database so each upload has a unique identifier.

My question is, what's the easiest (shortest) way to determine the highest number already in the database + 1 so I can number the next picture uploaded?

I know I could hack this out in a Perlish way but I'm hoping to do things, well, a little more natural without looping through the entire structure.

Replies are listed 'Best First'.
Re: How to find the highest number in a mysql table
by igelkott (Priest) on May 10, 2008 at 01:40 UTC

    Might not understand the problem but an auto_increment typically means that you don't need to know the ID for the next insert. You can skip that column (if you have a list of the others) or enter null or even 0.

    If you need to know the ID after an insert, use my $last_id = $dbh->{'mysql_insertid'};. This can useful to insert associated data into another table.

      I can think of many reasons why knowing the ID in a case like this would be required. Let's say your images for this gallery are numerically numbered, 00001, 00002, 00003... And they're numbered after the id column.

      You have to read from the database BEFORE inserting another row to see what the next image will be labled.

        That's typically avoided because of the race condition.

        You could insert the row with a "not yet published" flag, and only create the file after the insert. Or have a separate table with just the auto-increment field and allocate ids whenever you want to use for the filenames and insertion into the main table.

        It is generally a bad idea to have your key field(s) being anything else than an abstract number or code. All it has to be is to be unique. If it refers (even indirectly) to some real world properties, sooner or later you are going to run into trouble.

        Believe me, I have been bitten by it several times.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: How to find the highest number in a mysql table
by sulfericacid (Deacon) on May 10, 2008 at 00:53 UTC
    my $data = qq(SELECT MAX(id) FROM table); # +1
    or
    my $data = qq(SELECT id FROM table ORDER BY id DESC); # +1


    "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

    sulfericacid

      There are a couple of problems here.

      The basic problem is that this only tells you what the last ID in the table was (+1), not what the next ID will be. If I insert a record and then delete it, that ID will not be reused, but your solution assumes it will. The next ID will actually be the highest +2. I could do this with any number of records.

      The other problem is in your second solution:

      my $data = qq(SELECT id FROM table ORDER BY id DESC); # +1

      This selects every row in the table. You really want to select only one:

      my $data = qq(SELECT id FROM table ORDER BY id DESC LIMIT 1); # +1

      ...but this still suffers from the first problem.

Re: How to find the highest number in a mysql table
by Anonymous Monk on May 10, 2008 at 15:01 UTC
    my $dbh = DBI->connect(...); my $next_pic = $dbh->selectrow_hashref("SELECT MAX(image_id)+1 AS nextid from images")->{'nextid'); Supposing that your table is called 'images' and the autoincrement column name is 'image_id'.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://685804]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (10)
As of 2024-03-28 12:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found