Page 1 of 1

Generate number depending on last insert!??

Posted: Fri Nov 23, 2007 6:36 am
by jmansa
I'm trying to generate a new number/next number depending on the last number inserted into my db???

In my DB I have a column where the numbers look like this:

00001.00001
00001.00002
00001.00003
00002
00003.00001
00004
etc. etc.


This is done for pages to find its place in a hierarchy.

My question is as follows:
Is it possible to make a script that looks into the dbtable and finds out what the last number in ex. 00001.xxxxx, and then generates the next comming number? And if yes... How.

Posted: Fri Nov 23, 2007 9:37 am
by superdezign
You could use MAX() if it was only numbers (I don't believe MAX() works with strings, although it might), but instead, you could order the selections starting with the latest, and then select the first of that set (which is the latest entry).

Code: Select all

select from `table` order by `column` desc limit 1;

Posted: Fri Nov 23, 2007 9:49 am
by jmansa
I dont think that would work, caurse im only looking for making this work if the prevous record has the 00001.xxxxx and not 00002, 00003 and so on!

Posted: Fri Nov 23, 2007 10:15 am
by superdezign
Sounds like you're in need of a better database design.

Also, maybe MySQL's SUBSTR() could be of use.

Code: Select all

SELECT FROM `table` WHERE SUBSTR(`column`, 0, 6) = '00001.' ORDER BY `column` DESC LIMIT 1;

Posted: Fri Nov 23, 2007 10:36 am
by Kieran Huggins
That data needs more normalization. "category" might work better as a separate column.

Search for "has many" and "has and belongs to many" for a quick overview of record relationships.