Page 1 of 1

Proper mysql syntax for my script

Posted: Mon May 15, 2006 7:45 pm
by dotsc
I have a classified ad website, its getting a bit popular now and i've been getting spammed people. They always use their web addresses to link back to their site and I've this clean up script which suppose to help me with it but I am having a problem figuring out the right way for the mysql line.

This script runs every hour using cron, deletes ads if the keywords are found within the ads:

Code: Select all

<?php

$path_escape = "../";
if (!$config_loaded)
{
	require_once("{$path_escape}config.inc.php");
	$cleanup_logfile = "{$path_escape}log/spam.txt";
}
else
{
	$cleanup_logfile = "{$path_escape}log/spam.txt";
}

ob_start();

// Get last run time
$fp = @fopen($cleanup_logfile, "r");
if ($fp)
{
	$lastrun = 0+trim(fgets($fp, 1024));
	fclose($fp);
}
else
{
	$lastrun = 0;
}


// Cleanup if last run was before 23hrs
if ($lastrun < time()-23*60*60)
{
	// Log header
	$cleanup_start_time = time();
	echo $cleanup_start_time . "\r\n";
	echo "\r\n";
	echo "Time: " . gmdate("r", $cleanup_start_time) . "\r\n";
	echo "Last run: " . ($lastrun ? gmdate("r", $lastrun) : "-") . "\r\n";
	echo "\r\n";

    /////////////////////////////////////////////////////////////////////////////////////////////
	// Ads   content_body LIKE '%$search_term_esc%'
	$sql = "SELECT adid FROM $t_ads WHERE adtitle OR addesc LIKE '%highclass-escorts.net%' OR LIKE '%locate-escorts.net%' OR LIKE '%adultescortlist.com%' OR LIKE '%friendfinding%' OR LIKE '%ClubC2C.com%' OR LIKE '%camchatgirl.com%' OR LIKE '%locate-escorts.net%' OR LIKE '%urldo.com%' OR LIKE '%sensualdesires%'";
	$res = mysql_query($sql);
	while($row=mysql_fetch_array($res))
	{
		$adid = $row['adid'];

		echo "$t_adxfields: ";
		$sql = "DELETE FROM $t_adxfields WHERE adid = $adid";
		if(mysql_query($sql)) echo mysql_affected_rows();
		else echo $sql." ".mysql_error();
		echo "\r\n";

		$sql = "SELECT picfile FROM $t_adpics WHERE adid = $adid AND isevent = '0'";
		$pres = mysql_query($sql);
		while($p=mysql_fetch_array($pres))
		{
			unlink("{$path_escape}adpics/$p[picfile]");
		}

		echo "$t_adpics (A): ";
		$sql = "DELETE FROM $t_adpics WHERE adid = $adid AND isevent = '0'";
		if(mysql_query($sql)) echo mysql_affected_rows();
		else echo $sql." ".mysql_error();
		echo "\r\n";

		echo "$t_featured (A): ";
		$sql = "DELETE FROM $t_featured WHERE adid = $adid AND adtype = 'A'";
		if(mysql_query($sql)) echo mysql_affected_rows();
		else echo $sql." ".mysql_error();
		echo "\r\n";

		echo "$t_promos_featured (A): ";
		$sql = "DELETE FROM $t_promos_featured WHERE adid = $adid AND adtype = 'A'";
		if(mysql_query($sql)) echo mysql_affected_rows();
		else echo $sql." ".mysql_error();
		echo "\r\n";

		echo "$t_promos_extended (A): ";
		$sql = "DELETE FROM $t_promos_extended WHERE adid = $adid AND adtype = 'A'";
		if(mysql_query($sql)) echo mysql_affected_rows();
		else echo $sql." ".mysql_error();
		echo "\r\n";

	}

	echo "$t_ads: ";
        /////////////////////////////////////////////////////////////////////////////////////////////
    $sql = "DELETE FROM $t_ads WHERE adtitle OR addesc LIKE '%highclass-escorts.net%' OR LIKE '%locate-escorts.net%' OR LIKE '%adultescortlist.com%'  OR LIKE '%friendfinding%' OR LIKE '%ClubC2C.com%' OR LIKE '%camchatgirl.com%' OR LIKE '%locate-escorts.net%' OR LIKE '%urldo.com%' OR LIKE '%sensualdesires%'";

    if(mysql_query($sql)) echo mysql_affected_rows();
	else echo $sql." ".mysql_error();
	echo "\r\n";


	$op = ob_get_contents();
	$fp = @fopen($cleanup_logfile, "w");
	@fwrite($fp, $op);
	fclose($fp);

}

unset($fp);

ob_clean();

?>
If someone could point me in the right direction, I would appriciate it. Also, if anyone has any suggestions how I can implement a simple text file where I would be able to add the keywords (for ex. 1 per line or comma seperated), so I would not have to change up the script every time i need to add or remove a keyword.

Thank you from a php newbie

Posted: Mon May 15, 2006 11:03 pm
by RobertGonzalez
Which SQL are you having challenges with? There are some syntax things that could be cleared up in your select statements. Your delete statement seem to be OK.

Posted: Tue May 16, 2006 2:08 am
by dotsc
It is this part, it's just not deleting the ads with the terms I've entered as you can see in the syntax below:

Code: Select all

$sql = "DELETE FROM $t_ads WHERE adtitle OR addesc LIKE '%highclass-escorts.net%' OR LIKE '%locate-escorts.net%' OR LIKE '%adultescortlist.com%'  OR LIKE '%friendfinding%' OR LIKE '%ClubC2C.com%' OR LIKE '%camchatgirl.com%' OR LIKE '%locate-escorts.net%' OR LIKE '%urldo.com%' OR LIKE '%sensualdesires%'";
This is definately the wrong way of doing it, because it's not working. I've tried using just 1 term using this syntax and it has worked fine. But as soon as I add the extra OR LIKE statements, it just doesn't delete anything. Anyone has any suggestions what im doing wrong?

Posted: Tue May 16, 2006 3:09 am
by sava
It shoud be:

Code: Select all

$sql = "DELETE FROM $t_ads WHERE addesc LIKE '%highclass-escorts.net%' OR addesc  LIKE '%locate-escorts.net%' OR addesc LIKE '%adultescortlist.com%'  OR addesc LIKE '%friendfinding%' OR addesc LIKE '%ClubC2C.com%' OR addesc LIKE '%camchatgirl.com%' OR addesc LIKE '%locate-escorts.net%' OR addesc LIKE '%urldo.com%' OR addesc LIKE '%sensualdesires%'";
You can store the blocked domains in separate text file and retrive it automaticly:

Code: Select all

$domainsArr = file('blackList.txt');
$sql = "DELETE FROM $t_ads WHERE ";

foreach($domainsArr as $domain) {
     $sql .= "addesc LIKE '%".$domain."%' OR ";
}

$sql = preg_replace("/\ OR\ $/","",$sql);

Posted: Tue May 16, 2006 11:08 am
by RobertGonzalez
Try this...

Code: Select all

<?php
$sql = "DELETE FROM $t_ads 
		WHERE addesc LIKE '%highclass-escorts.net%' 
		OR addesc LIKE '%locate-escorts.net%' 
		OR addesc LIKE '%adultescortlist.com%' 
		OR addesc LIKE '%friendfinding%' 
		OR addesc LIKE '%ClubC2C.com%' 
		OR addesc LIKE '%camchatgirl.com%' 
		OR addesc LIKE '%locate-escorts.net%' 
		OR addesc LIKE '%urldo.com%' 
		OR addesc LIKE '%sensualdesires%'";
?>
Then replace `addesc` with `adtitle` and repeat. Then set something up on your app to prevent these from ever getting to your database.