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.
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.
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?
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.
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