Inserting into DB

Arc

When i only add items to my DB they always go to the end of the DB. BUT if i delete an item in the middle som where and then add new items they are lways added right there in the middle instead of the end.

$Sql = "INSERT INTO tblNews (NewsBody, NewsTitle, NewsDate) VALUES ('$txtNewsBody','$txtNewsTitle','$Date')";

Here is the SQL i'm using...anyone know how i can make items always be tacked on to the end of the DB?

shawon22

y u wanna do that for?

Arc

WHy do i want items to always be added to the bottom of the DB?

Well becasue it's a news section and i can't have the news all scrambeled, it has to be in order.

If the user goes in and decides to delete the news from 2002-01-18 and then adds more news all the new news will be added in that spot instead of at the end of the DB.

Then when the news is loaded you have old news that is showing after what is supposed to be the newest news...

jowatkins

Do you have a primary key field. If you make this an autonumber, the system will generate the next available number and add your records to the end of the db. Pretty fundamental and very necessary.

the SQL for this is:

fieldname INT(4) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT

You should do this automatically when you create a table, but you can add it in with an ALTER TABLE statement.

Arc

Yes i have a primary key and it is auto number.... BUt it still adds the items out of order if i delete a row in the middle somewhere

_SuperString

Its clear you database design is not capable of storing your data they way you need it so, maybe you should add a new field called 'order' so that when you add a new item you also add the value max(order)+1 to it. This should solve you problem.

Arc

What are you talking about, it's clear my database design is not capable of storing my data they way i need it?

Its a very simple Table.

I Have 4 fields NewsID, NewsBody, NewsTitle, NewsDate

NewsID is the Primary Field and is set to auto_increment
NewsBody Stores the Body of the News
NewsTitle Staors the Title of the news
NewsDate Stores the date the news was posted.

OK, if i start with a fresh, empty Table and only add items it adds them as it should, at the end of the DB and the auotincrment field NewsID works fine.

If i have for example 10 items in the table and i delete the 5th one, THEN i add a new item, it adds it in the 5th slot where the item i just deleted USED to be. Then all new items added after that are added in sequentual order after that item.

Example

Before deleting item
NEwsID       NewsBody     NEwsTitle     NEwsDate
1               The Body        The Title        2002-01-10
2              The Body        The Title        2002-01-11
3               The Body        The Title        2002-01-12
4               The Body        The Title        2002-01-13
5               The Body        The Title        2002-01-14

After deleting 3rd item and adding 2 new items
NEwsID       NewsBody     NEwsTitle     NEwsDate
1               The Body        The Title        2002-01-10
2              The Body        The Title        2002-01-11
6               The Body        The Title        2002-01-15
7               The Body        The Title        2002-01-16
4               The Body        The Title        2002-01-13
5               The Body        The Title        2002-01-14

Notice the NewsID numbers are out of order and in the place of where the third item used to be.

_SuperString

All I am saying is, if you want to control the order of your data then you need to control the order of the data's identifier. One way is to create a new field (not-autoincrement) and mange the order yourself.
Also its strange to see the NewsID values getting reclamed, this doesnt normally happen (on my databases anyway), you should be able to control this.

check out here for more details.

Arc

I dont understand how creating a new field is going to let me decide where MySQL inserts the new item. Isnt there like a command to tell it to enter the item at the end of the table?

_SuperString

But the question is were is the end of the table ??
Are you sure new records are inserted with previously deleted records unique identifier (auto-increment value) ??
What size is your auto-increment field (small, medium, big int) ?

Arc

No..new files are Not inserted using previously used unique identifiers. If you would look at the example i gave you could see what exactly is happening.

They are inserted with a continuing incrimental value, they dont use old numbers,BUT the first item you insert after deleting an Item is inserted where the old item used to be and then every item after that is inserted after that item.

But the question is were is the end of the table ??

The end of the table is after the last row ofcourse.

Are you sure new records are inserted with previously deleted records unique identifier (auto-increment value) ??

I am 100% absolutely positive that they are NOT inserted using previously deleted records unique identifier (auto-increment value).

What size is your auto-increment field (small, medium, big int) ?

it is int(11)

Thanks for your help😃

_SuperString

The end of the table is after the last row ofcourse.

The last row eh.
A database is a storage system for data, the data can be stored in structured and relational ways. But in order to retrieve the data (usually by using SQL - Structured Query Language) you should state the Order in which you want the data returned - if you do not state the order then it will be returned the way it has been stored (unordered and this may vary from DB to D😎. So the reality is that the end of the last row could in theory be any row in the entire table (depending in how you order it). - if you say - SELECT NewsID FROM Table ORDER BY NewsID DESC - you will have a different last row than you would if you say - SELECT NewsID FROM Table ORDER BY NewsID ASC. hope you understand.....

anyway it apears all you have to do is add ORDER BY NewsID to your query and bobs your uncle.

Arc

I think you are confusing a Recordset with the actual table.

You can run a query and have it ORDERED BY any format you wish..this is called a RECORDSET. In a recordset the last row is as you described(Whatever you queried it by.) But the table itself is stored a certain way, and if you do a SELECT * FROM tblname and then display the results you will see exactly how it is stored.

But thanks for the ORDER BY idea, i will use that 🙂