it--solutions
Posts: 24
|
| Posted: 09/06/2009, 11:16 PM |
|
a simple solution for finding the next available auto increment ID from your database table
Usage: Invoices Number, Booking ID, Receipt Number, etc....
create your_text_box
in the events tab select 'before show'
Add Action -> DLookup
Expression: "max(your_id_field) +1"
Domain: your_table_name
Criteria:
Connection: your_connection
Convert result to: Integer
Type of target: Control
Target: your_text_box
Done!
|
 |
 |
jjrjr1
Posts: 942
|
| Posted: 09/07/2009, 2:04 AM |
|
Hi
I hate to let you know that this method for getting the next sequence number will fail in a few cases. One most notably will be when records are deleted.
Exxmple would be:
lets say you have a table with 4 records like so.
ID DATA
1 Data1
2 Data2
3 Data3
4 Data4
Now let's say record 4 was deleted
The method for finding the next ID value above will return 4 however MySql will use 5 as the next ID value
I would consider using a method something like this
$query = mysql_query(SHOW TABLE STATUS LIKE tablename);
$row = mysql_fetch_array($query);
$next_id = $row[‘Auto_increment’] ;
Note: Using this value can be risky as duplicate or failed inserts could occur.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 09/07/2009, 2:17 AM |
|
BTW.
Let me know why you would want to know the next auto increment value.
There might be a better way to accomplish what you need to do that would not ptentially cause errors
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
steveadams617
Posts: 7
|
| Posted: 09/09/2009, 7:05 AM |
|
The other problem with wanting to know the next available unique ID is that by the time it is used in code another user of the system could have already added a record and used that value.
In older databases, programmers used to have a table of values to be used for the next ID. The code would pull the next available ID and then delete it. Perhaps that's what you need?
As the last writer wrote, I can't imagine why you would need to know this. It's much easier to grab the new value from the database after it's created.
|
|
|
|
damian
Posts: 838
|
| Posted: 09/09/2009, 9:25 AM |
|
using a mysql database i would reccomend that you use the mysql function: last_insert_id() or php function: mysql_insert_id() in an after insert event...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 09/09/2009, 10:08 AM |
|
Hmmm
That would give you the last auto incremented value for the ID field not the next ID. Also, It's value is only valid if you have not closed the MySQl session. After EXECUTE Insert is the place to get the last inserted ID. In CCS if you try to get the last inserted ID anywhere else, it will not work so pretty good since CCS closes the DB Connection after that event.
In any event adding 1 to the last inserted ID would stlll have all the issues the original solution proposed since it is a multi user system.
This is why I was wondering what the requirement was here since getting the last inserted ID could solve his problem depending on what he is trying to do.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 09/09/2009, 10:15 AM |
|
Wonder why we have not heard from IT-Solutions?
We are all trying to help..
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
it--solutions
Posts: 24
|
| Posted: 09/09/2009, 12:26 PM |
|
Hi,
Sorry about the lack of contact.
I do appreciate the fantastic help you are all offering,
...
I have revised the purpose of requiring the nextID from the database and decided to utilise the php date("Ymd-His") function to generate a unique booking_id as there is a possibility of 3+ users simultaneously using this booking system.
Unless there is a better way of generating a unique ID that anyone knows of?
|
|
|
|
damian
Posts: 838
|
| Posted: 09/09/2009, 6:33 PM |
|
what is wrong with using the autoincrement id? you just cant access the number until you have inserted... it will always be unique if you use auto-increment - 1 user, 3 users or 3000 users.
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
