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
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?
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?
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 > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[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
