PDA

View Full Version : Inserting rows in specified order???

josh013
02-28-02, 16:04
Ok, my first post here guys so please don't make fun of me too much:)

So, I got this silly idea to be able to insert entries/rows into specific locations. For example:

Let's say I have 3 columns

$query = "CREATE TABLE lessons (
EntryID INTEGER AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(30), Text TEXT)";

(This is how the table is setup now, but I understand I may be totally wrong in this setup...)

These entries are displayed one per page in my application w/next and prev buttons. My question regards wanting to insert a new row between two existing.

My idea is to create an "orderID" column to reference the rows display order (1,2,3,4, etc.) and write my "add" script to insert the newly created row between two existing, by referencing the "previous/above entry" the script would then add +1 that number assigning the position of the newly created row to follow the reference number, and add +! to all following numbered rows to ensure the correct order.

So I'm probabally going about this half blind and back asswards, like you can't tell from the post I'm quite new to mySQL and PHP, any help Would be greatly appreciated. Thanks.
JOSH
Paul
02-28-02, 16:39
Hi Josh

There is no need to insert the records into the database in any particular order. What's important, is that you are able to retrieve them in the correct order. This is done using the ORDER BY clause in your query.
josh013
02-28-02, 16:44
Hi Paul,

Ok, I can see that putting them in order within the database is totally un-necesary, but then how do I go about refrencing their order, don't I need something like my idea (the counting portion) to allow the display order to be updated?
Paul
02-28-02, 17:00
Not sure I fully understand, but a simple solution is;

Create the order id column and populate it with the relevent data.

The starting page is determined by the first query using WHERE orderID=1. Then when you click the next button, you issue another query by passing the current orderID to the script which will call WHERE orderid=$current_orderid+1.
achorozy
02-28-02, 17:09
josh013, I understand what you are saying. At our site we have a lot of applications that use dropdown boxes, list options. Generally you would think that the lists should be in alphabetic order (ORDER BY Description), but no the endusers wish to order the list by most popular or whatever. So we have a table like this

Code integer,
Description varchar(200),
DisplayOrder integer

Where we return the values ORDER BY DisplayOrder.

If you need to insert a row between 2 rows then you would update DisplayOrder = DisplayOrder + 1 where DisplayOrder >= NewDisplayOrder


select Code,Description,DisplayOrder
from table
order by DisplayOrder

Code Description DisplayOrder
----------- ----------- ------------
1 XXXX 1
4 DDDD 2
5 LLLL 3
2 ZZZZ 4
3 MMMM 5

update table
set DisplayOrder = DisplayOrder + 1
where DisplayOrder >= 4

insert table (Code,Description,DisplayOrder) values (6,'0000',4)

Code Description DisplayOrder
----------- ----------- ------------
1 XXXX 1
4 DDDD 2
5 LLLL 3
6 0000 4 <-- New Record
2 ZZZZ 5
3 MMMM 6
josh013
02-28-02, 17:39
WOOOO-HOOOOO!!!!

Thanks guys!

I can't belive this. I've been searching for info on how to do this for a week. My first post here and litterally within hours I've got an answer.

dBforums ROCK!

Thanks again.

JOSH