I am in the final stages of a project I have been working on for about 6 months. It is just a basic dynamic website where the user can login and edit the website. I am using Flash, PHP and MySQL. The problem that I have faced is that when I add or delete and entry, the auto_incremented `id` field may cause the entry to be out of order. When I add an entry, it will add it in the middle or beginning of the table. The result is a non-ascending set of values for `id`, when I need the entries to be in order. My scripts are as follows:
$id= $_POST['id']; //Retrieved from Flash
$limit= $_POST['limit']; //Retrieved from Flash
$query= "SELECT * FROM `news` LIMIT ".$id.",".$limit.";
//How can I incorpatate an efficient 'SORT BY' function to automatically sort the `id` field every time I retrieve an entry? Or is there a way that I can set the `id` field to automatically do this, such as making it a Primary, Index or Unique?
$query= "INSERT INTO `news` (`id`, `date`, `entry`) VALUES ('', '$date', '$entry');
//Is there a way that I can automatically add an entry to the end of the table with the auto-incremented value?
$query= "DELETE FROM `news` WHERE `id`= ".$id;
//When I delete an entry, is there a way that I can have all the entries that come after the row being deleted automatically reset their numbers to adjust to the missing id number? So if I delete entry '7', entry '8' will become entry 7, and entry '9' will become 8 and so on and so forth?
Thanks Very Much for your time,
Simple Functions holding me back... Please Help!
1 reply to this topic
Posted 04 September 2006 - 07:30 PM
This is NOT the way to get the entries "in order" -- sort by another column, or add a sortorder column. Don't mess around with the PK of a table, ever.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users