473,287 Members | 1,629 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Updating auto_increment fields ?

I have a table with quiz questions. Each question has a unique ID, based on
an auto_increment field.
Now I've discovered that with deleting rows from the table the deleted ID's
are not reissued. Makes sense, just thought of that a bit late.

How can I simply re-index or update or how one would call it, the columns
holding the ID numbers ? Is there a ready-made mysql command or do I have to
load all rows in PHP and modify where necessary ?

TIA
Pjotr
Jul 17 '05 #1
4 1942
*** Pjotr Wedersteers wrote/escribió (Mon, 30 Aug 2004 10:47:43 +0200):
I have a table with quiz questions. Each question has a unique ID, based on
an auto_increment field.
Now I've discovered that with deleting rows from the table the deleted ID's
are not reissued. Makes sense, just thought of that a bit late.

How can I simply re-index or update or how one would call it, the columns
holding the ID numbers ? Is there a ready-made mysql command or do I have to
load all rows in PHP and modify where necessary ?


I wouldn't play with that. If auto-generated IDs are not valid for you,
just quite using them. You can make your column UNIQUE to prevent
duplicates and assign your own IDs from the very beginning.
--
-- Álvaro G. Vicario - Burgos, Spain
-- Questions sent to my mailbox will be billed ;-)
--
Jul 17 '05 #2
There is no automatic way to rebuild a table and have the missing
auto-increment values used up. This could cause a serious problem if one of
these primary keys was a foreign key on another table.

Why do you need a deleted row to be re-used? What problem does it cause to
have a missing id number?

--
Tony Marston

http://www.tonymarston.net
"Pjotr Wedersteers" <x3****@westerterp.com> wrote in message
news:41***********************@news.xs4all.nl...
I have a table with quiz questions. Each question has a unique ID, based on
an auto_increment field.
Now I've discovered that with deleting rows from the table the deleted
ID's
are not reissued. Makes sense, just thought of that a bit late.

How can I simply re-index or update or how one would call it, the columns
holding the ID numbers ? Is there a ready-made mysql command or do I have
to
load all rows in PHP and modify where necessary ?

TIA
Pjotr

Jul 17 '05 #3
Tony Marston wrote:
There is no automatic way to rebuild a table and have the missing
auto-increment values used up. This could cause a serious problem if
one of these primary keys was a foreign key on another table.

Why do you need a deleted row to be re-used? What problem does it
cause to have a missing id number?
"Pjotr Wedersteers" <x3****@westerterp.com> wrote in message
news:41***********************@news.xs4all.nl...
I have a table with quiz questions. Each question has a unique ID,
based on an auto_increment field.
Now I've discovered that with deleting rows from the table the
deleted ID's
are not reissued. Makes sense, just thought of that a bit late.

How can I simply re-index or update or how one would call it, the
columns holding the ID numbers ? Is there a ready-made mysql command
or do I have to
load all rows in PHP and modify where necessary ?

TIA
Pjotr


I intended on using a random ID number 1..num_rows and retrieving the
question with that ID from the table. I will now have to do an additional
check to see whether mysql_reult for that random number holds a row of data
indeed. No biggy in this case. I think in retrospect in this case
auto_increment just wasn't the field to use. I had better used a UNIQUE id
field I numbered myself.

Thanks both for the advise. I somehow already figured tampering with
auto-increment data wasn't a wise plan.
Pjotr
Jul 17 '05 #4
In article <41*********************@news.xs4all.nl>, Pjotr Wedersteers wrote:
I intended on using a random ID number 1..num_rows and retrieving the
question with that ID from the table. I will now have to do an additional
check to see whether mysql_reult for that random number holds a row of data
indeed. No biggy in this case. I think in retrospect in this case
auto_increment just wasn't the field to use. I had better used a UNIQUE id
field I numbered myself.


If you are using MySQL:

SELECT * FROM table ORDER BY RAND();

--
Tim Van Wassenhove <http://home.mysth.be/~timvw>
Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Jason | last post by:
Let's say I have an html form with 20 or 30 fields in it. The form submits the fields via POST to a php page which updates a table in a database with the $_POST vars. Which makes more sense? ...
4
by: Geoff | last post by:
Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has...
33
by: bill | last post by:
In an application I am writing the user can define a series of steps to be followed. I save them in a sql database using the field "order" (a smallint) as the primary key. (there are in the range...
3
by: Siong.Ong | last post by:
Dear all, my PHP aims to update a MySQL database by selecting record one by one and modify then save. Here are my PHP, but I found that it doesnt work as it supposed to be, for example, when...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.