Jump to content


Photo

Append to END of table


  • Please log in to reply
3 replies to this topic

#1 doodmon

doodmon
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 September 2006 - 05:36 PM

So long as I create new records sequentially it will put them in order, but when i delete a record and then try to add a record it will place the new record in the old record's place. I want it to appear at the end of the table, not in the middle. Here is the code to create a new record and remove a record, can I do a sort by the number column after a new one is inserted or other suggestions??:


case "create":
//pretty basic, just counts the number of rows and puts record with number 1 greater than that in.
//problem is it doesn't always put it at the end of the table.
$vtip = isset($_POST['vtip']) ? trim($_POST['vtip']) : "";
$vtip = str_replace("'", "’", $vtip);
$db=mysql_connect ("localhost", "xxx", "xxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("tips");
$query = mysql_query("SELECT * FROM tips.new");
$x = mysql_num_rows($query);
$x++;
mysql_query("INSERT INTO new (number, tip) VALUES ('$x','$vtip')");
echo "Entry Successful";
mysql_close($db);
break;

case "delete":
//$num is the record number being deleted.
//After it is removed while loop renumbers all the records that fell after the record so there is no gap.
$num = isset($_REQUEST['num']) ? trim($_REQUEST['num']) : "";
$db=mysql_connect ("localhost", "xxx", "xxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("tips");
mysql_query("DELETE FROM new WHERE number='$num'");
$x=0;
$query = mysql_query("SELECT * FROM new");
$numrows = mysql_num_rows($query);
while ($row = mysql_fetch_array($query)) {
$x++;
mysql_query("UPDATE new SET number='$x' WHERE number='$row[0]'");
}
echo "Deletion Successful";
mysql_close($db);
break;

#2 fenway

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

Posted 07 September 2006 - 05:47 PM

Not again... don't worry yourself with how/where the DB puts the record.  If you want them sorted in a particular way, issue an ORDER BY clause, and add the appropriate column to your table (e.g. a created/modified date, etc.)  Don't expect, assume or in any way depend on the PK field for anything at all.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 doodmon

doodmon
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 September 2006 - 06:14 PM

Thanks dood... glad you could help without sounding snotty

#4 fenway

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

Posted 07 September 2006 - 06:33 PM

No problem... you'd be surprised how much trouble you'd run into if you start tinkering with primary key values.
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