SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast Dreeass's Avatar
    Join Date
    Sep 2012
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Removing column from database

    I'm making a simple blog for a website so I can easily edit the homepage. Since I want it to be easy for me and maybe future administrators I ofcourse want the option to delete a blog. But my function relies on the id's. It gets the latest 5 ids. I currently use the PHP date() function to store a date in a varchar. But is it good if I change varchar to date and let it rely on the date instead of the id?

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Dreeass View Post
    I'm making a simple blog for a website so I can easily edit the homepage. Since I want it to be easy for me and maybe future administrators I ofcourse want the option to delete a blog. But my function relies on the id's. It gets the latest 5 ids. I currently use the PHP date() function to store a date in a varchar. But is it good if I change varchar to date and let it rely on the date instead of the id?
    Each post should have a unique identifier assigned to it. This should almost always be a auto increment number. Its almost never good practice to use a date field as part of your primary key.

  3. #3
    SitePoint Enthusiast Dreeass's Avatar
    Join Date
    Sep 2012
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Each post should have a unique identifier assigned to it. This should almost always be a auto increment number. Its almost never good practice to use a date field as part of your primary key.
    Ofcourse I'm using an id as a primary key, but my PHP function to get the latest posts relies on the latest ids using MySQL's between and stuff. So if I delete 1 out of there, the there will be 1 less shown as specified. But I think I'll rely it on the current datestamp function.

  4. #4
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    A couple of points:

    Your question says that you want to delete rows from the table. That's correct. You don't want to delete columns, as your thread title indicates.

    Second, if I've understood this right, you're worried that deleting rows will cause gaps in the sequence of IDs, and this might muck up the function that retrieves the five most recent rows. If that's right, you need to retrieve the rows as follows:

    SELECT TOP 5 .... FROM ... ORDER BY ID DESC

    This will be much more efficient than using a date stamp.

    Gaps in the sequence of auto-incrementing IDs are inevitable, and nothing to worry about.

    If I've misunderstood the problem (which is quite likely), my apologies.

    Mike

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,234
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mikl View Post
    SELECT TOP 5 .... FROM ... ORDER BY ID DESC
    Since I can't recall if TOP 5 is valid for MySQL, you can also use LIMIT

    Code:
    SELECT columns FROM table ORDER BY ID DESC LIMIT 5

  6. #6
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Since I can't recall if TOP 5 is valid for MySQL, you can also use LIMIT
    Ah, now you mention it, I think you're right about TOP N not being valid in MySQL.

    Mike

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    SELECT TOP 5 .... FROM ... ORDER BY ID DESC

    This will be much more efficient than using a date stamp.
    no, not "much more" efficient -- i'll wager that you won't be able to measure the difference

    also, TOP is proprietary to microsoft *ptui* and not valid in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast Dreeass's Avatar
    Join Date
    Sep 2012
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Since I can't recall if TOP 5 is valid for MySQL, you can also use LIMIT

    Code:
    SELECT columns FROM table ORDER BY ID DESC LIMIT 5
    Thanks, but got it by just Googling around a bit. I'm not very experienced with MySQL. Another question:

    I got a ton of information to store in the database and wonder what will be one of the best (easy and fast) ways to do it. I got a checkbox that specifies if you have to pay or not, then the category (if the selection is 'Other', then they have to specify that), 2 more textboxes and 3 checkboxes with each 4 checkboxes and around 11 text inputs (actually 11x3 text inputs) each (so if one of the 3 checkboxes is checked, then they have to fill in 4 more checkboxes of that category and 11 more text inputs) and to finish it off another textarea that has to be stored. I've never worked with a form this big that I have to store in a database. So what are your suggestions?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dreeass View Post
    I got a checkbox that specifies if you have to pay or not, then the category (if the selection is 'Other', then they have to specify that), 2 more textboxes and 3 checkboxes with each 4 checkboxes and around 11 text inputs (actually 11x3 text inputs) each (so if one of the 3 checkboxes is checked, then they have to fill in 4 more checkboxes of that category and 11 more text inputs) and to finish it off another textarea that has to be stored.
    my advice is to design the database tables to hold the data that you want to store
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,606
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, not "much more" efficient -- i'll wager that you won't be able to measure the difference
    I would have thought that an index on a 32-bit integer would be more efficient than one on however-many characters are needed to store a date. OK, perhaps not "much more" efficient, but surely the difference would be susceptible to measurement?

    Quote Originally Posted by r937 View Post
    also, TOP is proprietary to microsoft *ptui* and not valid in mysql
    Really? I didn't know that. I felt sure I had seen it in Oracle and perhaps other non-Microsoft products. But what do I know?

    Mike

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    I would have thought that an index on a 32-bit integer would be more efficient than one on however-many characters are needed to store a date.
    dates in mysql are stored in three (3) bytes

    still sticking to your "much more efficient" claim?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mikl View Post


    Really? I didn't know that. I felt sure I had seen it in Oracle and perhaps other non-Microsoft products. But what do I know?

    Mike
    Oracle uses a rownum criteria:

    Code:
    WHERE ROWNUM <= 5

  13. #13
    SitePoint Enthusiast Dreeass's Avatar
    Join Date
    Sep 2012
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    my advice is to design the database tables to hold the data that you want to store
    Would you give me an example? Cause what I think now is just make a huge table or make several tables linking to eachother but what should store what and what should link to what for the most efficient system.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dreeass View Post
    Would you give me an example?
    i'm afraid my answer is going to be just as nebulous as your scenario of checkboxes, textboxes, and checkboxes

    concentrate on cardinality (primary keys) to decide which fields you can have in the same table, and when you get to something that involves a one-to-many relationship, that should go into a separate table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast Dreeass's Avatar
    Join Date
    Sep 2012
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm afraid my answer is going to be just as nebulous as your scenario of checkboxes, textboxes, and checkboxes

    concentrate on cardinality (primary keys) to decide which fields you can have in the same table, and when you get to something that involves a one-to-many relationship, that should go into a separate table
    What if I only stored the main thing that I needed for a built in search function and store the other part in html code in a varchar?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dreeass View Post
    What if I only stored the main thing that I needed for a built in search function and store the other part in html code in a varchar?
    i think i've completely lost the context here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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