auto increment MySQL

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Da_Elf
Posts: 311
Joined: Sun Apr 27, 2014 2:45 am

auto increment MySQL

Post by Da_Elf » Sun Nov 30, 2014 6:50 pm

i ant to find the next auto increment number
currently with livecode im using SELECT MAX(id) FROM database then adding 1 to it to get the next id that will be used.
however what if i have 6 items, the next to be used will be 7. but if i delete item 6 from the database using my method will give me 6 as the next available one however MySQL will set the next entry as 7

SparkOut
Posts: 2949
Joined: Sun Sep 23, 2007 4:58 pm

Re: auto increment MySQL

Post by SparkOut » Sun Nov 30, 2014 10:34 pm

Select max(id) after you do the insert? Or perhaps LAST_INSERT_ID()

Da_Elf
Posts: 311
Joined: Sun Apr 27, 2014 2:45 am

Re: auto increment MySQL

Post by Da_Elf » Mon Dec 01, 2014 7:55 pm

that works at that point in time. but what if i go do other stuff. Log off the come back then need to find it. I would have to insert something to get the AI number

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: auto increment MySQL

Post by bangkok » Mon Dec 01, 2014 8:29 pm

What about :

Code: Select all

SHOW TABLE STATUS WHERE Name = 'yourtable'
It will gives you infos on the table... with "auto increment" = the next value

Da_Elf
Posts: 311
Joined: Sun Apr 27, 2014 2:45 am

Re: auto increment MySQL

Post by Da_Elf » Tue Dec 02, 2014 3:34 am

thanks. This works

Code: Select all

   put "SHOW TABLE STATUS WHERE Name = 'users' " into xSQL
   put revDataFromQuery(comma, cr, gConnectionID, xSQL) into maxData
   set itemDelimiter to comma
   answer item 11 of maxData

Post Reply