Want to keep a table to 10 records - having trouble deleting

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

Want to keep a table to 10 records - having trouble deleting

Post by cardinal »

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Want to keep a table to 10 records - having trouble deleting

Post by Eran »

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
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

Re: Want to keep a table to 10 records - having trouble deleting

Post by cardinal »

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
Thanks, but I don't quite follow, are you saying -

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Want to keep a table to 10 records - having trouble deleting

Post by Eran »

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?
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

Re: Want to keep a table to 10 records - having trouble deleting

Post by cardinal »

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?
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.

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Want to keep a table to 10 records - having trouble deleting

Post by Eran »

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]
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

Re: Want to keep a table to 10 records - having trouble deleting

Post by cardinal »

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]
thanks for this! but i still get the same die() error, copied the thing verbatim and changed the tablename to my table's name.

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Want to keep a table to 10 records - having trouble deleting

Post by Eran »

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();
}
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Want to keep a table to 10 records - having trouble deleting

Post by s.dot »

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.
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

Re: Want to keep a table to 10 records - having trouble deleting

Post by cardinal »

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();
}
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.

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
Post Reply