mysql: how can i remove old rows at the table?

irmtfan · October 5th, 2004, 3:12 PM

i want remove the old rows at my pmsg table.
i want select by the field for example only delete the pms that user read it ( read_msg = '1' ) and have a specific subject ( subject="blah blah" )
i have read_msg and subject and msg_time fields at this table.
i dont know what query must run? what code?
any one can write it or get a hand?
thanks in advance

**sam** · October 5th, 2004, 5:13 PM

how goes it irmtfan

need a bit more detail about yoru database table to help you wiht the query.

please print out a few rows from it, including the field names like thus.

id | user | readmsg | msg
1 | 211 | 1 | blah blah blah blah
2 | 212 | 0 | here we go!

The query shouldn't be too complicated.

irmtfan · October 6th, 2004, 5:34 AM

table is: xoops_priv_msgs
fields:
msg_id | msg_image | subject | from_userid | to_userid | msg_time | msg_text | read_msg

1 | icon1.gif | Blah Blah | 7 | 283 | 1087152452 | Blah Blah | 0

this is fields:
--
-- Table structure for table `xoops_priv_msgs`
--

CREATE TABLE `xoops_priv_msgs` (
`msg_id` mediumint(8) unsigned NOT NULL auto_increment,
`msg_image` varchar(100) default NULL,
`subject` varchar(255) NOT NULL default '',
`from_userid` mediumint(8) unsigned NOT NULL default '0',
`to_userid` mediumint(8) unsigned NOT NULL default '0',
`msg_time` int(10) unsigned NOT NULL default '0',
`msg_text` text NOT NULL,
`read_msg` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`msg_id`),
KEY `to_userid` (`to_userid`),
KEY `touseridreadmsg` (`to_userid`,`read_msg`),
KEY `msgidfromuserid` (`msg_id`,`from_userid`)
) TYPE=MyISAM AUTO_INCREMENT=108018 ;

irmtfan · October 18th, 2004, 2:55 AM

can anyone take a look at this?
size of this table is 70 Mb and i want delete old rows to reduce db size and user inbox that get auto notifications with pm

**spartas** · October 18th, 2004, 11:15 AM

To delete all that have been read, use

DELETE from xoops_priv_msg WHERE read_msg='1'

but to do it with a subject filter as well, just alter the code to say the following:

DELETE from xoops_priv_msg WHERE read_msg='1' AND subject='whichever_subject_you_have_an_inate_deter mined_mindset_to_delete_goes_here'

irmtfan · October 18th, 2004, 4:30 PM

and for the msg_time field?
can i write this :
DELETE from xoops_priv_msg WHERE msg_time<='1087152452'

**Kickersny.com** · October 18th, 2004, 7:53 PM

could set up a chron job script to do it.

PHP Code:


			
$delete_sql = "DELETE FROM `table_name` WHERE `read_msg` = '1'";

$delete_result = mysql_query( $delete_sql, $conn ) or die( mysql_error() );

irmtfan · October 19th, 2004, 5:32 AM

thanks all of u.
only for the msg_time how can i put the variable to back a number and not a static number.
for example when the script run get date from one month later and change it to 10 digit format ?

DELETE from xoops_priv_msg WHERE msg_time<='1087152452' read_msg='1' AND subject='whichever_subject_you_have_an_inate_deter mined_mindset_to_delete_goes_here'

**spartas** · October 19th, 2004, 11:59 AM

Quote:

Originally Posted by irmtfan

thanks all of u.
only for the msg_time how can i put the variable to back a number and not a static number.
for example when the script run get date from one month later and change it to 10 digit format ?

DELETE from xoops_priv_msg WHERE msg_time<='1087152452' read_msg='1' AND subject='whichever_subject_you_have_an_inate_deter mined_mindset_to_delete_goes_here'

You would have to use a PHP (or some other language) script that is able to use SQL to manipulate your table.

The best way, as KickersNy put it, is to use a chron job that automatically executes the script at a certain time, say once a month on the first Wednesday of the month. Then you would use the following formula in your script:

PHP Code:


			


$delete_time = time() - ( 7 * 24 * 60 * 60 );  // 7 days/week * 24 hrs/day

// * 60 minutes/hour * 60 seconds/min



$sql = "DELETE from `xoops_priv_msg` WHERE `read_msg` = '1' AND `msg_time` > '$delete_time'";



$result = mysql_query($sql, $cxn); // $cxn is short for the connection. You did previously begin a connection, right?

I thank KickersNY for reminding me to put backticks in the query. That's very important when there are spaces in the field names or field names are using reserved words.

October 5th, 2004, 3:12 PM	#1 (permalink)
irmtfan Registered User Comfy Contributor Joined in Jun 2004 127 posts Gave thanks: 0 Thanked 0 times	mysql: how can i remove old rows at the table? i want remove the old rows at my pmsg table. i want select by the field for example only delete the pms that user read it ( read_msg = '1' ) and have a specific subject ( subject="blah blah" ) i have read_msg and subject and msg_time fields at this table. i dont know what query must run? what code? any one can write it or get a hand? thanks in advance __________________ Ready For Romance first persian harry potter site: www.jadoogaran.org(means wizards.org in english) ٌWelcome dedi

October 6th, 2004, 5:34 AM	#3 (permalink)
irmtfan Registered User Comfy Contributor Joined in Jun 2004 127 posts Gave thanks: 0 Thanked 0 times	table is: xoops_priv_msgs fields: msg_id \| msg_image \| subject \| from_userid \| to_userid \| msg_time \| msg_text \| read_msg 1 \| icon1.gif \| Blah Blah \| 7 \| 283 \| 1087152452 \| Blah Blah \| 0 this is fields: -- -- Table structure for table `xoops_priv_msgs` -- CREATE TABLE `xoops_priv_msgs` ( `msg_id` mediumint(8) unsigned NOT NULL auto_increment, `msg_image` varchar(100) default NULL, `subject` varchar(255) NOT NULL default '', `from_userid` mediumint(8) unsigned NOT NULL default '0', `to_userid` mediumint(8) unsigned NOT NULL default '0', `msg_time` int(10) unsigned NOT NULL default '0', `msg_text` text NOT NULL, `read_msg` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`msg_id`), KEY `to_userid` (`to_userid`), KEY `touseridreadmsg` (`to_userid`,`read_msg`), KEY `msgidfromuserid` (`msg_id`,`from_userid`) ) TYPE=MyISAM AUTO_INCREMENT=108018 ; __________________ Ready For Romance first persian harry potter site: www.jadoogaran.org(means wizards.org in english) ٌWelcome dedi Last edited by irmtfan; October 6th, 2004 at 5:37 AM..

October 18th, 2004, 2:55 AM	#4 (permalink)
irmtfan Registered User Comfy Contributor Joined in Jun 2004 127 posts Gave thanks: 0 Thanked 0 times	can anyone take a look at this? size of this table is 70 Mb and i want delete old rows to reduce db size and user inbox that get auto notifications with pm __________________ Ready For Romance first persian harry potter site: www.jadoogaran.org(means wizards.org in english) ٌWelcome dedi

October 18th, 2004, 11:15 AM	#5 (permalink)
spartas surmunity freak Comfy Contributor Joined in Sep 2004 Lives in Baton Rouge, LA, North Amewica, USA, Earth Hosted on D68; Serva; Pass15 143 posts Gave thanks: 0 Thanked 0 times	To delete all that have been read, use DELETE from xoops_priv_msg WHERE read_msg='1' but to do it with a subject filter as well, just alter the code to say the following: DELETE from xoops_priv_msg WHERE read_msg='1' AND subject='whichever_subject_you_have_an_inate_deter mined_mindset_to_delete_goes_here' __________________ Server: Pass15 twwright.net NoE Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.6) Gecko/20050405 Firefox/1.0 (Ubuntu package 1.0.2) I run ubuntu hoary!

October 18th, 2004, 4:30 PM	#6 (permalink)
irmtfan Registered User Comfy Contributor Joined in Jun 2004 127 posts Gave thanks: 0 Thanked 0 times	and for the msg_time field? can i write this : DELETE from xoops_priv_msg WHERE msg_time<='1087152452' __________________ Ready For Romance first persian harry potter site: www.jadoogaran.org(means wizards.org in english) ٌWelcome dedi

October 5th, 2004, 5:13 PM	#2 (permalink)
sam Surpass Fan Super #1 Joined in Dec 2003 Lives in NJ 5,057 posts Gave thanks: 0 Thanked 4 times	how goes it irmtfan need a bit more detail about yoru database table to help you wiht the query. please print out a few rows from it, including the field names like thus. id \| user \| readmsg \| msg 1 \| 211 \| 1 \| blah blah blah blah 2 \| 212 \| 0 \| here we go! The query shouldn't be too complicated.

October 18th, 2004, 7:53 PM	#7 (permalink)
Kickersny.com Surpass Fan Super #1 Joined in Aug 2004 Hosted on SH58 1,688 posts Gave thanks: 6 Thanked 7 times	could set up a chron job script to do it. PHP Code: $delete_sql = "DELETE FROM `table_name` WHERE `read_msg` = '1'"; $delete_result = mysql_query( $delete_sql, $conn ) or die( mysql_error() ); __________________ - Evan Charlton \| [site] \| Server - SH58

October 19th, 2004, 5:32 AM	#8 (permalink)
irmtfan Registered User Comfy Contributor Joined in Jun 2004 127 posts Gave thanks: 0 Thanked 0 times	thanks all of u. only for the msg_time how can i put the variable to back a number and not a static number. for example when the script run get date from one month later and change it to 10 digit format ? DELETE from xoops_priv_msg WHERE msg_time<='1087152452' read_msg='1' AND subject='whichever_subject_you_have_an_inate_deter mined_mindset_to_delete_goes_here' __________________ Ready For Romance first persian harry potter site: www.jadoogaran.org(means wizards.org in english) ٌWelcome dedi

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread: