hi all

I am learning php mysql now and I have a problem with numbering records.

I have a table with records numbered from 1 to 10 . tell know I dont have a problem and I've numbered them correctly .
The problem occurs when I delete one of the records using a function I wrote,I want to renumber them again.

what I mean that when I delete record number five the list of records numbers will apear like this 1-2-3-4-6-7-8-9-10
but I want them to apear like this 1-2-3-4-5-6-7-8-9

can anyone help me fixing that problem?

EADM

It would be a little more helpful if you explained what the numbering is used for. Many people new to databases try to number things in the database that can be done programatically.

However, let's assume that the reason for your numbering is valid. I really hope that you have a unique ID field aside from this sequencing (numbered) column. It should be an auto-incremented field.

Now, in your function that deletes a record you will need to add more to it. First, save the value of the sequence field (before you delete it obviously). Then pull all the records into a dataset (you only need the ID field and the sequence field). Lastly, loop through each record. If the sequence value of the record is greater than the sequence value of the record that was deleted, update that record (using the unique ID) to have a sequence value of the current value -1.

Michael J

and if I may add, you shouldn't "number" the records in an SQL database, it works differently than "lists" we have in mind.

It's best if you could do the numbering in the output, like in the PHP scripts. Leave the record with its unique id numbers.

---
Site of the Day

Actually, I must be high. All you need is a single update command:

UPDATE table_name
SET seq_column_name = seq_column_name - 1
WHERE seq_column_name > seq_value_of_deleted_record

Michael J

Laler, that was what I was alluding to - that's why I was asking what the purpose is for. But, sometimes a sequencing field needs to be used in the table. For instance when the records must be in a particular order, but there is no other unique data which the data can be properly sorted with.

A perfect example would be a top 10 list of your favorite movies. There would be no way to sort them via name, date, etc. to get them in the correct order without a sequence field.

Of course, I know that you know this Laler, I'm just stating it more for the benefit of the poster and others who might read this.

Michael J

That's a good example, I've never had a case, that forces me to sort the record directly in the database like that.

The closest problem I've had, can be solved by putting a column called "priority" or something, so I can fetch the records based on that column, and numbers them in the output. This column doesn't have to be filled with increasing numbers in a perfect order :)

---
Site of the Day

Of course, I know that you know this Laler, I'm just stating it more for the benefit of the poster and others who might read this.

The same goes to you, a discussion can open a lot of people's mind =D

---
Site of the Day

Mechael J & Laler thanks for replay

------------
It should be an auto-incremented field.
------------
yes I used auto-incremented

---------
Now, in your function that deletes a record you will need to add more to it. First, save the value of the sequence field (before you delete it obviously). Then pull all the records into a dataset (you only need the ID field and the sequence field). Lastly, loop through each record. If the sequence value of the record is greater than the sequence value of the record that was deleted, update that record (using the unique ID) to have a sequence value of the current value -1.
-------------
nice Idea ,but unfortunatlly I submitted my work from along time and forgot about my post her

------------
It's best if you could do the numbering in the output, like in the PHP scripts. Leave the record with its unique id numbers.

-------------
That's excactly what I did , but I felt that I am cheating since my proffesor wanted them to be numbered in the database

-------------
UPDATE table_name
SET seq_column_name = seq_column_name - 1
WHERE seq_column_name > seq_value_of_deleted_record

------------
that's great !!
I've just know that we can put conditions after WHERE like < > I thought we can use = only

EADM