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.
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.
[FONT=verdana]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
[/FONT]
Since I can’t recall if TOP 5 is valid for MySQL, you can also use LIMIT
SELECT columns FROM table ORDER BY ID DESC LIMIT 5
[FONT=verdana]
Ah, now you mention it, I think you’re right about TOP N not being valid in MySQL.
Mike
[/FONT]
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
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?
my advice is to design the database tables to hold the data that you want to store
[FONT=verdana]
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?
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[/FONT]
dates in mysql are stored in three (3) bytes
still sticking to your “much more efficient” claim?
Oracle uses a rownum criteria:
WHERE ROWNUM <= 5
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.
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?
i think i’ve completely lost the context here