[SOLVED] Need help with a SQL query...

[sbourdon]

Hello everyone!

 

Not being too comfortable with SQL queries, I would like your help to accomplish the following...

 

SQL Query must do the following:

 

1. Look for pictures in the /images/IMG directory

2. Look for [img:xxxxxxxxxx][yyy][/img:xxxxxxxxxx] tags in the phpbb_posts_text, phpbb_privmsgs_text, phpbb_users and phpbb_shout tables

3. Compare the 2 and delete pics from the IMG directory that no longer appear in those tables

 

Example: A post has been created using the following image: [img:3e25fd8174][images/IMG/sbourdon_logo_phpBB.gif][/img:3e25fd8174]

In the IMG directory, the image sbourdon_logo_phpBB.gif has been copied.

If the post is ever edited or deleted and that picture is no longer being used, I want it to be removed from the IMG directory.

 

What should the SQL query look like?

 

 

Thanks!

[cmgmyr]

You first will have to scan the directory for images. Then take each image and do a query like this:

 

$sql = "SELECT * FROM posts WHERE body LIKE \"%$img_name%\"";

 

count the number of rows in the query

 

if($count ==0){

delete image

}

 

...move on to next image

 

Hope this helps

[jitesh]

If using this then make a change

 

$sql = "SELECT * FROM posts WHERE body LIKE '%$img_name%'";

 

[sbourdon]

Thanks a lot for these quick answers!

 

Unfortunately, I'm a complete newbie at this so I'm gonna need a little bit more details...

How do I count the number of rows in the query?  And how do I move on to the next image?

 

Sorry if this seems ridiculous but we all have to start somewhere...

[per1os]

mysql_num_rows($result);

while ($row = mysql_fetch_array($result)) {
   print $row[0]  '<br />'; // prints the first column
}

[cmgmyr]

Try this out:

 

<?php 

include "connect.php";
$files = "";

$dir = "images/products"; //no trailing slash

$path = opendir($dir);
while (false !== ($file = readdir($path))) {
	if($file!="." && $file!="..") {
		if(is_file($dir."/".$file))
			$files[]=$file;
		else 
			$dirs[]=$dir."/".$file;            
	}
}
if($files) {
	//Output information
	natcasesort($files);
	$x = 0;
	foreach ($files as $file){
		//This is where you want to run your query
		$sql = "SELECT * FROM posts WHERE body LIKE \"%$file%\"";
		$result = mysql_query($sql);
		$count = mysql_num_rows($result);
		if($count == 0){
			unlink($dir.$file);
		}
	}
}
closedir($path);
?>

[sbourdon]

Thanks a LOT!

 

We're almost there!  Only 2 problems remain:

1. ALL images get deleted when this script is executed;

2. The following error message shows up each time the script is executed:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

 

 

I've inserted the code in the page_header.php file, and here's what it looks like as of now:

 

// Delete old IMG	
$files = "";

$dir = "images/thumbs/"; //no trailing slash

$path = opendir($dir);
while (false !== ($file = readdir($path))) {
	if($file!="." && $file!="..") {
		if(is_file($dir."/".$file))
			$files[]=$file;
		else 
			$dirs[]=$dir."/".$file;            
	}
}
if($files) {
	//Output information
	natcasesort($files);
	$x = 0;
	foreach ($files as $file){
		//This is where you want to run your query
		$sql = "SELECT * FROM " . POSTS_TEXT_TABLE . " WHERE body LIKE \"%$file%\"";
		$result = mysql_query($sql);
		$count = mysql_num_rows($result);
		if($count == 0){
			unlink($dir.$file);
		}
	}
}
closedir($path);
// Delete old IMG

 

I had to include a trailing slash in the code, even though it said not to...

 

Any ideas on how to solve these remaining issues? 

 

 

Regards,

sbourdon

[sbourdon]

Got it!

Simply had to change body to post_text!

 

Newbie mistake... 

 

 

Thanks to all for your help!