My sql id setup

[11Tami]

Hello, if I add this to my mysql server tables it adds an id to all my rows and numbers them.

 

ALTER TABLE `table_name` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

 

Is there a way to make it so if I delete some id's or rows, that the removed numbers will be refilled in?

 

For instance right now I could have 100 rows where many of them previous and in between could be deleted. Then if a new entry comes along it is listed as 101, and it automatically gets stuck right in the middle of maybe 34 and 40 or really anywhere. It also makes it impossible to know how many total rows there are. It would be better if 35 had been deleted and then when a new row id came along it were listed as 35 again, instead of 101.  Anyone know if this can be done? Thank you very much.

[Barand]

The correct answer is "You don't reuse deleted ids" and it should make no difference to your application if the ids are scattered in a random order in the table.

 

And not impossible

$res = mysql_query("SELECT COUNT(*) FROM tablename");
echo mysql_result ($res, 0);     // --> number of records in table

 

Consider if the banks reallocated account numbers.

 

John Does has bank account number 1234567, say and runs up a $1,000,000 overdraft and then, with no consideration for the bank, he dies.

 

The next day you decide to open an account at that bank and, as 1234567 is now unused, they give you that one. You just went bankrupt!

[11Tami]

Thank you very much but there is no other way? I am not dealing with any money issues.

 

I think I approached that wrong. The id's are already in the table so I probably need something to insert the next new row, into the first available id slot, instead.

 

I should be trying this instead. How can I alter this code so that it inserts a next row into the first missing id slot?

 

So if row id 33 is missing between rows 30 and 35, how to have only id row 33 added?  

 

Here is the code I have right now to just add a new row.

$sql="INSERT INTO addthis (comment)
VALUES ('" . $_POST['comment'] . "')";

Can I just add a "where" command or something instead?

 

Maybe something like

$id= "some code here saying enter into first empty id row found" 
//then the insert command to add the row
$sql="INSERT INTO addthis (comment)
VALUES ('" . $_POST['comment'] . "') WHERE `id` = $id";

I just need someone to help me to know, what to put in this variable. $id

 

[chronister]

Why do you need to "reuse" id's??

 

It really should not matter what the ID is since generally the id is only used for management issues like displaying data or modifying it.

 

What is the purpose of this?

 

I don't think you can specify INSERT ....... WHERE this='that';

 

An insert will simply insert and assign the next available id if it is auto-increment. If id 121 was the last one assigned, and there are only 25 records, then that makes 96 "missing id's". The next id will be 122.

 

I don't want to sound like a jerk, but I don't see the purpose of what your wanting to do.

[Barand]

It doesn't matter if it's money or not. If you have references to that table.column in any other table as a foreign key, don't do it.

 

IF you are just setting up a new table and

IF you have no references to it yet and

IF you know 33 is missing

 

INSERT INTO addthis (id, comment) VALUES (33, '{$_POST['comment']}')

[11Tami]

Thanks guys, here's what is causing the big problem. When stuff is automatically added to the database from a web page by someone: I have to go through all the entries and decide which rows I want to pull out onto pages and which ones should be deleted. Many of the rows are never kept. There is no problem assigning an id to someone else because it was never used. Then new rows are added by people and I have to go through the table again and delete any new ones we don't want. This is taking so much of my time and as the database grows will be virtually impossible. I don't get paid for helping my brother figure out his web site and looking through his tables and who knows someday if the site will make money where I ever will.

 

If the id's were replaced with new ones eventually there would be very few new ones mixed in with the old ones. I wouldn't have to look through the whole table each time, and I would know where to look for only new added rows to decide to keep or not. I hope you can understand why keeping it as is will cause a lot of stress and won't work in the long run. Unfortunately the form is sent by others not me so I can't insert just the row and new id I specify.

[Barand]

I still cannot see any reason for re-using ids in that scenario and you just seem to be creating work for yourself. You only want to do that if you are paid by the hour

 

just delete the ones you don't accept and don't worry about the gaps. You'll know where to look for new ones as they will have higher ids than the last one that was there when you last looked. Or if they as timestamped, they'll have a datetime later than your last visit.

 

Or

Delete the ones rejected.

Flag accepted ones as "accepted"

 

When you visit, list those not accepted, they'll be the new ones.

 

[11Tami]

Thank you, but I don't understand how you can think that because I still have to look through a whole huge table, and even several tables. If there is a gap between 1-10 and so on means I have to go back to the very beginning each time just to look for all the new fields. Even if I put accept etc. on some of the fields, I'll still have to go back to the beginning to look through all the fields for all of that including the new entries. These tables are getting huge and will only get bigger. My sql must have a way as popular of a database program that it is. I hope someone knows a way.

[chronister]

To find the new entries, simply sort the tables by ID descending, the entries with the highest ID are the newest.

 

I understand that stuff needs to be deleted from the table, but imagine you have 500 rows, and you are inserting data into previously removed id's. Then instead of being able to look at the highest ID's as they are the newest, you will have to look through all id's and decide if id 10 needs to be deleted again. It seems to me that this would create more work.

 

Maybe explain why you need to do this in a little more detail. What does the table hold. Is this from a contact form, or what kind of data is being stored? If we understand more about what the table holds, and why it needs to be cleaned out so frequently, maybe we can help you find a better solution. Include table structures and such.

 

I understand that some things need to be done, that may seem really strange to those who are not the administrator of the database, but this seems really strange and seems like it would cause more work than needs to be.

 

 

[11Tami]

Thats a great idea! Thanks very much for the sort tip at least it will solve this big problem. I looked for a sort before and couldn't find it which I thought was very odd that mysql wouldn't have sort when most other databases do. That should be all I need. I finally found the sort at mysql and it said I had to use an Order by clause. Which sounds like I'll need to view the sorted rows outside of phpmyadmin. I use phpmyadmin the most when I'm viewing and deleting the rows. Does anyone know of a way that sort can be done right at phpmyadmin instead of setting up a separate html page for the order by? Thanks.

[chronister]

In PhpMyAdmin, you simply click on the heading of the column you want to sort by. It will sort ascending order first, click again and it sorts descending order.

[11Tami]

Perfect! Thats better than expected and all I need. I bet a lot of people don't know about that. I think I sent you some links I had before. Thanks again everybody.