[RESOLVED] MySQL Delete only deleting content, not row itself

GreyBoy

Ok so heres the problem. I am working on a content editor for a client who wants tha ability to add new page. But he also has to be able to delete page. The pages are refered by their PID column which is set to auto increment.

However, when I delete a row, for some reason, it deletes the content in the row, but not the row itself. So, I have an empy row left which gets assigned a PID when I drop the PID column and reconstruct it with the new auto_increment numbers.

What could be causing this? Or is there a way to renumber the auto_increments after a page has been deleted? My coding is a little rusty right now as I am out of practice.... had to have a reference manual by my site at all times.

Heres my code:

$sql 	= "DELETE FROM $db_table WHERE PID = $id LIMIT 1";
$result = @mysql_query($sql, $db_connect) or die(mysql_error());



$drop 	= "ALTER TABLE $db_table DROP COLUMN PID";
$result = @mysql_query($drop, $db_connect) or die(mysql_error());



$add	= "ALTER TABLE $db_table ADD PID INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
$result = @mysql_query($add, $db_connect) or die(mysql_error());

Thanks for the help and sorry if the solution is obvious.

Sam

GreyBoy

Also, is there a method for running multiple statments in one query?

MarkR

The "delete" statement only ever deletes rows. It will NEVER change the contents of an existing row, only delete them.

Therefore, your original assertion would seem to be wrong. How have you determined that that's what's happening?

Why is your web application doing an ALTER TABLE? DDL statements (such as ALTER TABLE) should never normally be used in a conventional application. Keep the database schema the same throughout normal operation instead.

Mark

GreyBoy

MarkR wrote:
The "delete" statement only ever deletes rows. It will NEVER change the contents of an existing row, only delete them.

Thats what I want it to do.. delete the entire row. But for some reason, it is deleting the content, but not the row.

Why is your web application doing an ALTER TABLE? DDL statements (such as ALTER TABLE) should never normally be used in a conventional application. Keep the database schema the same throughout normal operation instead.

Mark

The reason I am altering the table is to re number the PID column. The structure of the table is the same, I m just dropping the column and adding a new one with the same setup... this way the auto increment will restart.

For example, Lets say I have the following table:

   PID   |  Page Name  |    Content
+----------------------------------------------------------------+
     1     |  Home           |     Something
     2     |  About           |     About Us
     3     |  Links            |     New links
+----------------------------------------------------------------+

When I delete the about page (PID 2)... Im left with:

   PID   |  Page Name  |    Content
+----------------------------------------------------------------+
     1     |  Home           |     Something
   [COLOR=Red]  3 [/COLOR]    |  Links            |     New links
+----------------------------------------------------------------+

So, I figure I would drop the PID column and rebuild it so
it auto fills with auto_increment so I have:

   PID   |  Page Name  |    Content
+----------------------------------------------------------------+
     1     |  Home           |     Something
    [COLOR=Red] 2[/COLOR]     |  Links            |     New links
+----------------------------------------------------------------+

But instead, I get this:

+----------------------------------------------------------------+
     1     |  Home           |     Something
     2     [COLOR=Red]|                      |[/COLOR]     

     3     |  Links            |     New links
+----------------------------------------------------------------+

Maybe that will help explain it?

MarkR

You misunderstood my post.

The DELETE statement in MySQL IS NOT FAULTY. There is absolutely no way that it could possibly be doing what you describe.

What you're doing to renumber your PID columns is nonsensical and should never be done in a production system. Don't worry about non-sequential IDs, they don't matter in the least.

Also, your "LIMIT 1" on the delete is unnecessary. I am presuming that you have a unique index (i.e. primary key) on your PID column. This is another good reason not to drop it.

Dropping the primary key ruins the integrity of the table.

How are foreign keys going to continue to refer to the right row if you've dropped the PK???!

Mark

GreyBoy

Thanks for your post. I thought of a different way to go about this. Create a temp table, transfer everything into that table, then drop the old table.. then rename the temp table:

" Create Table tempTable(
                PID int not null auto_increment, 
		page_name varchar(50),
		PCSSF text null,
		PCSSIE text null,
		content_1 text null,
		content_2 text null,
		content_3 text null, 
		primary key(id) )";

insert into tempTable (page_name, PCSSF, PCSSIE, content_1, content_2, content_3) Select page_name, PCSSF, PCSSIE, content_1, content_2, content_3 from $db_table;

		drop table $db_table;

		rename table tempTable to $db_table

GreyBoy

Havent tried it yet, but I think it should work....

GreyBoy

Figured it out... I had a pop up window opening and when it would open, it was refreshing the script on the add page section... so it added a blank row...

Piranha

But why do you have to drop the row? The unisgned bigint can have values up to 18,446,744,073,709,551,615, and I doubt that you need more rows than that. As Mark says there is no reason at all to change the id, it will only mess the database integrity up. And to be honest it is a really stupid thing to do. If you want to show that you can't handle databases this is the best way I have heard of to do it.

Sorry for being kind of rude. It is not ment personal, it is ment to really get you to wake up and realise that what you try to do is wrong. You will get in trouble if you mess with the keys, simple as that. If you don't understand why then you should google around until you understand it.