auto_increment howto?

Anon

i crate a table and have autoincrement rownumber there.

how i can know its next number?

[deleted]

You can't.

Use mysql_insert_id() to find the number that was insert AFTER you insert a new row.

do NOT EVER try to find what the next number will be, because the database will decide which number it inserts, not you.

eikyuu

um, don't hit me, I'm new to this myself, but I used to do it by getting the highest id (select (max) ...) and $newId = $currentID+1 ... is that wrong?

[deleted]

Yes that is wrong. :-)

And why is it wrong?
Because a database is a multi-user environment. That means that theoretically, hunderds of people/scripts can be accessing your database at the same time.
When you select max(column) and then insert with max_value+1, there is a delay between the select and the insert.
This may be only a fraction of a second, but that is more than enough time for someone else to insert a few records.
That means that the value you got from your select statement is already used by the time you do the insert.

If you first insert and then check which value was inserted by the database itself, you can never get the wrong value, no matter how many people are inserting rows at the same time.

Anon

my database isnt multyuser. i mean i use it alone. i use site admins tables and its me. but anja's idea wrong. why? because if i'll insert a new row it will be 12. (for example). after deleting it, rowid's next number will be 13 in database but max number will be 11. so its not a solution.

my problem is:
im uploading pictures to my site. and their names will be autoincrement numbers. im going to upload thousands pics so i dont plan give them names by letters. in next upload i must get next file name :-( and so on. have u got any solution or idea?

[deleted]

Anja's idea is wrong, but not because it will re-use id's old deleted rows.

The solution is to use auto_increment and get the new ID using mysql_insert_id() after you inserted the row

Anon

but at first i am uploading a file. my insertion is after uploading it. so i must get next number before uploading. i can use mysql_insert_id()+1 i think...

i tried. mysql dont reusing the numbers. for example if u delete field where rowid=2, next insertion will be in second field but rowid will be the last number, maybe 45.

[deleted]

but at first i am uploading a file. my insertion is after uploading it. so i must get
next number before uploading.

Why? When you upload a file it is store in under a temprary filename.
You have to move/copy it to where you want to store it.
So the procedure is:
upload file,
insert data into database,
fetch mysql_insert_id(),
move/copy the file from the temporary location to the final location with the name you got from mysql_insert_id()

mysql dont reusing the numbers. for example if u delete field where rowid=2, next insertion will be in second field

Wrong, mysql will put the new record wherever it fits, not wherever you just deleted a row.

but rowid will be the last number, maybe 45.

And so it should be.
MySQL has NO data-integrity checks, so it is not smart to re-use numbers.

Anon

in php i'm getting error for moving or coping my files. "you have no permission". and i dont want to open write permissions. its unsecure. so im uploading my files in perl. and cant connect to database for my file names :-(

[deleted]

Ehm. you get 'no permission' when you want to copy or move the file?

Well then just give that permission.
There is no security problem if you give the permission only the webserver.

Anon

what do you mean to give permisson only to web server?

cant everyone write there?

i know its funny but i have no permission to use chmod command :-)

my site is in f2s.com. im getting "unnown command" error when using chmod command?
im using them after connecting ftp. am i doing something wrong ?

and it gives rwxr-xr-x permissions to my all files. must my php files have read permissions open?

im asking u so much ! thanks for your help.. im newbie in php.. i designed only one databse. its something like telephone book :-) its www.elsad.f2s.com :-)