in reply to How to find the highest number in a mysql table

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.

Replies are listed 'Best First'.
Re^2: How to find the highest number in a mysql table
by Anonymous Monk on May 10, 2008 at 01:43 UTC
    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.

[reply]
      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.

      --
      Online Fortune Cookie Search
[reply]
        ++. Interesting approach. Get the database set up first, determine what the last id was, then continue processing.

        Didn't think of going this route.



        "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
[reply]
        ysth writes:
        You could insert the row with a "not yet published" flag, and only create the file after the insert.
        Instead of keeping the image data in a file, consider storing it in the database in the same row that describes it. Keeping a file system and database in sync can tricky. You have to be prepared to handle files which don't have database entries and database entries which don't refer to files. That's why it can be simpler just to store your file data in your database (e.g. in a BLOB column.) Just something to think about...

        Also, to avoid the race condition that ysth refers to, try to perform the determination of the last number used and the updating of the new row in one SQL statement: 

          -- create the new image row with blank gallery_id:
  INSERT INTO images ... VALUES (...);
  -- atomically update the gallery_id field
  UPDATE images SET gallery_id = (SELECT 1+MAX(...) FROM images) WHERE
+ ...;

        [download]
        Another possibility is to do it all in one INSERT statement: 
          INSERT INTO images (gallery_id, title, description, fstop)
    SELECT 1+MAX(...), 'title', 'description', 'fstop' FROM images;

        [download]
        You'll run into a race condition if you have perl figure out the next id and then use that id in a subsequent INSERT or UPDATE statement.
[reply]
[d/l]
[select]
      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

[reply]

In Section Seekers of Perl Wisdom