[PHP-DB] Deleting topics older than 2 hours?
I'm trying to run a cron job to delete forum topics more than 2 hours old. I know how to do that, but the problem is also deleting the posts in the topic at the same time. Can someone shed some light on this, please? -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Deleting topics older than 2 hours?
Sounds to me like you didn't understand the question. The table structure is something like: topics_ | id | forum | author | subject | posttime | - _posts | id | topic | author | message | posttime| - The problem is, if I run a query like (I know the query is probably wrong, I'm just writing it now): delete from topics where posttime + interval 2 hours now() I would leave orphan posts inside of that topic. I'm not sure how (or if it's even possible) to delete the posts as the topic is deleted. DL Neil wrote: Sorry Leif, but CrystalBall v2.6 is currently unable to help. The word WHERE features. Show us the money - or second choice: the (erroneous) code... =dn I'm trying to run a cron job to delete forum topics more than 2 hours old. I know how to do that, but the problem is also deleting the posts in the topic at the same time. Can someone shed some light on this, please? -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law.
Re: [PHP-DB] Deleting topics older than 2 hours?
Leif, Sounds to me like you didn't understand the question. The table =two sides to every story: either I'm too thick to understand, or you didn't explain sufficiently - or both... see problem with necessarily open-ended answer given below and consider... structure is something like: topics_ | id | forum | author | subject | posttime | - _posts | id | topic | author | message | posttime| - The problem is, if I run a query like (I know the query is probably wrong, I'm just writing it now): delete from topics where posttime + interval 2 hours now() I would leave orphan posts inside of that topic. I'm not sure how (or if it's even possible) to delete the posts as the topic is deleted. =some RDBMSes implement referential integrity/a 'chaining process' built from key-dependences, and thus when a row is DELETEd from topics, any corresponding rows would be removed from posts as well. =others, eg MySQL, do not, and thus unless MySQL's last DELETE format option works sufficiently well, the best option might be two DELETE's: the first per your suggestion above and the second to remove all entries in posts that no longer join to a topics row. =Regards, =dn DL Neil wrote: Sorry Leif, but CrystalBall v2.6 is currently unable to help. The word WHERE features. Show us the money - or second choice: the (erroneous) code... =dn I'm trying to run a cron job to delete forum topics more than 2 hours old. I know how to do that, but the problem is also deleting the posts in the topic at the same time. Can someone shed some light on this, please? -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Deleting topics older than 2 hours?
[snip] =others, eg MySQL, do not, and thus unless MySQL's last DELETE format option works sufficiently well, the best option might be two DELETE's: the first per your suggestion above and the second to remove all entries in posts that no longer join to a topics row. But you can't join on a DELETE, either (in MySQL at least). So you're left with writing a whole PHP script to do it. Run a SELECT to get all of the topics with IDs older than two hours. Roll up all of those IDs into a comma separated list, like (1,2,3,4) etc. Then run two DELETE queries on each table. DELETE FROM topics WHERE id IN $id_string DELETE FROM posts WHERE topic IN $id_string Is there a better way to organize your data so this is all in one table?? Maybe just add another column to say whether the message is a topic or a post? Then your deletes could be done with single query, probably. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
