Want to keep a table to 10 records - having trouble deleting
Moderator: General Moderators
Want to keep a table to 10 records - having trouble deleting
Hi all,
Initial Constraints:
I have a table which I use to keep track of the latest posts made by any users on my site. The purpose of this table is so that I can have a "Latest 10 Posts" tracker on my main page. So far inserting data in to the table is OK, and I have a unique auto increment key called 'id' which I use to keep track of the records.
Goal:
My goal is to keep this table limited to only 10 records because since it's going on my main page and will be accessed constantly I don't want to have to search through a large table and grab the latest 10 posts; rather I would like to delete anything older than than last 10 since I won't be doing anything with it anyways.
Problem:
The following is the code I'm using in my PHP script:
$input = "DELETE FROM $tablename WHERE id < (SELECT MIN(id) FROM (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10))";
mysql_query($input) or die( "Error cleaning up Last 10 Posts.");
For some reason I keep having issues with this block of code and always go into the die part of the query - I"ve checked my table and it's not cleaning up either, but I can't tell what's wrong from the syntax.. to me what it's doing is first grabbing all the id's from the table ordering by the id's in a descending order and only returning the latest 10 entries. Then it grabs the lowest id from that range and says that anything lower than that id will be deleted.
That's what I want it to do, but obviously it doesn't work - any suggestions as to what is wrong?
Thanks!!
Card
Initial Constraints:
I have a table which I use to keep track of the latest posts made by any users on my site. The purpose of this table is so that I can have a "Latest 10 Posts" tracker on my main page. So far inserting data in to the table is OK, and I have a unique auto increment key called 'id' which I use to keep track of the records.
Goal:
My goal is to keep this table limited to only 10 records because since it's going on my main page and will be accessed constantly I don't want to have to search through a large table and grab the latest 10 posts; rather I would like to delete anything older than than last 10 since I won't be doing anything with it anyways.
Problem:
The following is the code I'm using in my PHP script:
$input = "DELETE FROM $tablename WHERE id < (SELECT MIN(id) FROM (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10))";
mysql_query($input) or die( "Error cleaning up Last 10 Posts.");
For some reason I keep having issues with this block of code and always go into the die part of the query - I"ve checked my table and it's not cleaning up either, but I can't tell what's wrong from the syntax.. to me what it's doing is first grabbing all the id's from the table ordering by the id's in a descending order and only returning the latest 10 entries. Then it grabs the lowest id from that range and says that anything lower than that id will be deleted.
That's what I want it to do, but obviously it doesn't work - any suggestions as to what is wrong?
Thanks!!
Card
Re: Want to keep a table to 10 records - having trouble deleting
You are approaching this the wrong way - you should be retrieving the 10 latest posts directly from the posts table using an ORDER BY modifier in the SELECT query
Re: Want to keep a table to 10 records - having trouble deleting
Thanks, but I don't quite follow, are you saying -pytrin wrote:You are approaching this the wrong way - you should be retrieving the 10 latest posts directly from the posts table using an ORDER BY modifier in the SELECT query
a) I should just have my post table be big and keep all my posts and just use SELECT and ORDER BY to grab the latest posts (which I explained I don't want to do, I just want to have a table of 10 records), or...
b) that I should use ORDER BY mod in SELECT to figure out which posts to keep? B/c I think I already do this:
$input = "DELETE FROM $tablename WHERE id < (SELECT MIN(id) FROM (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10))";
Any specific code pointers would be helpful.
Thanks!
Card
Re: Want to keep a table to 10 records - having trouble deleting
It's option A. Keeping a separate table is unnecessary and cubmersome. I don't know what you consider to be a "big" table, but MySQL can handle very large datasets very effectively. Aren't you keeping all posts anyway? are you deleting and post beyond the last ten?
Re: Want to keep a table to 10 records - having trouble deleting
The reason for the table is that the info in the table is basically a union of data (but not the whole set) of two other tables.pytrin wrote:It's option A. Keeping a separate table is unnecessary and cubmersome. I don't know what you consider to be a "big" table, but MySQL can handle very large datasets very effectively. Aren't you keeping all posts anyway? are you deleting and post beyond the last ten?
For example, Table A has (1,2,3,4,5,6), Table B has (A,B,C,D,E,F), the post table mentioned above would have (1,2,3,C,D,E). And the logic behind the additional table is that space is cheap but processing is not, i.e. doing JOIN, ORDER, etc for a table that could quickly reach millions of entries very quickly, and the results (the last 10 posts) are to be shown constantly when my main page loads.
I know having an extra table is cumbersome, but if I do go that way, can you see anything wrong with my code?
Eventually I'll have to clean out the table anyways, whether I keep 10 or 1,000,000,000 entries someday I'll have to delete stuff from it, and most likely I'll choose to keep the newest stuff, so it'd be some modified version of the code below (which doesn't seem to work right now).
Thanks!
Card
Re: Want to keep a table to 10 records - having trouble deleting
You are optimizing way too early in my opinion (and I would use a cache, not a separate table), but try this -
[sql]DELETE FROM $tablename WHERE id < (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10,1)[/sql]
[sql]DELETE FROM $tablename WHERE id < (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10,1)[/sql]
Re: Want to keep a table to 10 records - having trouble deleting
thanks for this! but i still get the same die() error, copied the thing verbatim and changed the tablename to my table's name.pytrin wrote:You are optimizing way too early in my opinion (and I would use a cache, not a separate table), but try this -
[sql]DELETE FROM $tablename WHERE id < (SELECT id FROM $tablename ORDER BY id DESC LIMIT 10,1)[/sql]
from the code it looks like it'll delete whatever the 11th entry is - so if it's empty, it does nothing, but as the table grows it'll continually have the 11th row as a "hole" that marks the place where stuff gets deleted correct?
any clues on the error though? your code looks correct (although i think mine does too), the PHP version on my server is PHP Version 5.2.12.
thanks!
card
Re: Want to keep a table to 10 records - having trouble deleting
Instead of die('your message here') output the actual mysql error -
Code: Select all
$result = mysql_query(...delete query...);
if($result === false) {
echo mysql_error();
}Re: Want to keep a table to 10 records - having trouble deleting
Often for things that involve a heavy query and need to be shown frequently (such as stats, or even number of members online), I will have a cron script run every 5 minutes or so and write the results to a database table field or a small text file.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Re: Want to keep a table to 10 records - having trouble deleting
thank you much for this! as you can tell i'm still very new to PHP and SQL and have basically learned stuff along the way to fit the design of my site.pytrin wrote:Instead of die('your message here') output the actual mysql error -Code: Select all
$result = mysql_query(...delete query...); if($result === false) { echo mysql_error(); }
i resolved the problem, the error i got was "you can't specify target table 'xxx' for an UPDATE in FROM clause...", solved it by cutting up the query into two queries, returned the resource for the first one, grabbed the id and then continued with the second half of the query.
and thanks s.dot for the suggestion, i think for the latest posts i can't really use a script, but for members online and stuff i'll keep that in mind (rather than on-demand updates).
thanks guys!!
card