Jump to content


Photo

Simple Functions holding me back... Please Help!


  • Please log in to reply
1 reply to this topic

#1 released

released
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 September 2006 - 04:33 PM

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:

// Select
$id= $_POST['id']; //Retrieved from Flash
$limit= $_POST['limit']; //Retrieved from Flash
$query= "SELECT * FROM `news` LIMIT ".$id.",".$limit.";
$data= mysql_query($query);

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

//Insert
$date= $_POST['date'];
$entry= $_POST['entry'];
$query= "INSERT INTO `news` (`id`, `date`, `entry`) VALUES ('', '$date', '$entry');
mysql_query($query);

//Is there a way that I can automatically add an entry to the end of the table with the auto-incremented value?

//Delete
$id= $_POST['id'];
$query= "DELETE FROM `news` WHERE `id`= ".$id;
mysql_query($query);

//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,
Jesse

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

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