Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post

    really DELETE or set deleted='1'?

    Hello,

    I need some advice from experienced users. Let's say I have such a DB structure where I keep my pages' information:

    TABLE pages:
    ID | html | active | deleted

    I give the admin to delete the pages from the DB. Till yet, I did not actually delete the pages from the DB using

    PHP Code:
    $result mysql_query("DELETE FROM pages WHERE ID='$pID' LIMIT 1") or die(mysql_error()); 
    but

    PHP Code:
    $result mysql_query("UPDATE pages SET deleted='1' WHERE ID='$pID' AND deleted='0'") or die(mysql_error()); 
    So, I actually did not delete the pagerows but make them look like they are deleted. Naturally, with every live mysql_query I need to include the deleted='0' to work with undeleted pages.

    I wonder, if you did the same, are there negative consequences of this? I am thinking of really deleting the pages but fear that the DB will recreate the ID's in the future when new pages are created and the old ID's are not there anymore. The ID field is set to auto_increment.

  • #2
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,108
    Thanks
    11
    Thanked 101 Times in 99 Posts
    depends
    I normally delete unless the table is linked to others, e.g. imagine a 'suppliers' table, and imagine that at some point that supplier goes broke or you just stop using them... IF e.g. your products database is linked to your suppliers database and you want to keep historical data you have little choice except keep the data, I normally have an 'active' field which will stop the record ever turning up in selects, yes it is an extra index but the alternative is keeping historical data in flatfiles or duplicate databases.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #3
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    That was a quick reply! My table is linked to others, but this is a table where I keep the permissions about which profiles can access this page. After deleting the page I can delete the rows of permissions that are related to this page. What kind of "historical" data can I keep for example? Page visits?

    I also fear that MySQL will recreate the ID's that are deleted. Let's say I have deleted page with ID=1 and sometime when I create a new page in the future, can it recreate a page with ID=1 again? That won't be good.

  • #4
    Regular Coder
    Join Date
    Jun 2007
    Location
    N. Ireland
    Posts
    351
    Thanks
    16
    Thanked 4 Times in 4 Posts
    If you are using auto increment and delete a record MYSQL will not assign a new record with a previously used ID. I think. You can even set what number the records will begin at.

    eg: I use the IDs for my ordering system and set the base record to be 1,000. All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!

    Hope this helps.

    D.

  • #5
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    I am using autoincrement and till yet, the number to start was naturally 1 as an autoincrement value was not set.

    eg: I use the IDs for my ordering system and set the base record to be 1,000. All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!
    Thanks for the tip

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Auto-increment won't re-use the same number because it keeps track of which number it used last, not which numbers are currently available. So don't worry about that.

    If you are waffling on the issue of delete vs. update the deleted flag, then you may prefer a compromise: Move the row (via delete and insert) into an archive table. That way your primary table stays clean, but you have the old data to refer to if it's needed.

    All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!
    I do the same thing, but I do it to keep the ID length consistant, which means I start with 100000 (or even 1000000 if I think there will be more than a million records some day). It's a silly thing to do, but I prefer it, and it's my system, so I can do what I want!

  • #7
    Regular Coder GSimpson's Avatar
    Join Date
    Aug 2006
    Location
    New Zealand
    Posts
    268
    Thanks
    9
    Thanked 0 Times in 0 Posts
    just acting on the first question, you could output the code to a new html file in the a folder named "backups" then delete it from database. Just grab it from backups when you have too many backups. Keeps mysql space and webspace down. If that's what you asked, I didn't understand the question completely.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •