Method for deleting discarded stories

[fingerprn]

So far, I haven't found a modification on here for completely deleting a story from the database. For example, even though the link may be removed, the votes, comments, etc. remain. Therefore, I've decided to create my own solution and thought I'd share it with you.

I'll warn you upfront that I'm not a developer and I don't know a thing about object oriented programming. Therefore, I'm sure there's a better way to do this other than my little hack. Still, the result is the same so I'm sticking with this until somebody comes up with something better.

Version: 1.2

STEP 1:

Open admin_links_center.tpl (filename corrected from initial post) and find "<a href="javascript:uncheck_all()">Uncheck All</a>". Add the following:

<br><a href="admin_links.php" onclick="javascript:window.open('admin_delete_stories.php', 'resultwindow', 'width=300, height=200')">Delete discarded stories</a>

STEP 2:

Create a file called "admin_delete_stories.php" and add the following:

<?php
include_once('Smarty.class.php');
$main_smarty = new Smarty;

include('config.php');
include(mnminclude.'html1.php');
include(mnminclude.'link.php');
include(mnminclude.'user.php');
include(mnminclude.'smartyvariables.php');
			
// require user to log in
force_authentication();

// restrict access to god only
$amIgod = 0;
$amIgod = $amIgod + checklevel('god');
$main_smarty->assign('amIgod', $amIgod);

$canIhaveAccess = 0;
$canIhaveAccess = $canIhaveAccess + checklevel('god');
$canIhaveAccess = $canIhaveAccess + checklevel('admin');

if($canIhaveAccess == 0){	
	$main_smarty->assign('tpl_center', $the_template . '/admin_templates/admin_access_denied');
	$main_smarty->display($the_template . '/pligg.tpl');		
	die();
}


function delete_storylink($linkid) {

    $query="SELECT * FROM " . table_links . " WHERE link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}
    else {$sql_array = mysql_fetch_object($result); }

    # delete the story link
    $query="DELETE FROM " . table_links . " WHERE link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}
    
    # delete the story comments
    $query="DELETE FROM " . table_comments . " WHERE comment_link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}
	
    # delete the story pageviews
    $query="DELETE FROM " . table_pageviews . " WHERE (pv_type = 'story' OR pv_type = 'out') AND pv_page_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}
	
    # delete the saved links
    $query="DELETE FROM " . table_saved_links . " WHERE saved_link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}	
	
    # delete the story tags
    $query="DELETE FROM " . table_tags . " WHERE tag_link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}	 		 

    # delete the story trackbacks
    $query="DELETE FROM " . table_trackbacks . " WHERE trackback_link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}    

    # delete the story votes
    $query="DELETE FROM " . table_votes . " WHERE vote_link_id = '$linkid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}   
  
	}

	
$sql_query = "SELECT * FROM " . table_links . " WHERE link_status = 'discard'";
			
$result_storylinks = mysql_query($sql_query);
$num_rows = mysql_num_rows($result_storylinks);
		while($storylink = mysql_fetch_object($result_storylinks))
		{
			delete_storylink($storylink->link_id);
		}
	
# set discards total to zero
$query="UPDATE " . table_totals . " SET total = '0' WHERE name = 'discard'";
if (!mysql_query($query)) {die (mysql_error());}

	
echo $num_rows. " discarded stories deleted";

?>
<p><a href="admin_optimize_database.php">Click here</a> to optimize database</p>

STEP 3 (OPTIONAL):

This next file allows you to optimize your database after deleting your stories. This isn't necessary, but it's a good idea because the deleted stories will create overhead in your MySQL database.

Create a file called "admin_optimize_database.php" and add the following:

<?php
include_once('Smarty.class.php');
$main_smarty = new Smarty;

include('config.php');
include(mnminclude.'html1.php');
include(mnminclude.'link.php');
include(mnminclude.'user.php');
include(mnminclude.'smartyvariables.php');
			
// require user to log in
force_authentication();

// restrict access to god only
$amIgod = 0;
$amIgod = $amIgod + checklevel('god');
$main_smarty->assign('amIgod', $amIgod);

$canIhaveAccess = 0;
$canIhaveAccess = $canIhaveAccess + checklevel('god');
$canIhaveAccess = $canIhaveAccess + checklevel('admin');

if($canIhaveAccess == 0){	
	$main_smarty->assign('tpl_center', $the_template . '/admin_templates/admin_access_denied');
	$main_smarty->display($the_template . '/pligg.tpl');		
	die();
}

// $message = "";

    $query = "SHOW TABLE STATUS";
    $result=mysql_query($query);
    $table_list = "";
    while ($cur_table = mysql_fetch_object($result)) {
        $table_list .= $cur_table->Name.", ";
    }

    if (!empty($table_list)) {
        $table_list = substr($table_list, 0, -2);
        $query = "OPTIMIZE TABLE ".$table_list;
        mysql_query($query);
        $message = "Database optimized";
    }

echo $message

?>

Version changes:
Version 1.1 (12-31-2007): Made accessible to administrators only.
Version 1.2 (5-4-2008): Removed need for custom error page.

[chuckroast]

Nice work, and thank you for sharing.

[swiper]

Thanks for this. I had just posted the other day asking if there was any reason partially submitted stories are logged to the database with a status of discard. This will also help take care of these. Thanks!

[crakez]

Thanks a lot for sharing.

[anakcerdas]

Wow.. Thanks.

I used to delete the data directly in phpmyadmin.

This works great.

[blaze]

In your STEP 1, are you actually referring to admin_links_center.tpl instead of admin_links_center.php? Because there isn't a admin_links_center.php file in my version (v. 9.8.2).

[noyp]

nice work

cool!

noyP

[argh2xxx]

this method rocks, and you're good with database stuffs!

[timofsuburbia]

Thanks so much!

[nzbullet]

Originally Posted by blaze

In your STEP 1, are you actually referring to admin_links_center.tpl instead of admin_links_center.php? Because there isn't a admin_links_center.php file in my version (v. 9.8.2).

Yes - The file is actually admin_links_center.tpl - well at least it is in 9.8.2

fingerprn - Awesome mod. Nice and easy to add to site, following instructions you posted. Also easy to use.

One comment the file, incase anyone can't find it, is located as follows:

/pligg/templates/yget/admin_templates/admin_links_center.tpl

The new files should just be added to the root directory of your site along with the other "admin_.......php" files.