How I can find posts with missing files?

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Post Reply
luconsta
Registered User
Posts: 19
Joined: Thu Sep 24, 2009 3:03 pm

How I can find posts with missing files?

Post by luconsta » Thu Aug 30, 2018 11:13 am

Hello,

I've upgraded recently to 3.2.2 and I've noticed that some of my old posts have missing files. Is there a tool that could check what files from the phpbb_attachments table no more exists in the /files folder? With other words I would like to know how many post will throw "file not found" error.

Don't know the reason, I've noticed that "something" happened (before upgrade to 3.2.2) and a lot of posts in a certain period of two weeks are missing, and exactly for that period I do not have a backup, jo I just want to assess the damage.

Kind regards,
Lucian

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2796
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How I can find posts with missing files?

Post by thecoalman » Thu Aug 30, 2018 1:32 pm

This script should work, upload to your phpBB directory and then use your browser to run it. Immediately delete it once you run it.

You need to insert the credentials for the database and edit in your domain.

Code: Select all

<?php
error_reporting(E_ALL);
set_time_limit(300);

//Edit in your username, password and database name
$conn = mysqli_connect( 'localhost', 'username', 'password', 'database_name');
mysqli_set_charset( $conn, 'utf8');

$sql = "SELECT * FROM phpbb_attachments";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
	$post_id = $row["post_msg_id"];
	$upload_name =  $row["real_filename"];
	$filename = __DIR__ . '/files/' . $row["physical_filename"];
	
	

	if (!file_exists($filename)) {
	//Edit in your domain and folder for phBB if applicable.
	echo '<a href="https://yourdomain.com/forum/viewtopic.php?p=' . $post_id . '#p' . $post_id . '">' . $upload_name . '</a></br></br>';
	} 
}
?>
This will output some links for the posts where an attachment should be present but the file does not exist. Two things to note, if the thumbnail exists and the full file is missing you'll still see the thumb in the post. The other thing is if the attachment was deleted from the post and the attachments table was never updated for whatever reason there will be no attachment on the post.

luconsta
Registered User
Posts: 19
Joined: Thu Sep 24, 2009 3:03 pm

Re: How I can find posts with missing files?

Post by luconsta » Thu Aug 30, 2018 5:25 pm

Super! Thank you very much! It was perfect!

User avatar
John connor
Registered User
Posts: 1726
Joined: Fri Nov 14, 2014 5:14 pm
Location: U S Of A
Contact:

Re: How I can find posts with missing files?

Post by John connor » Sun Sep 02, 2018 4:46 am

This is an interesting scrip I plan to keep on hand, but I'm wondering about this line: "Edit in your domain and folder for phBB if applicable."

Does that mean I can only search per topic? or can I put the path to the files folder? Or in fact the whole domain of domain.com to search the entire forum?

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2796
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How I can find posts with missing files?

Post by thecoalman » Mon Sep 03, 2018 9:28 am

This sets up an operation to loop through an array, look up "while" loop or "foreach" loop for explanation. In this case it's $result which is the entire attachments table.

Code: Select all

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)
Each row in the table, is operated on individually. This sets up the path for a file that should be attached to a post. $row["physical_filename"] would changer for each iteration of the loop.

Code: Select all

$filename = __DIR__ . '/files/' . $row["physical_filename"];

file_exists just checks to see if if the file exists however we want to know if it doesn't exist, adding exclamation before something gives us opposite. For example if you are doing comparison != means "does not equal".

Code: Select all

if (!file_exists($filename)) {
If the file does not exist echo a link to the post so you can navigate to it with web browser and manually edit the post to remove ghost attachment:

Code: Select all

echo '<a href="https://yourdomain.com/forum/viewtopic.php?p=' . $post_id . '#p' . $post_id . '">' . $upload_name . '</a></br></br>';
This is only useful a for a handful of posts, if you have many posts like this it could be expanded to update the tables and remove reference to the ghost attachment.

luconsta
Registered User
Posts: 19
Joined: Thu Sep 24, 2009 3:03 pm

Re: How I can find posts with missing files?

Post by luconsta » Mon Sep 03, 2018 10:13 am

Thank you again thecoalman,

Also for explanation given to John connor, so you could help me to improve your script with some more info - because were just a handful of topics, I hope the users may have that files and I could repair the forum.

So here the new script:

Code: Select all

<?php
error_reporting(E_ALL);
set_time_limit(0);
include('./config.php');
//Edit in your username, password and database name
$conn = mysqli_connect( $dbhost, $dbuser, $dbpasswd, $dbname);
mysqli_set_charset( $conn, 'utf8');

$sql = "SELECT a.post_msg_id, a.real_filename, a.physical_filename, a.filesize, from_unixtime(a.filetime) as xfiletime, b.username, b.user_email FROM phpbb_attachments a left join phpbb_users b on a.poster_id=b.user_id";
$result = mysqli_query($conn, $sql);

echo '<table border="0">
<tr>
<td>File link</td>
<td>File size</td>
<td>File time</td>
<td>File phisical name</td>
<td>User name</td>
<td>User email</td>
</tr>';

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
	$post_id = $row["post_msg_id"];
	$upload_name =  $row["real_filename"];
	$filename = __DIR__ . '/files/' . $row["physical_filename"];
	$fsize=$row["filesize"];
	$ftime=$row["xfiletime"];
	$fname=$row["physical_filename"];
	$uname=$row["username"];
	$uemail=$row["user_email"];
	
	if (!file_exists($filename)) {
	//Edit in your domain and folder for phBB if applicable.
	echo '<tr>';
	echo '<td><a href="http://yourdomain.com/forum/viewtopic.php?p=' . $post_id . '#p' . $post_id . '">' . $upload_name . '</a></td>';
	echo '<td>' . $fsize . '</td>';
	echo '<td>' . $ftime . '</td>';
	echo '<td>' . $fname . '</td>';
	echo '<td>' . $uname . '</td>';
	echo '<td>' . $uemail . '</td>';
	echo '</tr>';
	} 
}
echo '</table>';
mysqli_close($conn);
?>

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2796
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How I can find posts with missing files?

Post by thecoalman » Mon Sep 03, 2018 10:27 am

Code: Select all

set_time_limit(0);
While this was in original script I posted I edited it to 300(seconds). Depends on your server environment but you may not be able to to override this and it shouldn't be necessary for this anyway. Some operations on the topic or posts table some can take a while if you have a large forum and setting that high can be useful to prevent the script from timing out.

User avatar
John connor
Registered User
Posts: 1726
Joined: Fri Nov 14, 2014 5:14 pm
Location: U S Of A
Contact:

Re: How I can find posts with missing files?

Post by John connor » Mon Sep 03, 2018 5:49 pm

I'm just wondering about, "Edit in your domain and folder for phBB if applicable."

What do I put there?

User avatar
canonknipser
Registered User
Posts: 1600
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: How I can find posts with missing files?

Post by canonknipser » Mon Sep 03, 2018 5:58 pm

Instead of using individual database access function, I would recommend to use phpBB's DBAL engine. You can find a example here http://canonknipser.com/viewtopic.php?f=6&t=12#p39: I comment the relevant part:

Code: Select all

define('IN_PHPBB', true); // define that you are inside phpBB
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './'; // set the phpBB root path
$phpEx = substr(strrchr(__FILE__, '.'), 1); // file extension for php scrips, commonly "php"
include($phpbb_root_path . 'common.' . $phpEx); // Startup code, include all basic functions including config, dbal etc.
include($phpbb_root_path . 'includes/functions_display.' . $phpEx); // more functions used by the script
include($phpbb_root_path . 'includes/functions_posting.' . $phpEx);  // more functions used by the script
With that code in the beginning of the script, there is no need to
John connor wrote:
Mon Sep 03, 2018 5:49 pm
"Edit in your domain and folder for phBB if applicable."

What do I put there?
, it's handled by phpBB functions
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 2796
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: How I can find posts with missing files?

Post by thecoalman » Mon Sep 03, 2018 8:15 pm

John connor wrote:
Mon Sep 03, 2018 5:49 pm
I'm just wondering about, "Edit in your domain and folder for phBB if applicable."

What do I put there?
You would use your domain and the phpBB root folder if you have one. Once again that part is used to generate links to the posts where a ghost attachment is. If phpbb is installed in the root of your domain.

Code: Select all

echo '<a href="https://yourdomain.com/viewtopic.php?p=' . $post_id . '#p' . $post_id . '">' . $upload_name . '</a></br></br>';
If it was installed in folder called forum:

Code: Select all

echo '<a href="https://yourdomain.com/forum/viewtopic.php?p=' . $post_id . '#p' . $post_id . '">' . $upload_name . '</a></br></br>';

User avatar
John connor
Registered User
Posts: 1726
Joined: Fri Nov 14, 2014 5:14 pm
Location: U S Of A
Contact:

Re: How I can find posts with missing files?

Post by John connor » Tue Sep 04, 2018 1:30 pm

Okay, thanks. That's clears things up.

Post Reply

Return to “phpBB Custom Coding”

Who is online

Users browsing this forum: Bruce Banner, Mannix_ and 6 guests

cron