0

Hi my qouestion is how to get the first number that is not used in specific database row. The number must be betwen 1 and 9999 and must be compared with all numbers in that specific database row, so if data in my database row starts with 5, i wont to be able to get the first number that is not used ...in this case the number 1. then when I create data with number 1.. the next number I need to get is 2 and...I'm using that to create profiles, and that number is the profile number, and ewery new profile must have the first unused number in data base. How to do that. I don't know where to start. So if someone can put me on the right path for solution of this problem? Thanks.

the edit

But, I dont need the auto increment i need to user to be able choosing this number on his own, first, this first number must bee suggested to the user by placeing it in the text form. And if the user select the number that is alredy in the database my program whil let the user know that he is trying to select the number that is allredy exist. So if you understand me ...I know the basics of mysql. The problem comes when the user deletes one profil then the deleted number can't be used eny more. For that i need the functio first free unused number.

New edit

I'l try to clear up some details...Frst this is the program for human resources and the user creates the dosies of workers... when user is creating the new dosie hee needs to select the dosie number for this worker, now I need to sugest to user the first unused number for the new dosie... the dosie number is not the dosie 'id'. Dosie number must be selected manualy by user or he can let the first free number to given to the new dosie... I think this whill clear some things

Intex
  • 31
  • 1
  • 5

4 Answers4

4

You are probably talking about auto-Increment primary key of table rows. Just insert the data, without specifying this "number" and the database will automatically set it to the proper (next free) value.

Do not reuse primary keys (eg you have 1,2,3,4,5 but then delete 3 - if you reuse 3 you will not know at any future point that 3 was some other record that was actually deleted).

This, btw, is very basic database knowledge. Read some introduction tutorials on MySQL or any other SQL relational database.

ddinchev
  • 33,683
  • 28
  • 88
  • 133
  • Now I've read the question another couple of times, I think this is the answer :) – Pete Jun 21 '12 at 13:33
  • @Veseliq But I dont need the auto increment i need to user to be able choosing this number, first this first number must bee suggested to the user. I if the user select the number that is alredy in the database my program whil let the user know that he is trying to select the number that is allredy exist. So if you understand me ...I know the basics of mysql. – Intex Jun 21 '12 at 13:40
  • Note that it *is* possible to reuse PKs, but it involves having a special column indicating of the row has been deleted or not, and querying the table to find the first row with that column set. But this also involves locking the table to prevent race conditions on the row! ...I'm just saying :) – Yanick Rochon Jan 06 '13 at 03:21
1

You are trying to use bad the database.

May be you can look this: Finding the next available id in MySQL

Community
  • 1
  • 1
Estefano Salazar
  • 419
  • 4
  • 15
0

First create a table with values 1 to 9999. Then, run this query once:

delete from table where id IN (select id from profiles)

This way, you get IDs that are not in the profiles table. The first one can be shown to the user. On saving the record, make sure to delete that ID from this table.

If I understood you correctly, this is what you are looking for.

Sunish Menon
  • 152
  • 11
  • Look u are close al try to clear up some details...First this is the program for human resources and the user enters the dosies of workers with workers info... when user is creating the new dosie hee needs to select the dosie number for this worker, now I wont to sugest to user the first unused number for the new dosie... the dosie number is not the dosie 'id'. Dosie number must be selected manualy by user or he can let the first free number to given to the new dosie... I think this whill clear some things – Intex Jun 21 '12 at 13:57
  • OK. So what's the issue? Create a table dosie with just IDs from 1 to 9999 and do as I said. If all used rows are removed, then you have a table with unused dosies. (What's a dosie by the way?) :( – Sunish Menon Jun 21 '12 at 14:01
  • And how are you going to handle multiple users? If 2 people access the application simultaneously, both of them will get the same ID (Dosie number). Won't it create problems? – Sunish Menon Jun 21 '12 at 14:03
  • yes I understand u but can this be done without creating another table with numbers from 1 to 9999 ? For example can it be done by php alone? For multiple users I hawe the user acount and administrator acount only admin whil be able to create dosie... Dosie is colection of all kind of workers data ... – Intex Jun 21 '12 at 14:11
  • No, as far as I know. Actually, I had posted a similar question yesterday, but deleted it later as it was found to be an exact duplicate. Creating another table is the most easiest and fastest way. The other way is creating a MySQL do..while loop and check if each number exists in the table. This puts lots of load on the server. – Sunish Menon Jun 21 '12 at 14:13
  • Using PHP - store all existing dosie numbers in an array. Then create a for loop from 1 to 9999, and if the current iterator is not found in the array, return that number, exiting the loop. This is also, not an efficient way, as all numbers needs to be stored in PHP beforehand. – Sunish Menon Jun 21 '12 at 14:16
  • Thanks for the help man, I hope this whill help ...One more question can I yust create datatable with 1-9999 numbers and then compare with existing data from dosie table row and then somehow select the first free number with max() or min() value? I meen without deleting the numbers becose if I vuld have more diferent acounts users then this numbers whill be needed... :S Or am I'm wrong somewhere? – Intex Jun 21 '12 at 14:26
  • sou can I do something like that? Described up. – Intex Jun 21 '12 at 14:41
  • Without deleting - you can add a column "used" and mark it 0 or 1, as posted by Stu. – Sunish Menon Jun 21 '12 at 14:50
0

If you are limited to using values 1 through 9999 I would probably setup the process as follows:

  1. Add another table with two columns (id_tracker).
  2. Populate id_tracker with id's 1 through 9999 defaulting is_used to 0.
  3. Update id_tracker.is_used to 1 based on the contents of your table.
  4. Add a delete, insert triggers to your table to update the id_tracker as necesssary.
  5. And select empty ID's as follows SELECT id FROM id_tracker WHERE is_used = 0 ORDER BY id LIMIT 1

Here's some SQL to get you started:

create table id_tracker
(id int not null, is_used tinyint default 0, primary key (id));

delimiter |

CREATE TRIGGER your_table_delete_trigger BEFORE DELETE ON your_table
  FOR EACH ROW
  BEGIN
      UPDATE id_tracker SET is_used = 0 WHERE id = OLD.your_table_id;
  END;
|

CREATE TRIGGER your_table_insert_trigger AFTER INSERT ON your_table
  FOR EACH ROW
  BEGIN
      UPDATE id_tracker SET is_used = 1 WHERE id = NEW.your_table_id;
  END;
|

delimiter ;

** NOTE: the above is for MySQL

Stu
  • 340
  • 2
  • 8